Uncovering ADO.NET's Secret Identity

Editor's Note: This article is adapted from Chapter 29 of Michael Otey's book ADO.NET: The Complete Reference (Osborne/McGraw-Hill, 2003)

SQL Server's IDENTITY columns let SQL Server automatically assign unique values to table rows. When you add a row to a table, the application you're using doesn't need to know anything about the row's unique identity number; SQL Server just handles that part automatically. However, in some cases, the application or the user needs to know the IDENTITY column's assigned value as soon as you add the row to the database. For example, an order-entry application that uses an IDENTITY column for the order ID might need to provide an order-confirmation number to the customer immediately. In such a case, you need a way to retrieve the value of the IDENTITY column and display it in your application. ADO.NET lets you retrieve and display this value in a couple of ways. You can use an output parameter of a stored procedure that performs the insert action, or you can use the SqlDataAdapter object's RowUpdated method.

Returning Identity Values by Using a Stored Procedure

The simplest way to get the value of an IDENTITY column is to set up the identity value as an output parameter of a stored procedure and use the SqlDataAdapter to map that value back to the column in the DataSet. Listing 1, page 20, shows a stored procedure that inserts an employee record into the Northwind database's Employees table and returns the identity field of EmployeeID as an output parameter. The most important point to notice about this stored procedure is the use of the SCOPE_IDENTITY() function, which retrieves the identity value SQL Server assigns, then sets that value into the @Identity variable that the stored procedure returns.

After you create the stored procedure, you can assign it to the InsertCommand property of a SqlDataAdapter object. Then, anytime you use the SqlDataAdapter to insert a row into the Employees table, the stored procedure will return the identity value to the application. Listing 2 shows an example of code that uses a stored procedure to insert a row and returns the corresponding identity value. First, the code at callout A in Listing 2 passes the server and database names into the top of the routine and creates a SqlConnection object. At callout B, the subroutine creates a SqlDataAdapter object. The code uses a T-SQL SELECT statement as the first argument in the constructor and the connection object as the second argument. Then, the code creates a SqlCommand object and assigns it to the SqlDataAdapter's InsertCommand property. In this case, the first argument is the name of the stored procedure, spEmployees-InsertCmd, and I've set the SqlCommand object's CommandType property to CommandType.StoredProcedure.

At callout C, the subroutine creates a DataSet and populates it by using the SqlDataAdapter's Fill method inside the Try-Catch loop. The next statements create SqlParameter objects for each of the columns that the spEmployeesInsertCmd stored procedure will use and add the SqlParameter objects to the growing collection of parameters for the SqlDataAdapter's InsertCommand. Each SqlParameter object is created using a parameter marker, data type, length, and source column that corresponds to the values needed for the associated spEmployeesInsertCmd stored procedure parameters. The last parameter, @Identity, is an output parameter that contains the identity value that SQL Server will assign after a row is inserted. In the last line of callout C, you can see that the parameter object's Direction property is set to ParameterDirection.Output.

After defining the parameters, the subroutine creates a new DataRow object and assigns values for each of the input parameters, as callout D shows. First, the code uses the NewRow method to initialize a new row in the Employees DataTable and returns a DataRow object, sqlDR. The code then assigns data to the new DataRow's input columns and uses the Add method to add the new row to the Employees DataTable's Rows collection. At this point, the Add method has updated the DataTable, but the update still hasn't been posted to SQL Server. To send the update to SQL Server, the code at callout E calls the SqlDataAdapter's Update method, which executes the DataAdapter's InsertCommand (which points to the spEmployeesInsert-Cmd stored procedure) and inserts the new row into the Employees database table. SQL Server assigns an identity value for the inserted row. Then, the spEmployeesInsertCmd stored procedure returns the identity value that SQL Server assigned the new row and displays that value in the TextBox txtIdent.

Returning Identity Values by Using the OnRowUpdated Event

Although using a stored procedure with the SCOPE_IDENTITY() function is the most straightforward method of retrieving identity values, in some cases you can't use a stored procedure to perform the insert. For example, you might be working with a third-party application that prevents you from modifying the database.

For these cases, you can use the RowUpdated event of the SqlDataAdapter to retrieve the newly generated identity value to the DataSet row. The code in Listing 3 shows how to use the RowUpdated event to return the identity value of the EmployeeID column after you insert a row into the Northwind database's Employees table. This example starts with a SqlConnection object, which I created outside the scope of the subroutine so that I can use the same connection object in the subroutines that Listing 3 and Listing 4 show.

The code at callout A in Listing 3 starts by passing the server and database names to the top of the routine and setting the SqlConnection object's ConnectionString property. The code creates a SqlDataAdapter object by using a T-SQL SELECT statement as the first argument in the constructor and the SqlConnection object as the second argument. At callout B, the subroutine creates a SqlCommand object and assigns that object to the SqlDataAdapter's InsertCommand property. At this point, the code creates the SqlCommand object, for which the first argument is a T-SQL INSERT statement, and sets the SqlCommand object's CommandType property to CommandType.Text.

Next, the code at callout C creates a DataSet and populates it by using the SqlDataAdapter's Fill method inside the Try-Catch loop. In this case, instead of letting the Fill method open and close the connection to the database, the code calls the SqlConnection's Open method to open the connection. This technique lets the On-RowUpdated event handler (which I use later in the subroutine that Listing 4 shows) use the SqlConnection Open connection object.

The statements at callout D show how to create the SqlParameter objects and add them to the collection of parameters for the DataAdapter's InsertCommand. Each parameter is defined with a parameter marker, data type, length, and source column that corresponds to the values needed for the associated SqlCommand object's parameters. The code at callout E initializes a new row in the Employees DataTable and returns a DataRow object. Then, the subroutine enters data into each of the new DataRow's input columns and adds the new row to the Employees DataTable's Rows collection.

The most important point to notice in Listing 3 is the AddHandler statement at callout F, which adds an event handler for the SqlDataAdapter's RowUpdated event. This event will fire after a row in the database changes. The code then calls the SqlDataAdapter's Update method, which executes the SqlDataAdapter's InsertCommand and inserts the new row into the database table, causing SQL Server to assign a new identity value. The Update method causes the RowUpdated event to fire, then the OnRowUpdated subroutine that Listing 4 shows executes.

Listing 4's OnRowUpdated subroutine retrieves the identity value for the newly inserted row. Inside the OnRowUpdated subroutine, you can see that an iEmpId variable is initialized to 0. Next, the code creates a SqlCommand object that uses a SELECT statement to return an IDENTITY column as the first argument and the open connection as the second argument. (Remember that I defined the sqlConnection object with a global scope so that I could use it in more than one example subroutine.) The code then checks the StatementType. If the StatementType isn't an INSERT, the subroutine terminates. If the statement that fired the event is an INSERT statement, the SqlCommand executes the ExecuteScalar method by using a SELECT @@IDENTITY command. The Execute-Scalar method returns just the first column of the first row in the query result. In this case, the SqlCommand query returns the new identity number, which the subroutine puts in the EmployeeID column of the current row in the Employees DataTable. Finally, the subroutine closes the connection to the data source.

Behind the Secret Identity

ADO.NET's disconnected nature is great for developing highly scalable Web applications, but it can also make some development tasks—such as retrieving IDENTITY column values—a bit tougher than they were in the connected model. By using the techniques in this article, you can get your ADO.NET applications to reveal the secrets of your SQL Server IDENTITY columns when you and your customers need to know them.