Get the value of the identity column after inserting a record
Aparently there are still a lot of people who are having problems when inserting rows in a table that has an identity field. Sometimes you need to have the id of the record you just inserted to be able to use it afterwards.
Yesterday I saw someone doing a SELECT MAX(id) FROM MyTable to be able to get the maximum id and then raise it by 1. This gives 2 issues:
- what if someone else has added a row after you selected the maximum value?
- what if the identity increment is not 1?
In SQL Server there is a function SCOPE_IDENTITY() that returns the last used identity within the scope of the statement. If you add this to the end of your statement, you can return the value of the identity column.
Here is a sample that shows you how to do it (NOTE the end of the select statement):
static void Main(string[] args)
{
int identity = -1;
string connectionString = "Data Source=LP51; Initial Catalog=Northwind; User Id=sa; Password=P@ssw0rd;";
string sql = "INSERT INTO Employees (LastName, FirstName) VALUES (@LastName, @FirstName) SELECT SCOPE_IDENTITY()";SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@LastName", "Verhoeven");
cmd.Parameters.AddWithValue("@FirstName", "Geert");
try
{
conn.Open();
identity = int.Parse(cmd.ExecuteScalar().ToString());
}
catch
{
// implement exception handling here
}
finally
{
conn.Dispose();
cmd.Dispose();
}
Console.WriteLine("The identity is {0}", identity);
Console.WriteLine("Press any key to continue ...");
Console.ReadLine();
}
