Monday, July 23, 2012

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

9 comments:

  1. Very good article - we don't use the auto-increment field, but we use GUID's instead, so we call the function NEWID() to generate a new GUID. we can then store this value and pass it through a out variable in the SP to have access to the variable in the code. :)

    Keep on posting :)

    ReplyDelete
  2. Thats possible with SQL Server.But What is the type of the primary key column?

    ReplyDelete
  3. Hi Timothy

    In my post I am referring to an auto-incrementing Integer as the primary key.

    ReplyDelete
  4. Hi Gates,
    You can add a calculated column to display the value formatted the way you want to.

    ReplyDelete
  5. What's the different between SQL and MySQL?

    ReplyDelete
  6. Hi Mo,

    You're not comparing the same things.

    SQL is a language. Specifically, the "Structured Query Language"

    MySQL is one of several database systems, or RDMS (Relational Database Management System), others of which are Oracle, Informix, Postgres, and Microsoft SQL Server.

    All of these RDMSs use SQL as their language. Each of them have minor variations in the "dialect" of SQL that they use, but it's all still SQL.

    ReplyDelete
  7. Thanks a million! Been looking all over the web for this answer. Working with a 3rd party database, so I am bound by their design. Exactly what I needed! Jim

    ReplyDelete