is the only correct way of retrieving the identity value of the just-inserted table row (opposed to
which are prone to multiuser conflicts).
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
. It also means that you can't use a
generates its default SQL command.
is a sealed class and can't be modified.
But there is a workaround to continue using CommandBuilders and keep things simple: use a
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)
OleDbDataAdapter DA = new OleDbDataAdapter(...);
DA.RowUpdated += new OleDbRowUpdatedEventHandler(My_OnRowUpdate);
OleDbCommandBuilder CB = new OleDbCommandBuilder(DA);
DA.DeleteCommand = (OleDbCommand) CB.GetDeleteCommand().Clone();
DA.UpdateCommand = (OleDbCommand) CB.GetUpdateCommand().Clone();
OleDbCommand cmd = (OleDbCommand) CB.GetInsertCommand().Clone();
cmd.CommandText += " SET ? = SCOPE_IDENTITY()";
OleDbParameter parm = new OleDbParameter();
parm.Direction = ParameterDirection.Output;
parm.Size = 4;
parm.OleDbType = OleDbType.Integer;
parm.ParameterName = "@ID";
parm.DbType = DbType.Int32;
DA.InsertCommand = cmd;
So now we have a
with a customized INSERT command that returns the identity value into an output parameter. Such parameter is available during the
event, so we can update our table row as following:
private void My_OnRowUpdate(object sender, OleDbRowUpdatedEventArgs e)
object ai = e.Command.Parameters["@ID"].Value;
foreach(DataColumn C in Tab.Columns)
C.ReadOnly = false;
e.Row[C] = ai;
C.ReadOnly = true;