Using SCOPE_IDENTITY with CommandBuilder and DataAdapter

SCOPE_IDENTITY() is the only correct way of retrieving the identity value of the just-inserted table row (opposed to @@IDENT and IDENT_CURRENT() which are prone to multiuser conflicts).

But since SCOPE_IDENTITY() works within its execution scope, it cannot be called after the INSERT command: it has to be called within the same INSERT command that inserts the row.

This means that you have to modify your INSERT command by adding a call to SCOPE_IDENTITY(). It also means that you can't use a DbCommandBuilder and a DataAdapter because the DbCommandBuilder generates its default SQL command.

Also the DbCommandBuilder is a sealed class and can't be modified.

But there is a workaround to continue using CommandBuilders and keep things simple: use a CommandBuilder to generate the INSERT command and then modify it in its CommandText property. Here's how: (the example is for OleDb, but applies also for Odbc and Sql as well)

// creates the data adapter with handled RowUpdated event
OleDbDataAdapter DA = new OleDbDataAdapter(...); 
DA.RowUpdated += new OleDbRowUpdatedEventHandler(My_OnRowUpdate);

// creates the commandbuilder for the adapter
OleDbCommandBuilder CB = new OleDbCommandBuilder(DA);                

// DELETE and UPDATE commands don't need to be modified, just clone them
DA.DeleteCommand = (OleDbCommand) CB.GetDeleteCommand().Clone();
DA.UpdateCommand = (OleDbCommand) CB.GetUpdateCommand().Clone();

// now we modify the INSERT command, first we clone it and then modify
OleDbCommand cmd = (OleDbCommand) CB.GetInsertCommand().Clone();

// adds the call to SCOPE_IDENTITY                                      
cmd.CommandText += " SET ? = SCOPE_IDENTITY()"; 

// the SET command writes to an output parameter "@ID"
OleDbParameter parm = new OleDbParameter();
parm.Direction = ParameterDirection.Output;                   
parm.Size = 4;
parm.OleDbType = OleDbType.Integer;
parm.ParameterName = "@ID";
parm.DbType = DbType.Int32;                                      

// adds parameter to command
cmd.Parameters.Add(parm);

// adds our customized insert command to DataAdapter
DA.InsertCommand = cmd;

// CommandBuilder needs to be disposed otherwise 
// it still tries to generate its default INSERT command 
CB.Dispose();

So now we have a DataAdapter with a customized INSERT command that returns the identity value into an output parameter. Such parameter is available during the RowUpdate event, so we can update our table row as following:

private void My_OnRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
{             
   if(e.StatementType==StatementType.Insert) 
   {                
      // reads the identity value from the output parameter @ID
      object ai = e.Command.Parameters["@ID"].Value;
      
      // updates the identity column (autoincrement)                   
      foreach(DataColumn C in Tab.Columns)
      {
         if(C.AutoIncrement)
         {
            C.ReadOnly = false;                      
            e.Row[C] = ai;  
            C.ReadOnly = true;
            break; // there can be only one identity column
         }      
      }                        

      e.Row.AcceptChanges();             
   }
}