.net - Possible to retrieve IDENTITY column value on insert using SqlCommandBuilder (without using Stored Proc)?

Question

FYI: I am running on dotnet 3.5 SP1

I am trying to retrieve the value of an identity column into my dataset after performing an update (using a SqlDataAdapter and SqlCommandBuilder). After performing SqlDataAdapter.Update(myDataset), I want to be able to read the auto-assigned value of myDataset.tables(0).Rows(0)("ID"), but it is System.DBNull (despite the fact that the row was inserted).

(Note: I do not want to explicitly write a new stored procedure to do this!)

One method often posted http://forums.asp.net/t/951025.aspx modifies the SqlDataAdapter.InsertCommand and UpdatedRowSource like so:

SqlDataAdapter.InsertCommand.CommandText += "; SELECT MyTableID = SCOPE_IDENTITY()"
InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord

Apparently, this seemed to work for many people in the past, but does not work for me.

Another technique: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619031&SiteID=1 doesn't work for me either, as after executing the SqlDataAdapter.Update, the SqlDataAdapter.InsertCommand.Parameters collection is reset to the original (losing the additional added parameter).

Does anyone know the answer to this???

Answer 1

This is a problem that I've run into before, the bug seems to be that when you call da.Update(ds); the parameters array of the insert command gets reset to the inital list that was created form your command builder, it removes your added output parameters for the identity.

The solution is to create a new dataAdapter and copy in the commands, then use this new one to do your da.update(ds);

like

SqlDataAdapter da = new SqlDataAdapter("select Top 0 " + GetTableSelectList(dt) + 
"FROM " + tableName,_sqlConnectString);
SqlCommandBuilder custCB = new SqlCommandBuilder(da);
custCB.QuotePrefix = "[";
custCB.QuoteSuffix = "]";
da.TableMappings.Add("Table", dt.TableName);

da.UpdateCommand = custCB.GetUpdateCommand();
da.InsertCommand = custCB.GetInsertCommand();
da.DeleteCommand = custCB.GetDeleteCommand();

da.InsertCommand.CommandText = String.Concat(da.InsertCommand.CommandText, 
"; SELECT ",GetTableSelectList(dt)," From ", tableName, 
" where ",pKeyName,"=SCOPE_IDENTITY()");

SqlParameter identParam = new SqlParameter("@Identity", SqlDbType.BigInt, 0, pKeyName);
identParam.Direction = ParameterDirection.Output;
da.InsertCommand.Parameters.Add(identParam);

da.InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;

//new adaptor for performing the update                     
SqlDataAdapter daAutoNum = new SqlDataAdapter();
daAutoNum.DeleteCommand = da.DeleteCommand;
daAutoNum.InsertCommand = da.InsertCommand;
daAutoNum.UpdateCommand = da.UpdateCommand;

daAutoNum.Update(dt);