Implement SCOPE_IDENTITY() in Data Access Layer

Published on
-
1 min read

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

Before you go...

If you've found this post helpful, you can buy me a coffee. It's certainly not necessary but much appreciated!

Buy Me A Coffee

Leave A Comment

If you have any questions or suggestions, feel free to leave a comment. I do get inundated with messages regarding my posts via LinkedIn and leaving a comment below is a better place to have an open discussion. Your comment will not only help others, but also myself.