C# Get Primary ID Key on SQL Insert

 

How do I get the primary ID key on SQL Insert in C#

If you need to get the auto-generated Primary Key of a newly added record when inserting into SQL server, it can be a nightmare. Some people try to use timestamps and unique data to look up the record in a new query, but you don’t need to do all that.

Update: I have different post for how to Get the Primary Key on insert using Entity Framework. Click here

scope_identity()

Lucky for us there is a wonderful SQL tool called scope_identity()

You can Google it if you want all the gory details. More importantly, here is a basic code example to get you going.

Code Sample;

1:        Int32 newProdID = 0;  
2:        string strSQL = "Insert into Table (Field1, Field2) values (@Field1, @Field2); "  
3:        strSQL += "SELECT CAST(scope_identity() AS int)";  
4:        SqlConnection conn = new SqlConnection(sConn);  
5:        try  
6:        {  
7:          conn.Open();  
8:          using (SqlCommand comd = new SqlCommand(strSQL, conn))  
9:          {  
10:            comd.Parameters.Add("@Field1", SqlDbType.VarChar);  
11:            comd.Parameters["@Field1"].Value = "some data";  
12:            comd.Parameters.Add("@Field2", SqlDbType.VarChar);  
13:            comd.Parameters["@Field2"].Value = "some data";  
14:            newProdID = (Int32)comd.ExecuteScalar();  
15:          }  
16:          conn.Close();  
17:        }  
18:        catch (Exception err)  
19:        { }  

If you want to streamline your work you can update your database in a method and return the Primary Key ID as an integer.

Code Block formated by codeformatter.blogspot.com