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

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
• Custom开发者_运维问答er.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.

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.

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.

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);