Getting IDs back from an Oracle query using .net core and Oracle.ManagedDataAccess.Core

Question

I have the following code:

List<foo> collection = getData();
using (OracleConnection conn = _client.GetConnection())
{
    await conn.OpenAsync();
    using (OracleTransaction transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted))
    using (OracleCommand cmd = new OracleCommand())
    {
        cmd.Transaction = transaction;
        cmd.Connection = conn;
        cmd.CommandText = @"INSERT INTO SOME.TABLE
                           (A, B, C)
                           VALUES
                           (:A, :B, :C)";
        cmd.ArrayBindCount = collection.Count;
        cmd.Parameters.Add(":A", OracleDbType.Varchar2, collection.Select(c => c.A).ToArray(), ParameterDirection.Input);
        cmd.Parameters.Add(":B", OracleDbType.Varchar2, collection.Select(c => c.B).ToArray(), ParameterDirection.Input);
        cmd.Parameters.Add(":C", OracleDbType.Varchar2, collection.Select(c => c.C).ToArray(), ParameterDirection.Input);

        try
        {
            await cmd.ExecuteNonQueryAsync();
            transaction.Commit();
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            throw new FailedTransactionException(ex.ToString());
        }
    }
}

Here I can insert multiple rows of data from an collection from my but I need to return the IDs of inserted rows somehow. Could someone point me in the right direction or if there's an better approach to inserting array of objects and getting the IDs back.

Answer 1

You can get back the generated id by doing this:

@"INSERT INTO SOME.TABLE
  (A, B, C)
  VALUES
  (:A, :B, :C) 
  RETURNING <YOUR-ID-FIELD> INTO :resultId";

And declare the result parameter like this:

var resultParam = new OracleParameter(":resultId", OracleDbType.Decimal /* or whatever type of your identifier has */);
resultParam.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(resultParam);

Then you can read the result from the param after executing the query from its Value property:

var result = resultParam.Value