Retrieving Identity or Autonumber ValuesÂ
You can set a column in a DataTable to be an auto-incrementing primary key in order to ensure a unique value for each row in the table. However, you may have multiple clients for your application, and each of those clients can be working with a separate instance of the DataTable. In this case, you might end up with duplicate values between the separate instances of the DataTable. Because all your clients are working with a single data source, you can resolve this conflict by letting the data source define the auto-incremented value. To accomplish this you use identity columns in Microsoft SQL Server, or Autonumber fields in Microsoft Access.
Using the data source to populate an Identity or Autonumber column for a new row added to a DataSet creates a unique situation because the DataSet has no direct connection to the data source. As a result, the DataSet is unaware of any values generated automatically by the data source. However, with a data source that can create stored procedures with output parameters, such as Microsoft SQL Server, you can specify the automatically generated values, such as a new identity value, as an output parameter and use the DataAdapter to map that value back to the column in the DataSet.
Your data source may not support stored procedures with output parameters. In this case, you may be able to use the RowUpdated event to retrieve an automatically generated value and place it in the inserted or updated row in the DataSet. This section includes a sample that shows how, with Microsoft Access 2000 or later, and using the Jet 4.0 OLE DB Provider, you can add code to the RowUpdated event to determine if an insert has occurred and to retrieve the auto-incremented value and store it in the currently updated row.
Retrieving SQL Server Identity Column Values
The following stored procedure and code example show how to map the auto-incremented identity value from a Microsoft SQL Server table back to its corresponding column in a row added to a table in a DataSet. The stored procedure is used to insert a new row into the Categories table of the Northwind database and to return the Identity value returned from the Transact-SQL SCOPE_IDENTITY() function as an output parameter.
CREATE PROCEDURE InsertCategory
@CategoryName nchar(15),
@Identity int OUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
The InsertCategory stored procedure can then be specified as the source of the InsertCommand. A parameter is created to receive the Identity output parameter. That parameter has a ParameterDirection of Output, and has a SourceColumn specified as the CategoryID column of the local Categories table in the DataSet. When the InsertCommand is processed for an added row, the auto-incremented Identity value is returned as this output parameter and is placed in the CategoryID column of the current row.
The following code example shows how to return the auto-incremented value as the output parameter and specify it as the source value for the CategoryID column in the DataSet.
' Assumes that connection is a valid SqlConnection object.
Dim adapter As SqlDataAdapter = New SqlDataAdapter( _
"SELECT CategoryID, CategoryName FROM dbo.Categories", connection)
adapter.InsertCommand = New SqlCommand("InsertCategory", connection)
adapter.InsertCommand.CommandType = CommandType.StoredProcedure
adapter.InsertCommand.Parameters.Add( _
"@CategoryName", SqlDbType.NChar, 15, "CategoryName")
Dim parameter As SqlParameter = adapter.InsertCommand.Parameters.Add( _
"@Identity", SqlDbType.Int, 0, "CategoryID")
parameter.Direction = ParameterDirection.Output
connection.Open()
Dim categories As DataSet = New DataSet
adapter.Fill(categories, "Categories")
Dim newRow As DataRow = categories.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
categories.Tables("Categories").Rows.Add(newRow)
adapter.Update(categories, "Categories")
connection.Close()
// Assumes that connection is a valid SqlConnection object.
SqlDataAdapter adapter = new SqlDataAdapter(
"SELECT CategoryID, CategoryName FROM dbo.Categories", connection);
adapter.InsertCommand = new SqlCommand("InsertCategory", connection);
adapter.InsertCommand.CommandType = CommandType.StoredProcedure;
adapter.InsertCommand.Parameters.Add(
"@CategoryName", SqlDbType.NChar, 15, "CategoryName");
SqlParameter parameter = adapter.InsertCommand.Parameters.Add(
"@Identity", SqlDbType.Int, 0, "CategoryID");
parameter.Direction = ParameterDirection.Output;
connection.Open();
DataSet categories = new DataSet();
adapter.Fill(categories, "Categories");
DataRow newRow = categories.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
categories.Tables["Categories"].Rows.Add(newRow);
adapter.Update(categories, "Categories");
connection.Close();
Retrieving Microsoft Access Autonumber Values
Microsoft Access does not support stored procedures or batch command processing, so it is not possible to map an output parameter to the source column in the table in the preceding example. However, Microsoft Access 2000 or later does support the @@IDENTITY property to retrieve the value of an Autonumber field after an INSERT. Using the RowUpdated event, you can determine if an INSERT has occurred, retrieve the latest Autonumber value, and place that in the Identity column of the local table in the DataSet.
The following code example shows how to insert a new value into the Categories table of the Microsoft Access 2000 Northwind database using a OleDbDataAdapter. The example uses the RowUpdated event to fill in the Autonumber values generated by the Jet engine and the Access database when a record is inserted into the Categories table. Note that this will only work with the Jet 4.0 OLE DB provider and Microsoft Access 2000 or later.
' Assumes that connection is a valid OleDbConnection object.
' Use the DataAdapter to fill and update the DataSet.
Dim adapter As OleDbDataAdapter = New OleDbDataAdapter( _
"SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID", _
connection)
adapter.InsertCommand = New OleDbCommand( _
"INSERT INTO Categories (CategoryName) Values(?)", connection)
adapter.InsertCommand.CommandType = CommandType.Text
adapter.InsertCommand.Parameters.Add( _
"@CategoryName", OleDbType.Char, 15, "CategoryName")
connection.Open()
' Fill the DataSet.
Dim categories As DataSet = New DataSet
adapter.Fill(categories, "Categories")
' Add a new row.
Dim newRow As DataRow = categories.Tables("Categories").NewRow()
newRow("CategoryName") = "New Category"
categories.Tables("Categories").Rows.Add(newRow)
' Include an event to fill in the Autonumber value.
AddHandler adapter.RowUpdated, _
New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
' Update the DataSet.
adapter.Update(categories, "Categories")
connection.Close()
' Event procedure for OnRowUpdated
Private Shared Sub OnRowUpdated( _
sender As Object, args As OleDbRowUpdatedEventArgs)
' Include a variable and a command to retrieve the identity value
' from the Access database.
Dim newID As Integer = 0
Dim idCMD As OleDbCommand = New OleDbCommand( _
"SELECT @@IDENTITY", connection)
If args.StatementType = StatementType.Insert
' Retrieve the identity value and store it in the CategoryID column.
newID = CInt(idCMD.ExecuteScalar())
args.Row("CategoryID") = newID
End If
End Sub
// Assumes that connection is a valid OleDbConnection object.
OleDbDataAdapter adapter = new OleDbDataAdapter(
"SELECT CategoryID, CategoryName FROM Categories ORDER BY CategoryID",
connection);
adapter.InsertCommand = new OleDbCommand(
"INSERT INTO Categories (CategoryName) Values(?)", connection);
adapter.InsertCommand.CommandType = CommandType.Text;
adapter.InsertCommand.Parameters.Add( _
"@CategoryName", OleDbType.Char, 15, "CategoryName");
connection.Open();
// Fill the DataSet.
DataSet categories = new DataSet();
adapter.Fill(categories, "Categories");
// Add a new row.
DataRow newRow = categories.Tables["Categories"].NewRow();
newRow["CategoryName"] = "New Category";
categories.Tables["Categories"].Rows.Add(newRow);
// Include an event to fill in the Autonumber value.
adapter.RowUpdated += new OleDbRowUpdatedEventHandler(OnRowUpdated);
// Update the DataSet.
adapter.Update(categories, "Categories");
connection.Close();
// Event procedure for OnRowUpdated
protected static void OnRowUpdated(
object sender, OleDbRowUpdatedEventArgs args)
{
// Include a variable and a command to retrieve the identity value from the Access database.
int newID = 0;
OleDbCommand idCMD = new OleDbCommand(
"SELECT @@IDENTITY", connection);
if (args.StatementType == StatementType.Insert)
{
// Retrieve the identity value and store it in the CategoryID column.
newID = (int)idCMD.ExecuteScalar();
args.Row["CategoryID"] = newID;
}
}