SqlDataAdapter fails to get row after insert

Question

I'm trying to use SqlDataAdapter to insert a row, and then immediately get that same row. I followed the advice on this post and use SCOPE_IDENTITY, but it doesn't work. Here is my code...

using (var conn = _db.OpenConnection())
{
    var sqlQuery = "SELECT * FROM RotationItem WHERE [RotationItemId] = SCOPE_IDENTITY()";
    var adapter = new SqlDataAdapter(sqlQuery, conn);
    var builder = new SqlCommandBuilder(adapter);
    var dataSet = new DataSet();
    adapter.Fill(dataSet);
    Debug.WriteLine("First fill, rows " + dataSet.Tables[0].Rows.Count);
    var table = dataSet.Tables[0];
    table.Rows.InsertAt(table.NewRow(), 0);
    CopyJsonToRow(table, 0, item);
    if (adapter.Update(dataSet) != 1)
    {
        throw new InvalidOperationException("Insert failed");
    }
    // After insert, fetch the new record
    dataSet.Clear();
    adapter.Fill(dataSet);
    Debug.WriteLine("Second fill, rows " + dataSet.Tables[0].Rows.Count);
}

My output is:

First fill, rows 0
Second fill, rows 0 <== this is NOT what I expect

Why does the second fill fail? Shouldn't it get the row that I just inserted?!

I am not using any Transactions. The definition of the table is below...

CREATE TABLE [dbo].[RotationItem] (
    [RotationItemId] INT NOT NULL IDENTITY(1,1),
    [RotationScheduleId] INT NOT NULL,   
    [QuestionnaireId] INT NOT NULL,  
    [Order] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([RotationItemId] ASC)
);

SCOPE_IDENTITY works on the same session. I suspect that your insert may be on a different session and therefore the subsequent SCOPE_IDENTITY will not have anything to return. You could verify by running a profiler trace while you do this and see what the session_id values return. A better way would be to use a stored procedure to INSERT your row and return the SCOPE_IDENTITY value as an Output Parameter — Martin Cairney, Oct 15 '18 at 15:53
@MartinCairney I think you are correct, because when I change the query to use `IDENT_CURRENT('RotationItem')` instead of `SCOPE_IDENTITY()` then it works!! However, it often gives the wrong answer if many inserts are running concurrently on different threads. That sucks. — John Henckel, Oct 15 '18 at 16:45
Here is the doc page about retrieving identity values with ADO.NET: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/retrieving-identity-or-autonumber-values — David Browne - Microsoft, Oct 15 '18 at 16:55
@DavidBrowne-Microsoft Thanks, but that page does not help me, because I am using the `SqlCommandBuilder`. Do you suppose it is impossible using SqlCommandBuilder? I tried to append ";SELECT SCOPE_IDENTITY()" to the end of `builder.GetInsertCommand().CommandText` but it has **no effect**. I tried to set `adapter.InsertCommand` but it has no effect. Once I create the SqlCommandBuilder, I can't seem to customize it at all. — John Henckel, Oct 15 '18 at 20:26

score 0 · Answer 1 · answered Oct 15 '18 at 21:07

Here's what I found after several hours of messing around.

You should not use IDENT_CURRENT because it is very unreliable
You cannot use SCOPE_IDENTITY after SqlDataAdapter.Update (as in OP) because it closes the scope.
when you call new SqlCommandBuilder(adapter) it does secret voodoo to your adapter that make it impossible to customize. In particular, any changes you make to the InsertCommand are ignored.

Here is my clever work around (some might say horrible hack). This works perfectly, even it I hammer it with many concurrent clients.

using (var conn = _db.OpenConnection())
{
    var sqlQuery = "SELECT * FROM RotationItem WHERE [RotationItemId] = SCOPE_IDENTITY()";

    // Create a dummy adapter, so we can generate an appropriate INSERT statement
    var dummy = new SqlDataAdapter(sqlQuery, conn);
    var insert = new SqlCommandBuilder(dummy).GetInsertCommand();

    // Append the SELECT to the end of the INSERT command, 
    // and set a flag to copy the result back into the dataSet table row
    insert.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
    insert.CommandText += ";" + sqlQuery;

    // Now proceed as usual...
    var adapter = new SqlDataAdapter(sqlQuery, conn);
    adapter.InsertCommand = insert;
    var dataSet = new DataSet();
    adapter.Fill(dataSet);
    Debug.WriteLine("First fill, rows " + dataSet.Tables[0].Rows.Count);
    var table = dataSet.Tables[0];
    table.Rows.InsertAt(table.NewRow(), 0);
    CopyJsonToRow(table, 0, item);
    if (adapter.Update(dataSet) != 1)
    {
        throw new InvalidOperationException("Insert failed");
    }
    // After insert, the table AUTOMATICALLY has the new row ID (and any other computed columns)
    Debug.WriteLine("The new id is " + table.Rows[0].ItemArray[0]);
}

The negative points of this hack is I need to make two SqlDataAdapters, one is a dummy just feed to the SqlCommandBuilder. Also using the string concatenation to glue two SQL queries together is very dodgy. I'm not sure if the internal security audit will allow me to do that, because of the injection issue. Anyone have a better idea?

SqlDataAdapter fails to get row after insert

1 Answers1