Returning the value of an identity column during/after SQL INSERT command

Question

Using VS 2010, with ASP.NET, and .NET 3.0, and C#...

When I use a System.Web.UI.WebControls.SqlDataSource and call its Insert() method to insert a new row, and that table has an identity column, I'd like my method to return the value of that column.

For example, in my SQL 2005 table, I've got:

• Customer.Id
• Customer.FirstName
• Customer.LastName

Where Customer.Id is an identity colum.

When I call my method InsertNewCustomerRecord( "John", "Smith" ), I'd like to be able to return the Customer.Id that gets automatically generated in the database.

Sorry for such a roughly posed question. Let me know if I can add better detail. Thanks.

Answer 1

I would rather go with SCOPE_IDENTITY (Transact-SQL)

SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Be aware that @@IDENTITY might not return what you expect

For example, there are two tables, T1 and T2, and an INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Answer 2

If you are using SQL Server 2005 or higher, you can do this in a single statement by using the OUTPUT clause:

Create Table Foo    (
                    Id int not null identity(1,1)
                    , Name varchar(50) null
                    , ....
                    )

Insert Foo(Name)
    Output inserted.Id, inserted.Name
Select 'Foo'
Union All Select 'Bar'
....

If you are using SQL Server 2000, then you should use SCOPE_IDENTITY like so:

Insert Foo(Name)
Select 'Foo'

Select SCOPE_IDENTITY()

Notice that I can only do one Insert at a time using this method because we want to call SCOPE_IDENTITY immediately after the Insert statement.

If you are using a version prior to SQL Server 2000, then you want to use @@IDENTITY

Insert Foo(Name)
Select 'Foo'

Select @@Identity

The problem is that @@Identity will do funky things if you have triggers on the table.

EDIT You asked how to use this information in C#. You would use it just as if you called a SELECT query:

var connString = ConfigurationManager.ConnectionStrings["MyConnectionStringName"].ConnectionString;
DataTable newData;

using ( var conn = new SqlConnection( connString ) )
{
    conn.Open();
    const string sql = "Insert Foo(Name) Output inserted.Id, inserted.Name Values(@Name)";
    using ( var cmd = new SqlCommand( sql, conn ) )
    {
        cmd.Parameters.AddWithValue("@Name", "bar");
        using ( var da = new SqlDataAdapter( cmd ) )
        {
            da.Fill( newData );
        }
    }
}

Here I am assuming you have in your configuration file a connectionStrings entry for MyConnectionStringName. In addition, you will need to add a reference to System.Configuration in order to use the ConfigurationMananager class. I did not check this code but it should be pretty close to what you need. In this case, I'm writing the query directly. There are other solutions such as using a DataSource control and setting the SelectCommand.

Answer 3

The Insert method uses the InsertCommand property...

So modify the InsertCommand to be either

INSERT Mytable (col1, col2) VALUES (@param1, @parame);SELECT SCOPE_IDENTITY();

Or (for SQL Server 2005+)

INSERT Mytable (col1, col2) OUTPUT INSERTED.IDCol VALUES (@param1, @param2);