Retrieving the Just-Inserted ID of an IDENTITY Column Using a SqlDataSource Control

Introduction

ASP.NET offers a variety of tools and mechanisms for working with database data, including a number of data source controls, such as the SqlDataSource, ObjectDataSource, and LinqDataSource, among others. The SqlDataSource is one of the most basic data source controls as it operates directly against a configured database. Using the SqlDataSource control, an ASP.NET developer can retrieve, insert, update, or delete data by simply setting a few properties. Little to no code is needed. While the SqlDataSource makes it a walk in the park to implement the most common data access scenarios, a little extra effort is needed for more intricate scenarios. One such data access pattern is retrieving the value of the just-inserted record's ID field, where the ID field is an IDENTITY column. (An IDENTITY column is a numeric column in a SQL Server database table that has its value automatically assigned when a new record is added to the table. IDENTITY columns are sometimes referred to as auto-number columns, as well.) Being able to get the ID value of the just-inserted record is helpful in cases where you need to insert a new record and then insert other records into related tables, or when you want to let the user start working with the just-added record, which might entail taking them to a URL like EditRecord.aspx?ID=justInsertedRecordID.
This article shows how to use the SqlDataSource control to insert a new record and retrieve the value of its ID field. In particular, we will look at two examples: one that uses a stored procedure to insert the new record and another that uses an ad-hoc INSERT statement. Read on to learn more!

An IDENTITY Column Overview

Most database tables provide some mechanism to uniquely identify each record. There are a variety of approaches, but a very common one is the use of an IDENTITY column, which is also referred to as an auto-number. An IDENTITY column is one whose value is automatically assigned by the database system when a new record is added to the table. These values start at some seed (usually 1) and increment by some specified amount with each new record (usually incremented by 1). Therefore, if you add three new records to the table, the IDENTITY column values for those first three records will be 1, 2, and 3, respectively. When using IDENTITY columns a common requirement is to be able to retrieve the value of the just-inserted record's IDENTITY column. Perhaps after inserting a new record you want to automatically whisk the user to a details page where you need to pass along the IDENTITY column value through the querystring, or maybe you want to add additional records in a child table and need the just-inserted parent record's IDENTITY column value to properly link the child records to the parent. In either case, in Microsoft SQL Server you can use the SCOPE_IDENTITY() function to get the IDENTITY column value of the just-insert record.
In order to pull back this information when using the SqlDataSource we need to do the following:

1. Define the INSERT statement. This can be performed in a stored procedure or via an ad-hoc SQL statement.
2. Return the just-inserted IDENTITY value using the SCOPE_IDENTITY() function and assigning the value to an output parameter.
3. To access the resulting output parameter's value we need to create an event handler for the SqlDataSource's Inserted event. This event fires after the insert "action" has been performed. Once we have the IDENTITY value of the just-inserted record we can use it as needed.

The demo available for download at the end of this article includes two examples: one that shows how to get the just-insert record's ID value when the INSERT is performed by a stored procedure, and one that shows how to get the same results when using an ad-hoc SQL statement. Both examples use the Northwind database and insert records into the Products table.

Uniquely Identifying Records Using GUIDs

IDENTITY columns are one way to uniquely identify each record in a table. Another approach is to use a Globally Unique Identifier (GUID). One benefit of using a GUID is that it's easier to determine the value of the ID field because you can create the GUID in the code-behind class of your ASP.NET page. For instance, you can create a new GUID using the Guid class's NewGuid method and then assign this value to the appropriate parameter in the SqlDataSource control's Inserting event handler. With this approach it's possible to know the value of the new record's ID field before you've even sent the INSERT statement to the database! For more information on different ways to uniquely identify database records, be sure to read: Techniques for Uniquely Identifying Database Records.

Returning the Just-Inserted Record's ID Value Using an Ad-Hoc INSERT Statement

Before we look at how to have the SqlDataSource control return the just-inserted record's ID value, let's first focus on how to insert a new record and worry about getting back the ID value later. The SqlDataSource includes three properties for specifying a command to run when inserting data:

• InsertCommand - the text of the command to execute. Can be a stored procedure name or an ad-hoc query.
• InsertCommandType - this property tells the SqlDataSource control whether the InsertCommand is a stored procedure or an ad-hoc query.
• InsertParameters - information about the parameters used in the InsertCommand. Usually this collection includes a series of <asp:Parameter> objects whose values are automatically populated by a data Web control like the DetailsView or FormView through two-way data binding.

To configure the SqlDataSource to use an ad-hoc INSERT query to add a new record to the Products table might set the InsertCommand and InsertParameters properties as follows:

<asp:SqlDataSource ID="ProductsDataSource" runat="server"    InsertCommand="INSERT INTO [Products] ([ProductName], [CategoryID], [UnitPrice], [Discontinued]) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued)"    ...>    <InsertParameters>       <asp:Parameter Name="ProductName" Type="String" />       <asp:Parameter Name="CategoryID" Type="Int32" />       <asp:Parameter Name="UnitPrice" Type="Decimal" />       <asp:Parameter Name="Discontinued" Type="Boolean" />    </InsertParameters> </asp:SqlDataSource>

The above ad-hoc INSERT statement adds a new record to the Products table specifying values for the ProductName, CategoryID, UnitPrice, and Discontinued columns. These parameters are specifying in the InsertParameters as <asp:Parameter> objects. The InsertCommandType is not explicitly set; its default value is Text, which means that the SqlDataSource control parses the InsertCommand as an ad-hoc query.
To complete this example we would add a DetailsView to the page, bind it to the ProductsDataSource SqlDataSource, configure the DetailsView to support inserting, and customize the inserting interface as necessary. These steps have been performed in the demo, but I'm going to skip over them here as these topics are discussed in detail in my Accessing and Updating Data in ASP.NET article series. In particular, see the Inserting Data and Customizing the Editing Interface installments. The screen shot below shows the DetailsView in action.

At this point we have a page that enables the user to add a new product to the database. However, we do not know the value of the newly added product's ID, ProductID. To capture this information we need to update our INSERT statement to use SCOPE_IDENTITY() to retrieve the ProductID and assign it to an output parameter. Update the SqlDataSource control's InsertCommand property so that it includes the following:

INSERT INTO [Products] ([ProductName], [CategoryID], [UnitPrice], [Discontinued]) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued) SELECT @NewProductID = SCOPE_IDENTITY()

The INSERT statement is the same as before, but we've added SELECT @NewProductID = SCOPE_IDENTITY(). This assigns the just-inserted record's ID value to the output parameter @NewProductID.
We now need to update the InsertParameters collection to include this new parameter. Add the following declarative markup to the InsertParameters collection:

<asp:SqlDataSource ...>    <InsertParameters>       <asp:Parameter Name="ProductName" Type="String" />       <asp:Parameter Name="CategoryID" Type="Int32" />       <asp:Parameter Name="UnitPrice" Type="Decimal" />       <asp:Parameter Name="Discontinued" Type="Boolean" />       <asp:Parameter Direction="Output" Name="NewProductID" Type="Int32" />    </InsertParameters> </asp:SqlDataSource>

Note the addition of <asp:Parameter Direction="Output" Name="NewProductID" Type="Int32" />. This markup introduces a new parameter to the collection named NewProductID and indicates that its an output parameter of type Int32.
After inserting a product, the @NewProductID output parameter contains the value of the just-inserted record's ID field. To get at this information we need to create an event handler for the SqlDataSource's Inserted event. The Inserted event fires after the insert "action" has been performed, in this case after the ad-hoc INSERT statement has been sent to the database. (For more information on the SqlDataSource control's events, see Examining the Data Source Control's Events.)

Protected Sub ProductDataSource_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles ProductsDataSource.Inserted    'Read the value of the @NewProductID OUTPUT parameter    Dim newProductID As Integer = Convert.ToInt32(e.Command.Parameters("@NewProductID").Value)    ... End Sub

The demo includes a Label Web control that displays the ID of the just-inserted product, as shown by the screen shot below.

Add caption

That's all there is to it!

Returning the Just-Inserted Record's ID Value Using an Stored Procedure to Insert the Record

The SqlDataSource control can use stored procedures when querying, inserting, updating, and deleting data from the database. If you use a stored procedure to insert a record and need to retrieve the just-inserted ID value from your ASP.NET page, you can have the stored procedure return the ID value via an output parameter. The demo available for download at the end of this article includes a stored procedure named AddProductAndReturnNewProductIDValue that inserts a record into the Products table and returns the just-inserted ID using an output parameter named @NewProductID. The stored procedure's syntax follows:

ALTER PROCEDURE dbo.AddProductAndReturnNewProductIDValue (    @ProductName nvarchar(40),    @CategoryID int,    @UnitPrice money,    @Discontinued bit,     @NewProductID int OUTPUT ) AS -- Insert the record into the database INSERT INTO Products(ProductName, CategoryID, UnitPrice, Discontinued) VALUES (@ProductName, @CategoryID, @UnitPrice, @Discontinued) -- Read the just-inserted ProductID into @NewProductID SET @NewProductID = SCOPE_IDENTITY()

As you can see, the stored procedure includes an output parameter named @NewProductID and this value is assigned via the SCOPE_IDENTITY() function just like in our ad-hoc example. (For more on using output parameters with a stored procedure, see Retrieving Scalar Data from a Stored Procedure.
From the ASP.NET page, everything else is the same as with the ad-hoc example: you need to add an output parameter to the InsertParameters collection, and the value of the output parameter is accessible from the SqlDataSource control's Inserted event.

Conclusion

Retrieving the ID value of a just-inserted record is quite useful in many data access scenarios. SQL Server includes the SCOPE_IDENTITY() function for retrieving this information. To get this back to your ASP.NET page, you need to assign the value returned by the SCOPE_IDENTITY() function to an output parameter. The output parameter's value can then be accessed in the SqlDataSource control's Inserted event.

Download the code used in this article