How to retrieve auto-generated ID and update DataTable after SQL Server insert operation

Data and Databases
1

I’m working with a DataTable in C# that has an identity column using negative values for temporary row identification. After inserting new records into SQL Server, I need to update the DataTable rows with the actual primary key values generated by the database.

I tried this approach but the DataTable rows still contain the original negative values:

using (SqlDataAdapter dbAdapter = new SqlDataAdapter(queryString, sqlConnection))
using (SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(dbAdapter))
{
    var keyColumn = myDataTable.PrimaryKey[0];
    var keyName = keyColumn.ColumnName;
    
    dbAdapter.UpdateCommand = cmdBuilder.GetUpdateCommand();
    dbAdapter.DeleteCommand = cmdBuilder.GetDeleteCommand();
    
    dbAdapter.InsertCommand = cmdBuilder.GetInsertCommand();
    dbAdapter.InsertCommand.CommandText += "; SET @NewId = SELECT CAST(SCOPE_IDENTITY() AS int);";
    var outputParam = dbAdapter.InsertCommand.CreateParameter();
    
    outputParam.ParameterName = "@NewId";
    outputParam.DbType = DbType.Int32;
    outputParam.Direction = ParameterDirection.Output;
    outputParam.SourceColumn = keyName;
    dbAdapter.InsertCommand.Parameters.Add(outputParam);
    
    dbAdapter.Update(myDataTable);
}

I also experimented with the RowUpdated event handler but couldn’t get it working properly. What am I missing here?

2

are you handling the RowUpdated event right? I’ve hit this before - sometimes you need to manually refresh the row after inserting. what’s your event handler look like? also, why use negative temp ids? why not just let the datatable handle it automatically?

3

Your SQL syntax is incorrect. You shouldn’t use SET @NewId = SELECT CAST(SCOPE_IDENTITY() AS int) like that. Instead, append ; SELECT @NewId = SCOPE_IDENTITY() to your insert command and make sure to set the UpdatedRowSource property accordingly:

dbAdapter.InsertCommand = cmdBuilder.GetInsertCommand();
dbAdapter.InsertCommand.CommandText += “; SELECT @NewId = SCOPE_IDENTITY()”;

Additionally, declare var outputParam = new SqlParameter("@NewId", SqlDbType.Int); and set its properties correctly:

outputParam.Direction = ParameterDirection.Output;
outputParam.SourceColumn = keyName;
outputParam.SourceVersion = DataRowVersion.Current;
dbAdapter.InsertCommand.Parameters.Add(outputParam);

dbAdapter.InsertCommand.UpdatedRowSource = UpdateRowSource.OutputParameters;

The important step is to set UpdatedRowSource to OutputParameters. This configuration ensures that the output parameter maps back to your DataTable row, allowing it to reflect the new identity values post-insertion.

4

Check if you’re using the AcceptChangesDuringUpdate property - set it to false before calling update. Otherwise the datatable thinks everything’s already committed and won’t pull back the new IDs. Had the same issue last month and this fixed it for me.