Unfortunately Google failed me recently. I needed to get the value of an identity field in a Sql Server database after I inserted a record from an ASP.Net application. I'm sure the information is out there somewhere, but I wasn't able to find it (OK, I didn't really look that long).
The key to making this work was knowing two basic things.
- You can batch statements in a SqlCommand.
- SCOPE_IDENTITY is a T-SQL method that returns the last identity assigned within a particular scope.
Here is the code I wrote that gets the identity for a record that is just inserted and uses it in a second SQL statement. See line # 6 for the SCOPE_IDENTITY usage.
1: var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["Gleneagle"].ConnectionString);
2: conn.Open();
3: var trans = conn.BeginTransaction();
4: try
5: {
6: var cmd = new SqlCommand("INSERT INTO Business (Name, Description) VALUES (@Name, @Description); SELECT SCOPE_IDENTITY();", conn);
7: cmd.Transaction = trans;
8: cmd.Parameters.AddWithValue("@Name", txtBusinessName.Text);
9: cmd.Parameters.AddWithValue("@Description", txtContactInfo.Text);
10: var busId = Convert.ToInt32(cmd.ExecuteScalar());
11:
12: cmd = new SqlCommand("INSERT INTO BusinessRecommendation (BusinessID, Name, DisplayName, StreetAddress, Description) VALUES (@BusinessID, @Name, @DisplayName, @StreetAddress, @Description)", conn);
13: cmd.Transaction = trans;
14: cmd.Parameters.AddWithValue("@BusinessID", busId);
15: cmd.Parameters.AddWithValue("@Name", txtName.Text);
16: cmd.Parameters.AddWithValue("@DisplayName", txtDisplayName.Text);
17: cmd.Parameters.AddWithValue("@StreetAddress", txtAddress.Text);
18: cmd.Parameters.AddWithValue("@Description", htmDescription.Html);
19: cmd.ExecuteNonQuery();
20:
21: trans.Commit();
22:
23: }
24: catch
25: {
26: trans.Rollback();
27: throw;
28: }
29: finally
30: {
31: conn.Close();
32: conn.Dispose();
33: conn = null;
34: }
If you are wondering why line # 10 converts the value to an Integer, SCOPE_IDENTITY returns a numeric value which is translated to be a Decimal because an identity field in Sql Server is not required to be an integer.