CODEWITHSUNDEEP
sql-servergetprimary-key
user3888775
user3888775

Reputation: 113

Retrieve mssql primary key from query?

In C# Visual Studio Express Windows Forms: How do I retrieve the primary key of a just executed insert query.

Here is my con+query code:

SqlConnection con = new SqlConnection(...);
SqlCommand command = con.CreateCommand();    
command.CommandText = "INSERT INTO bestillinger (ha) VALUES (@ha);
command.Parameters.AddWithValue("@ha", listBox1.SelectedItem.ToString());            
con.Open();
command.ExecuteNonQuery();
con.Close();

Upvotes: 1

Views: 49

Answers (2)

Hannah Vernon
Hannah Vernon

Reputation: 3472

As @Andomar said in his answer, ensure you properly close the connection.

Another way to return the value of the newly inserted identity field would be:

using (var con = new SqlConnection(...))
{
    con.Open();
    var command = con.CreateCommand();
    command.CommandText = @"
         INSERT INTO bestillinger (ha) OUTPUT inserted.[ID] VALUES (@ha);";
    command.Parameters.AddWithValue("@ha", listBox1.SelectedItem.ToString());
    var newPk = (long) command.ExecuteScalar();
}

[ID] would be replaced with the name of the identity field (or any field, or fields) that you want.

Upvotes: 0

Andomar
Andomar

Reputation: 238296

With a manual Close(), you risk leaking a connection if the code that uses it throws an exception. So please use using instead.

Assuming your PK is an identity column, the new value is most easily retrieved with scope_identity():

using (var con = new SqlConnection(...))
{
    con.Open();
    var command = con.CreateCommand();
    command.CommandText = @"
         INSERT INTO bestillinger (ha) VALUES (@ha);
         select scope_identity();";
    command.Parameters.AddWithValue("@ha", listBox1.SelectedItem.ToString());
    var newPk = (long) command.ExecuteScalar();
}

Upvotes: 1

Related Questions