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;
from two solutions provided by Éric Moreau and Bonnie Berent in Messages #1090511 and #930339
|