SQL INSERT and SELECT into Oracle using a single command with .NET

Question

I'm trying to execute a SQL command that insert a record on a table and returns the generate primary key.
I'm using .NET Core 3.1 and Oracle.ManagedDataAccess.Core package.

This is the C# code to execute the SQL command (it uses some extension methods but is clear how it works):

        private int PutSomethingInTheDatabase(string entity)
        {
            string sqlComamnd = File.ReadAllText("SQL//Insert Card.sql");

            using (var connection = new Oracle.ManagedDataAccess.Client.OracleConnection(connectionString))
            using (var command = connection.OpenAndUse().CreateTextCommand(sqlComamnd))
            {
                //var reader = command.ExecuteReader();
                //reader.Close();
                //var result = command.ExecuteScalar();
                //return (int)(decimal)result;

                return -1;
            }
        }

Ideally I will receive a single value and read it with ExecuteScalar().
It is an itegration test (thats why I read the SQL from a file).

The SQL I want to use should INSERT the new record and return the generated sequence within the same scope/transaction, that's whi I'm using Begin/End but I'm not sure it is the right way.
My problem is that I cannot find the right syntax to execute the last SELECT to return the generated sequence_id, I also tried with RETURN...

This is the SQL:

declare new_id number;

BEGIN
    select seq_stage_card.NEXTVAL into new_id from dual;

    INSERT INTO spin_d.stage_card (
        sequence_id,
        field_1,
        field_2
    )
    VALUES (
        new_id,
        'aaa'
        TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss')
    );

    select new_id from dual where 1 = 1 ;  -- not valid

END;

-- return new_id ;                   -- not valid 
-- select new_id from dual ;         -- not valid

How to change the SQL in order to return the new_id ? There is another (better) way to achieve the same result? Is it safe (isolated scope), or the select will return a wrong ID if there is a concurrent insert?

[Update]
Someone suggested to use RETURNING (see here: Oracle - return newly inserted key value)
I already tried to use RETURN and RETURNING but I haven't find any real example of usage with the .NET (or other frameworks) driver, eg. OracleSqlCommand and the right call to execute.
Maybe it works but I still cannot figure out how to use it.

Does this answer your question? Oracle - return newly inserted key value — jason.kaisersmith
– jason.kaisersmith, Commented Feb 12, 2020 at 10:07

Dmitrii Bychenko · Accepted Answer · 2020-02-12 10:39:19Z

2

In general case (when you have to implement some logics within anonymous block, and when returning is not an option) try bind variables: first, turn new_id into :new_id in the query:

BEGIN
    SELECT seq_stage_card.NEXTVAL 
      INTO :new_id -- bind variable to return back to c#
      FROM dual;

    INSERT INTO spin_d.Stage_Card (
        sequence_id,
        field_1,
        field_2
    )
    VALUES (
        :new_id,
        'aaa',
         TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss')
    );
END;

Then use it in C# code:

...
using (var command = connection.OpenAndUse().CreateTextCommand(sqlComamnd))
{
  //TODO: check the syntax and RDBMS type
  command.Parameters.Add(
    ":new_id", 
     OracleDbType.Int32).Direction = ParameterDirection.Output;

  // Execute query 
  command.ExecuteNonQuery();

  // Bind variable reading
  return Convert.ToInt32(command.Parameters[0].Value);
}

edited Feb 12, 2020 at 10:39

answered Feb 12, 2020 at 10:16

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alex 75 Over a year ago

Yes!!! The ID is (as usual) a Oracle.Decimal so the way I like to parse it (because I hate Convert) is this: return (int)(decimal)newidParameter.Value. Thank you.

Dmitrii Bychenko Over a year ago

@Alex 75: tastes differ - I love Convert ;) If (when?) the scheme is changed Convert will help me out (say, converting from string), and cast can well crash

Alex 75 Over a year ago

Just to help the next guy will try this code. The correct cast is this: return (int)(Oracle.ManagedDataAccess.Types.OracleDecimal)(idParameter.Value). I always used to cast to decimal (using .Net Framework), maybe .Net Core 3.x works differently. I also tried to use Convert.ToInt32 but I have the same cast error:

// System.InvalidCastException: 'Unable to cast object of type 'Oracle.ManagedDataAccess.Types.OracleDecimal' to type 'System.IConvertible'.'

Thanks again.

Collectives™ on Stack Overflow

SQL INSERT and SELECT into Oracle using a single command with .NET

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related