Implement SCOPE_IDENTITY() in Data Access Layer

The SCOPE_IDENTITY() function are used in Insert queries to return the last identity value within your table. However, I never knew how to retrieve the ID value when using this function in my code.

Create an Insert Query in your Data Access Layer called "InsertUser". For example:

INSERT INTO Users (FirstName, LastName, DateOfBirth, Email, City) VALUES (@FirstName, @LastName, @DateOfBirth, @Email, @City);
SELECT SCOPE_IDENTITY() 

When you return to the DataSet Designer you'll see that the "InsertUser" method has been created. If this new method doesn't have a parameter for each column in the table, chances are you forgot to terminate the INSERT statement with a semi-colon. Configure the "InsertUser" method and ensure you have a semi-colon delimiting the INSERT and SELECT statements.

By default, insert methods issue non-query methods, meaning that they return the number of affected rows. However, we want the "InsertUser" method to return the value returned by the query, not the number of rows affected. To accomplish this, adjust the "InsertUser" method's ExecuteMode property to Scalar (this can be found in the Properties panel on the right).

The following code will put your new Insert Query into action:

int intUserID = Convert.ToInt32(BLL.InsertUser("John", "Doe", "16/06/1985", "joe@hotmail.com", "Oxford"));
//Output new User ID
Response.Write("New User ID Inserted: " + intUserID); 

For more info regarding the use of Data Access in ASP.NET 2.0 go to: http://msdn2.microsoft.com/en-us/library/Aa581778.aspx