Retrieve Identity Column Value After Inserting Record in Database Table
by David Hayden ( .NET Developer )
Retrieving the value of an identity column after inserting a record into the table of a SQL Server Database is a fairly common question in the MSDN Forums. There are several ways to pull this off and the answer may often be “it depends”, but here is one example using the Shippers Table in the Northwind Database.
Adding a new Shipper
If you take a peek at the Shippers Table in Northwind, you will notice that the ShipperID Column in the table is an Identity PK. This means that the table itself will generate a new ShipperID when you insert a new record into the table. Since you are probably interested in the value of ShipperID after inserting a new record, it is your job to go and get it.
My preference is to use an Output Parameter as part of the ExecuteNonQuery() Command to return the value of ShipperID. This is certainly the fastest as you avoid additional roundtrips to the database as well as returning any rows. Here is the complete code for inserting a new record into the Shippers Table and returning its PK:
private int AddShipper(string companyName, string phone)
{
string connectionString = "...Northwind...";
int id = 0;
using (SqlConnection connection =
new SqlConnection(connectionString))
{
using (SqlCommand insertCommand =
connection.CreateCommand())
{
insertCommand.CommandText = "INSERT INTO Shippers
(CompanyName, Phone) VALUES (@CompanyName,
@Phone) SET @ShipperID = SCOPE_IDENTITY()";
SqlParameter companyNameParameter = new
SqlParameter("@CompanyName", SqlDbType.NVarChar,
40);
companyNameParameter.Value = companyName;
insertCommand.Parameters.Add(companyNameParameter);
SqlParameter phoneParameter = new SqlParameter
("@Phone", SqlDbType.NVarChar
, 24);
if (phone.Length == 0)
phoneParameter.Value = DBNull.Value;
else
phoneParameter.Value = phone;
insertCommand.Parameters.Add(phoneParameter);
SqlParameter shipperIDParameter =
new SqlParameter("@ShipperID",
SqlDbType.Int);
shipperIDParameter.Direction =
ParameterDirection.Output;
insertCommand.Parameters.Add(shipperIDParameter);
insertCommand.Connection.Open();
insertCommand.ExecuteNonQuery();
id = (int)shipperIDParameter.Value;
}
}
return id;
}
The magic is the additional Select Statement in the InsertCommand.CommandText shown above:
insertCommand.CommandText =
"... SET @ShipperID = SCOPE_IDENTITY()";
SCOPE_IDENTITY() returns the value of the identity column ( ShipperID ) in the Insert Statement that just occurred. For more information on SCOPE_IDENTITY() you can read the following article:
SCOPE_IDENTITY() vs. @@IDENTITY - Retrieving Identity for Most Recently Added Row in Table
How Microsoft Does It - Check SQL Profiler
So the above code is just my opinion and certainly there are others ways to pull it off. My question to myself was how does Microsoft do it? When you drop a DataSource on a Windows Form and it renders a DataGridView, they do some code generation underneath that generates an Insert Statment for you. I started up SQL Profiler and took a look. Here is the results:
INSERT INTO [dbo].[Shippers]
([CompanyName], [Phone])
VALUES
(@CompanyName, @Phone);
SELECT ShipperID, CompanyName, Phone
FROM Shippers
WHERE (ShipperID = SCOPE_IDENTITY())
As you can see, Microsoft is also using SCOPE_IDENTITY() to retrieve the identity of the new record. But instead of just returning the identity value as an output parameter, they return the entire record. Not quite as fast, but it does have an added safety value of assuring your code accounts for any triggers and default values on columns not participating in the original Insert Statement.
Conclusion
Hopefully this helps one understand how to retrieve the value of an identity column after inserting a record into the table of a SQL Server Database.
Source: David Hayden ( Florida .NET Developer )
Free ADO.NET Tutorials