Click here to Skip to main content
13,147,050 members (67,561 online)
Click here to Skip to main content
Add your own
alternative version


3 bookmarked
Posted 23 Nov 2011

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
// 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 

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)
      // 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)
            C.ReadOnly = false;                      
            e.Row[C] = ai;  
            C.ReadOnly = true;
            break; // there can be only one identity column


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


About the Author

Antonino Porcino
Software Developer
Italy Italy
No Biography provided

You may also be interested in...


Comments and Discussions

Questionhad to modify the code Pin
Member 93646083-Aug-17 9:59
memberMember 93646083-Aug-17 9:59 
QuestionI Found this code Great!!! Pin
Mehdi Azizi8-May-14 3:41
memberMehdi Azizi8-May-14 3:41 
QuestionI receive the message " Wrong syntax near '?' No it works Pin
Member 17850916-Oct-13 10:01
memberMember 17850916-Oct-13 10:01 
QuestionMore information to error I previously mentioned Pin
Member 17850916-Oct-13 6:27
memberMember 17850916-Oct-13 6:27 
QuestionI receive the message " Wrong syntax near '?' Pin
Member 17850916-Oct-13 6:23
memberMember 17850916-Oct-13 6:23 
AnswerRe: I receive the message " Wrong syntax near '?' Pin
Antonino Porcino6-Oct-13 7:43
memberAntonino Porcino6-Oct-13 7:43 
QuestionYour code saved me :-)) Pin
Member 17850915-Oct-13 7:59
memberMember 17850915-Oct-13 7:59 
GeneralArticolo Pin
Member 83166148-Jan-13 21:45
memberMember 83166148-Jan-13 21:45 
GeneralA minor comment to your trick: The <code>scope_identity</cod... Pin
Mika Wendelius23-Nov-11 8:59
mvpMika Wendelius23-Nov-11 8:59 

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

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 23 Nov 2011
Article Copyright 2011 by Antonino Porcino
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid