Get the ID of a Newly Inserted Row Using SCOPE_IDENTITY()
Occasionally you might insert a row into a database and find yourself needing to get the ID of that newly created row. Certainly you could just use a select statement and get the ID of the most recent row, but this leaves a chance that someone else will insert a row between when you insert your row and when you perform the select.
In the past, it was common to use "SELECT @@IDENTITY" to get the latest ID, but there is a better way as of SQL Server 2000. @@Identity will return the last identity value that was generated in the current session but in any scope. The newer "SCOPE_IDENTITY()" command will give you the last identity value that was generated in the current scope. What's the difference here? Well, if you've got triggers on your database to insert new rows, @@Identity is going to give you the ID of the last row created by the triggers, not the ID of the last row you inserted. In most instances, you'll want the ID of the row you inserted, which is just what SCOPE_IDENTITY() will give you.
In order to stay within the same scope, you'll need to execute the commands together. The example below does this with inline SQL, but you'll probably want to use a stored procedure with both these commands together when you use them in your apps. If you execute them seperatly, all SCOPE_IDENTITY() will return is a NULL value.
using (SqlConnection MyConnection = new SqlConnection("I'm hiding this info because I don't want you in my database"))
DateTime postDate = DateTime.Now;
string sqlString = "INSERT INTO MyTable (BoardID, Topic, UserID) VALUES (@BoardID, @Topic, @UserID); SELECT NewID = SCOPE_IDENTITY ()";
SqlCommand objCmd = new SqlCommand(sqlString, MyConnection);
objCmd.Parameters.Add(new SqlParameter("@BoardID", boardID));
objCmd.Parameters.Add(new SqlParameter("@Topic", topic);
objCmd.Parameters.Add(new SqlParameter("@UserID", currentUser.UserID));
SqlDataReader dataReader = objCmd.ExecuteReader();
newRowID = Convert.ToInt32(dataReader["NewID"]);
Note that there are a few parameter names that aren't defined in the code I displayed above (boardID, topicName, currentUser.UserID). I borrowed this code from a forum that I am writing and none of these fields are necessary to illustrate the ideas presented in this example. So remove them and create the INSERT however you like, the important part is that you put the "SELECT NewID = SCOPE_IDENTITY()" after you insert your new row.
Always use using blocks with database connections too... it's the only surefire way to make sure your connections get closed after they're used.
This article has been view 10466 times.