Returning the value of an identity column during / after the 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
  • 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.

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