Click here to Skip to main content
Click here to Skip to main content

Using SCOPE_IDENTITY with CommandBuilder and DataAdapter

, 23 Nov 2011
Rate this:
Please Sign up or sign in to vote.
How to use T-SQL function SCOPE_IDENTITY() to retrieve inserted rows identity values with CommandBuilders and DataAdapters
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();             
   }
}

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Antonino Porcino
Software Developer
Italy Italy
No Biography provided

Comments and Discussions

 
QuestionI Found this code Great!!! PinmemberMehdi Azizi8-May-14 3:41 
QuestionI receive the message " Wrong syntax near '?' No it works PinmemberMember 17850916-Oct-13 10:01 
QuestionMore information to error I previously mentioned PinmemberMember 17850916-Oct-13 6:27 
QuestionI receive the message " Wrong syntax near '?' PinmemberMember 17850916-Oct-13 6:23 
AnswerRe: I receive the message " Wrong syntax near '?' PinmemberAntonino Porcino6-Oct-13 7:43 
QuestionYour code saved me :-)) PinmemberMember 17850915-Oct-13 7:59 
GeneralArticolo PinmemberMember 83166148-Jan-13 21:45 
GeneralA minor comment to your trick: The <code>scope_identity</cod... PinmvpMika Wendelius23-Nov-11 8:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140827.1 | Last Updated 23 Nov 2011
Article Copyright 2011 by Antonino Porcino
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid