Determine new PK after an Insert

Today’s blog post will be another excerpt from my .Net Tips column in the April 2008 Issue of the Universal Thread Magazine.

You have a couple of options to get the PK value of a newly inserted record. I am assuming SQL Server database in these examples.

First, if you are not using Stored Procs and simply sending an INSERT INTO statement, just add a "SELECT SCOPE_IDENTITY() at the end of your query and be sure to execute your query by reading the result back (into a datareader or dataset for example).

Your query string should look like this:

string Sql = "INSERT INTO MyTable (ColumnOne) VALUES ('Bob') SELECT SCOPE_IDENTITY() AS MyPK";

And, here's the code that shows how to execute and read the results back using a DataReader:

int MyPK = 0;
SqlCommand sc = new SqlCommand(Sql, new SqlConnection(this.TestConnection));
sc.Connection.Open();
SqlDataReader rd = sc.ExecuteReader(CommandBehavior.CloseConnection);
while (rd.Read())
{
  MyPK = Convert.ToInt32(rd["MyPK"]);
}

Alternatively, if you're using a Stored Proc, you'd use an OUTPUT parameter in your Stored Proc, and a ParameterDirection.InputOutput in your code:

-- The Stored Proc
CREATE PROCEDURE MySP 
          @PK        int     = NULL OUTPUT,
          @ColumnOne char(8) = NULL,
          @ColumnTwo char(4) = NULL
AS
  INSERT MyTable (ColumnOne, ColumnTwo)
         SELECT @ColumnOne, @ColumnTwo
  SELECT @PK = SCOPE_IDENTITY()

And here's how you would call it in your code:

Command.CommandText = "MySP";
Command.Parameters.Add("@PK", 0);
Command.Parameters["@PK"].Direction = ParameterDirection.InputOutput;
Command.Parameters.Add("@ColumnOne", OneValue);
Command.Parameters.Add("@ColumnTWo", TwoValue);
Command.ExecuteNonQuery();
MyNewPK = Command.Parameters["@PK"].Value;

Thanks to Éric Moreau (and another post by me) in Messages #1090511 and #930339 on the Universal Thread.