Questions That Should Be Asked More Frequently

Retrieving Newly Generated Autoincrement Values

SQL Server, Access, Sybase, and other databases use autoincrement columns (also referred to as identity columns). You can insert a new row into a table, and the database will generate a new value for the autoincrement column for that row. Many tables in the Northwind database—such as Employees, Orders, and Products—use autoincrement columns for their primary keys.

Why does working with autoincrement columns constitute an advanced updating scenario? You can submit the new row to your table, but the database will generate the value for that row's autoincrement column. This means you won't know the value of the autoincrement column for your new row once you've submitted the new row to your table. Generally speaking, you want to know the primary key value for your rows.

So how do you use the ADO.NET object model to retrieve the newly generated autoincrement value for your row?

Working with SQL Server

For the moment, imagine that you're not submitting changes to your database using a DataAdapter. Let's say you're building your own queries to submit changes.

If you're working with order information from the Northwind database, you might use the following query to retrieve data from your table:

SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM Orders

To insert a new row into your table, you might issue a query like this:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (?, ?, ?)

To retrieve the autoincrement value that the database generated for your new row, you might use the following query:

SELECT @@IDENTITY

	Why "might use" rather than "should use"? See the discussion on @@IDENTITY vs. SCOPE_IDENTITY later in the chapter.

This query is the key to retrieving the new autoincrement value. We can use this query in the ADO.NET object model the same way we used the query to retrieve the timestamp value in the previous example.

We can modify the CommandText of the DataAdapter object's InsertCommand to execute the SELECT @@IDENTITY query after each insert:

INSERT INTO Orders (CustomerID, EmployeeID, OrderDate) VALUES (?, ?, ?); SELECT @@IDENTITY AS OrderID

Note that the SELECT @@IDENTITY query includes an alias so that the Command knows the column in which it should store the results of the query.

As with fetching new timestamp values, we can also return the new autoincrement value by using a stored procedure output parameter, as shown here:

CREATE PROCEDURE spOrdersInsert (@OrderID int OUTPUT, @CustomerID nchar(5),   @EmployeeID int, @OrderDate datetime) AS INSERT INTO Orders (CustomerID, EmployeeID, OrderDate)     VALUES (@CustomerID, @EmployeeID, @OrderDate) SELECT @OrderID = @@IDENTITY

Finally, we can use the DataAdapter object's RowUpdated event to execute a query to fetch the new autoincrement value, as shown in the following code samples:

Visual Basic .NET

Dim da As OleDbDataAdapter = CreateMyDataAdapter() Dim cn As OleDbConnection = da.SelectCommand.Connection Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", cn) AddHandler da.RowUpdated, AddressOf HandleRowUpdated Dim tbl As DataTable = CreateMyDataTable() da.Fill(tbl)  da.Update(tbl) Private Sub HandleRowUpdated(ByVal sender As Object, _                              ByVal e As OleDbRowUpdatedEventArgs)     If e.Status = UpdateStatus.Continue AndAlso _        e.StatementType = StatementType.Insert Then         e.Row("OrderID") = CType(cmdGetIdentity.ExecuteScalar, Integer)         e.Row.AcceptChanges()     End If End Sub

Visual C# .NET

OleDbDataAdapter da = CreateMyDataAdapter(); OleDbConnection cn = da.SelectCommand.Connection; OleDbCommand cmdGetIdentity = new OleDbCommand("SELECT @@IDENTITY", cn); da.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdated); DataTable tbl = CreateMyDataTable(); da.Fill(tbl);  da.Update(tbl); private void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e) {     if ((e.Status == UpdateStatus.Continue) &&        ((e.StatementType == StatementType.Insert))     {         e.Row["OrderID"] = (int) cmdGetIdentity.ExecuteScalar();         e.Row.AcceptChanges();     } }

This code snippet differs slightly from the code that fetched new timestamp values in the RowUpdated event in two ways. First, and most obviously, the query we're executing to fetch data is different.

The second difference is in performance. What's the fastest way to fetch your new autoincrement values? The performance numbers I generated in simple tests mirrored those from the timestamp tests. Stored procedure output parameters provided the best performance, with batched queries second and using the RowUpdated event a distant third.

@@IDENTITY vs. SCOPE_IDENTITY()

The SELECT @@IDENTITY query returns the last identity value generated on your connection. This means that work done by other users on other connections will not affect the results of your query. However, that does not mean you'll receive the value you expected.

Database administrators often use their own audit tables to track changes made to the database. To track those changes, they generally rely on triggers or stored procedures. Figure 11-2 shows an example.

Figure 11-2

Tracking changes using audit tables

Why have I drifted into a discussion of audit logs and triggers in the middle of a discussion of retrieving autoincrement values? Let's assume that the audit table that the trigger shown in Figure 11-2 references has an autoincrement column. If you insert a new row into the Orders table and then issue the SELECT @@IDENTITY query, you'll receive the autoincrement value that the trigger generated for the new row in the audit table.

Remember that SELECT @@IDENTITY returns the last autoincrement value generated for your connection.

To address this type of scenario, SQL Server 2000 introduced a new way to retrieve autoincrement values: SCOPE_IDENTITY(). If you issue a SELECT SCOPE_IDENTITY() query in this situation, you'll receive the autoincrement value generated for the new row in the Orders table.

If you're working with SQL Server 2000 or later or Microsoft Desktop Engine (MSDE) 2000 or later, you should consider using SCOPE_IDENTITY instead of @@IDENTITY. There's one minor exception to this rule. If you insert the new row using a stored procedure but you want to retrieve that value after calling the stored procedure, SCOPE_IDENTITY() will return Null. As I said, this is a minor exception. If you're going to insert new rows using stored procedures and you want to retrieve the newly generated autoincrement value, you should return this information using an output parameter.

For more information on the differences between @@IDENTITY and SCOPE_IDENTITY(), see SQL Server Books Online.

Working with Access 2000

If you're working with an Access database, you can also use the SELECT @@IDENTITY query to retrieve new autoincrement values. This feature was added in version 4.0 of the Jet OLE DB provider and works only with databases formatted for Access databases version 2000 or later. Like its SQL Server counterpart, the SELECT @@IDENTITY query returns the last autoincrement value generated on your connection.

Access databases do not support output parameters on QueryDefs—stored queries that are similar to views and stored procedures. The Jet OLE DB provider does not support batch queries. So the only way to fetch newly generated autoincrement values is to use the DataAdapter object's RowUpdated event, as shown earlier in the chapter.

Working with Oracle Sequences

Oracle databases do not support autoincrement columns, but they do support a similar construct—a sequence. With SQL Server, you mark a column as an autoincrement column and SQL Server will automatically generate new values for the column when you insert a new row. An Oracle sequence is slightly different. You generally create a sequence to generate new values for a column in your table, but there is no direct link between the sequence and the table or column. An Oracle sequence is an object, like a table or a stored procedure.

The following query creates a new Oracle sequence:

CREATE SEQUENCE MySequence

You can set a number of options when you create an Oracle sequence, such as the minimum and maximum values for the sequence.

	I don't claim to be an expert on Oracle sequences. I have managed to create and use them in simple INSERT and SELECT queries, but that's about the extent of my knowledge on the topic. For more information on Oracle sequences, see your Oracle documentation.

You can use the sequence in two ways. You can reference the sequence in your INSERT query, as shown here:

INSERT INTO MyTable (ID, OtherColumn)     VALUES (MySequence.NEXTVAL, 'New Row')

The sequence returns a new value each time you issue this query.

Once you've inserted the new row, you can query the sequence to determine the last value you used, as shown here:

SELECT MySequence.CURRVAL FROM DUAL

As with using the SELECT @@IDENTITY query with SQL Server and Access databases, the results of this query are not affected by other users referencing the sequence to insert new rows.

How do you retrieve the new sequence values into your DataRow objects? Oracle does not support batch queries that return data, so you can't use the Sequence.CURRVAL query in the CommandText of the InsertCommand. However, you can issue this query when you handle the DataAdapter object's RowUpdated event, much like how we used the RowUpdated event in previous examples:

Visual Basic .NET

Dim da As OleDbDataAdapter = CreateMyDataAdapter() Dim cn As OleDbConnection = da.SelectCommand.Connection Dim strSQL As String = "SELECT MySequence.CURRVAL FROM DUAL"

Dim cmdGetSequence As New OleDbCommand(strSQL, cn) AddHandler da.RowUpdated, AddressOf HandleRowUpdated Dim tbl As DataTable = CreateMyDataTable() da.Fill(tbl)  da.Update(tbl) Private Sub HandleRowUpdated(ByVal sender As Object, _                              ByVal e As OleDbRowUpdatedEventArgs)     If e.Status = UpdateStatus.Continue AndAlso _        e.StatementType = StatementType.Insert Then         e.Row("OrderID") = CType(cmdGetSequence.ExecuteScalar, Integer)         e.Row.AcceptChanges()     End If End Sub

Visual C# .NET

OleDbDataAdapter da = CreateMyDataAdapter(); OleDbConnection cn = da.SelectCommand.Connection; string strSQL = "SELECT MySequence.CURRVAL FROM DUAL"; OleDbCommand cmdGetSequence = new OleDbCommand(strSQL, cn); da.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdated); DataTable tbl = CreateMyDataTable(); da.Fill(tbl);  da.Update(tbl); private void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e) {     if ((e.Status == UpdateStatus.Continue) &&        ((e.StatementType == StatementType.Insert))     {         e.Row["OrderID"] = (int) cmdGetSequence.ExecuteScalar();         e.Row.AcceptChanges();     } }

You don't have to use Sequence.NEXTVAL in your INSERT INTO query. You can query the sequence before issuing the INSERT INTO query. The following code snippet creates a new procedure that queries a sequence for a new value and stores the new value in a variable. The procedure uses that value to insert the new row using an INSERT INTO query.

CREATE PROCEDURE MyStoredProc     (pOtherCol IN VARCHAR2, pID OUT NUMBER) IS BEGIN     SELECT MySequence.NEXTVAL INTO pID FROM DUAL;     INSERT INTO MyTable (ID, OtherCol)         VALUES (pID, pOtherCol); END;

The stored procedure returns the new sequence value using an output parameter. You can use this type of stored procedure in your DataAdapter object's InsertCommand to insert new rows. If you bind the output parameter to the corresponding column in your DataRow object, the DataRow will contain the new value immediately after you've submitted the new row to your database.

	Remember that there is no direct link between the sequence and the column in the table. You don't have to use the sequence when you insert a new row into the table. If users insert new rows without referencing the sequence, the sequence might generate new values that already exist in your database. To avoid the problem, make sure that the only way to insert new rows into your table is to call a stored procedure that references the sequence.

Generating Dummy Values for Your Sequence DataColumn Objects

Sequences are not autoincrement columns, but you can have ADO.NET generate dummy values for new rows by setting the AutoIncrement property of the corresponding DataColumn objects to True. However, you must do this manually. There is no direct link between the sequence and the table. If you use the DataAdapter object's FillSchema method or the Visual Studio .NET Data Adapter Configuration Wizard to retrieve database schema information, ADO.NET won't know that the column in your table is associated with a sequence.

You'll face similar problems when you use tools to generate updating logic for your DataAdapter objects. The CommandBuilder object and the Data Adapter Configuration Wizard won't know to omit the column from the logic in the InsertCommand. If you're going to rely on such tools to generate your updating logic, you must make some minor changes to the logic they generate.

It might sound like I'm knocking these tools. I'm not. There is no schema information that links the column in the table to the sequence, so the tools are doing the best they can to generate the appropriate code.

The fact that you can treat a column that's indirectly linked to an Oracle sequence as an autoincrement column and control how and when you retrieve new sequence values into your DataSet is a testament to the power and control that the ADO.NET object model gives to developers. Such functionality was not an option in previous Microsoft data access object models.

Sample Applications That Retrieve Autoincrement Values

I've covered a number of scenarios that deal with retrieving new autoincrement and sequence values into your DataSet. To retrieve new SQL Server autoincrement values, we can use batch queries, stored procedure output parameters, or the DataAdapter object's RowUpdated event. To retrieve new Access autoincrement values, we can use the DataAdapter object's RowUpdated event. And to retrieve new Oracle sequence values, we can use stored procedure output parameters and the DataAdapter object's RowUpdated event. That's a lot of choices.

To help you sort through all of this, the companion CD contains sample applications (in both Visual Basic .NET and Visual C# .NET) that cover each of these scenarios and options. Figure 11-3 shows one sample application, AutoIncrementSql. The CD also includes similar samples that cover retrieving autoincrement values from Access databases as well as sequence values from Oracle databases.

Figure 11-3

The AutoIncrementSql sample application