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

If those other methods didn't work for you, the .Net provided tools (SqlDataAdapter) etc. don't really offer much else regarding flexibility. You generally need to take it to the next level and start doing stuff manually. Stored procedure would be one way to keep using the SqlDataAdapter. Otherwise, you need to move to another data access tool as the .Net data libraries have limits since they design to be simple. If your model doesn't work with their vision, you have to roll your own code at some point/level.

Answer 2

The insert command can be instructed to update the inserted record using either output parameters or the first returned record (or both) using the UpdatedRowSource property...

InsertCommand.UpdatedRowSource = UpdateRowSource.Both;

If you wanted to use a stored procedure, you'd be done. But you want to use a raw command (aka the output of the command builder), which doesn't allow for either a) output parameters or b) returning a record. Why is this? Well for a) this is what your InsertCommand will look like...

INSERT INTO [SomeTable] ([Name]) VALUES (@Name)

There's no way to enter an output parameter in the command. So what about b)? Unfortunately, the DataAdapter executes the Insert command by calling the commands ExecuteNonQuery method. This does not return any records, so there is no way for the adapter to update the inserted record.

So you need to either use a stored proc, or give up on using the DataAdapter.

Answer 3

Have you looked into using LINQ instead? I understand this doesn't address your actual question, but if you are using .NET 3.5 you really ought to try using LINQ.

Answer 4

Actually, this works for me :

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

Cheers.

Answer 5

Hi! I had the same problem. It was just solved when I cloned the command generated by the commandbuilder. It looks that even when you change the commandText of the insertcommand, it keeps getting the command generated by the Commandbuilder... Here it's the code that I used to clone the command...

        private static void CloneBuilderCommand(System.Data.Common.DbCommand toClone,System.Data.Common.DbCommand repository)
    {
        repository.CommandText = toClone.CommandText;
        //Copying parameters
        if (toClone.Parameters.Count == 0) return;//No parameters to clone? go away!
        System.Data.Common.DbParameter[] parametersArray= new System.Data.Common.DbParameter[toClone.Parameters.Count];
        toClone.Parameters.CopyTo(parametersArray, 0);
        toClone.Parameters.Clear();//Removing association before link to the repository one
        repository.Parameters.AddRange(parametersArray);            
    }

Answer 6

What works for me is configuring a MissingSchemaAction:

        SqlCommandBuilder commandBuilder = new SqlCommandBuilder(myDataAdapter);
        myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;

This lets me retrieve the primary key (if it is an identity, or autonumber) after an insert.

Good luck.