Retrieving Identity or Autonumber Values into Datasets

More Information on installing the .Net Framework click here.
Download full Visual Studio C# .NET Examples from this Article.

Overview

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 fields in Microsoft SQL Server

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.

Example Using Stored Procedure

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 SCOPE_IDENTITY() as an output parameter.

Create and test the stored procedure on SQL Server 2000

DROP PROCEDURE InsertCategory
GO

CREATE PROCEDURE InsertCategory
  @CategoryName nchar(15),
  @Identity int OUTPUT
AS
INSERT INTO Categories (CategoryName) VALUES(@CategoryName)
SET @Identity = SCOPE_IDENTITY()
GO

DECLARE @answer int
EXECUTE InsertCategory 'Akadia',@answer OUTPUT
SELECT 'Result = ', @answer
GO

The InsertCategory stored procedure can then be specified as the source of the DataAdapter.InsertCommand. A parameter is created to receive the identity output parameter. That parameter has a Direction of ParameterDirection.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.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;


namespace Akadia.AutoIncrement
{
    public class AutoIncrement : System.Windows.Forms.Form
    {
        private System.Windows.Forms.DataGrid dgShowCat;
        private System.Windows.Forms.Label lblUpdate;
        private System.Windows.Forms.Button btnUpdate;
        private System.ComponentModel.Container components = null;

        private DataSet catDS;
        private SqlDataAdapter catDA;
        private System.Windows.Forms.TextBox txtCatName;

        public AutoIncrement()
        {
            // Initialize GUI
            InitializeComponent();

            // Fetch Categories Table into DataSet
            SqlConnection nwindConn = new SqlConnection("Data Source=XEON; " +
                "Initial Catalog=Northwind; User id=sa; Password=manager;");

            catDA = new SqlDataAdapter("SELECT CategoryID, "+
                "CategoryName FROM Categories ORDER BY CategoryID", nwindConn);

            catDA.InsertCommand = new SqlCommand("InsertCategory", nwindConn);
            catDA.InsertCommand.CommandType = CommandType.StoredProcedure;

            catDA.InsertCommand.Parameters.Add("@CategoryName",
                SqlDbType.NChar, 15, "CategoryName");

            SqlParameter myParm = catDA.InsertCommand.Parameters.Add(
                "@Identity", SqlDbType.Int, 0, "CategoryID");
            myParm.Direction = ParameterDirection.Output;

            nwindConn.Open();

            catDS = new DataSet("CategoriesDS");
            catDA.Fill(catDS, "Categories");
            dgShowCat.SetDataBinding(catDS,"Categories");

            nwindConn.Close();
        }

        // Update Dataset using the Stored Procedure and fetch
        // Autoincrement Value back to the Dataset
        private void btnUpdate_Click(object sender, System.EventArgs e)
        {
            DataRow newRow = catDS.Tables["Categories"].NewRow();
            newRow["CategoryName"] = txtCatName.Text;
            catDS.Tables["Categories"].Rows.Add(newRow);

            catDA.Update(catDS, "Categories");
        }

        ....
        ....

        [STAThread]
        static void Main()
        {
            Application.Run(new AutoIncrement());
        }
    }
}

Example Using INSERT followed by SELECT

When working with auto-incrementing columns at a data source and auto-incrementing columns in a DataSet, you create the column in the DataSet with an AutoIncrementStep of -1 and an AutoIncrementSeed of 0, as well as ensuring that your data source generates auto-incrementing identity values starting from 1 and  incrementing with a positive step value. As a result, the DataSet generates negative numbers for auto-incremented values that do not conflict with the positive auto-increment values generated by the data source.

Next, you'll need to select the autoincrement value from the server back into the DataSet upon update. SQL Server provides a couple of ways to do this, including the @@Identity variable that returns the ID of the last row inserted within your scope. Finally, if you want to continue using the DataSet on the client, you would want to update the DataSet on the client by mergine the DataSet updated with new values from the server. In order to do this, you need to make sure that the DataAdapter does not accept changes when it updates the rows in the DataSet; otherwise the inserted row that has been updated with the new values will lose the original primary key values, and when you merge with the original dataset it will be added as a new row. In order to prevent the DataAdapter from calling AcceptChanges, you'll need to intercept the OnRowChanged event and tell the DataAdapter to skip calling AcceptChanges on the current row. It's not all as complicated as it sounds. The following code example shows the same example as above. In order to have the returned value applied to the DataSet, it specifies that the source of data to use to update the row is the first returned record. Finally, an event handler is registered which intercepts the OnRowChanged event in order to prevent AcceptChanges from being called.

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
namespace Akadia.AutoIncrement
{
    public class AutoIncrement : System.Windows.Forms.Form
    {
        private System.Windows.Forms.DataGrid dgShowCat;
        private System.Windows.Forms.Label lblUpdate;
        private System.Windows.Forms.Button btnUpdate;
        private System.ComponentModel.Container components = null;
        private DataSet catDS;
        private SqlDataAdapter catDA;
        private System.Windows.Forms.TextBox txtCatName;
        public AutoIncrement()
        {
            // Initialize GUI
            InitializeComponent();
            // Fetch Categories Table into DataSet
            SqlConnection nwindConn = new SqlConnection("Data Source=XEON; " +
                "Initial Catalog=Northwind; User id=sa; Password=manager;");
            catDA = new SqlDataAdapter("SELECT CategoryID, "+
                "CategoryName FROM Categories ORDER BY CategoryID", nwindConn);
           // Change the INSERT command text to include a SELECT
            // command as well, and make sure the INSERT command's
            // UpdatedRowSource is set to UpdateRowSource.FirstReturnedRecord
            SqlCommand cmdIn = new SqlCommand(
                "INSERT INTO Categories(CategoryName) VALUES (@CategoryName);" +
                "SELECT CategoryID, CategoryName " +
                "  FROM Categories " +
                " WHERE (CategoryID = @@IDENTITY)",nwindConn);

            cmdIn.Parameters.Add("@CategoryName",
                typeof(String)).SourceColumn="CategoryName";
            catDA.InsertCommand = cmdIn;
            // The data in the first returned row is mapped to the
            // changed row in the DataSet.
            cmdIn.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord;
            // Set UpdateRowSource and Register RowUpdatedEventHandler
           catDA.RowUpdated += new SqlRowUpdatedEventHandler(myHandler);
            DataTable catTable = new DataTable("Categories");
            // Fetch Data into Dataset
            nwindConn.Open();
            catDS = new DataSet("CategoriesDS");
            catDA.Fill(catDS, "Categories");
            dgShowCat.SetDataBinding(catDS,"Categories");
            nwindConn.Close();
        }
        // Next, you'll need to select the autoincrement
        // value from the server back into the DataSet upon update.
        // SQL Server provides a couple of ways to do this, including
        // the @@Identity variable that returns the ID of the last row inserted
        // within your scope. Finally, if you want to continue using the DataSet
        // on the client, you would want to update the DataSet on the client by
        // mergine the DataSet updated with new values from the server. In order
        // to do this, you need to make sure that the DataAdapter does not accept
        // changes when it updates the rows in the DataSet; otherwise the inserted
        // row that has been updated with the new values will lose the
        // original primary key values, and when you merge with the original
        // dataset it will be added as a new row. In order to prevent the
        // DataAdapter from calling AcceptChanges, you'll need to intercept
        // the OnRowChanged event and tell the DataAdapter to skip calling
        // AcceptChanges on the current row.
        public static void myHandler(object adapter, SqlRowUpdatedEventArgs e)
        {
            // Don't call AcceptChanges
            e.Status = UpdateStatus.SkipCurrentRow;
        }
        // Update Dataset using the INSERT Command and fetch
        // Autoincrement Value back to the Dataset
        private void btnUpdate_Click(object sender, System.EventArgs e)
        {
            // When working with auto-incrementing columns at a data source
            // and auto-incrementing columns in a DataSet, you create the column
            // in the DataSet with an AutoIncrementStep of -1 and an
            // AutoIncrementSeed of 0, as well as ensuring that your data source
            // generates auto-incrementing identity values starting from 1 and
            // incrementing with a positive step value. As a result, the DataSet
            // generates negative numbers for auto-incremented values that do not
            // conflict with the positive auto-increment values generated by
            // the data source.
            catDS.Tables["Categories"].Columns["CategoryID"].AutoIncrementStep = -1;
            catDS.Tables["Categories"].Columns["CategoryID"].AutoIncrementSeed = 0;
            // Create a new Row and UPDATE it to the database
            DataRow newRow = catDS.Tables["Categories"].NewRow();
            newRow["CategoryName"] = txtCatName.Text;
            catDS.Tables["Categories"].Rows.Add(newRow);
            catDA.Update(catDS, "Categories");
        }
       ...
        ....

        [STAThread]
        static void Main()
        {
            Application.Run(new AutoIncrement());
        }
    }
}