Recipe 4.2 Getting an Identity Column Value from SQL Server

Problem

When you add a row into a SQL Server table that has an identity column, the value assigned to the column in the DataTable is replaced by a value generated by the database. You need to retrieve the new value to keep the DataTable synchronized with the database.

Solution

There are two ways to synchronize identity values generated by the data source: use either the first returned record or the output parameters of a stored procedure.

The sample uses a single stored procedure:

InsertCategories

Used to add a new Categories record to the Northwind database. The stored procedure returns the CategoryId value generated by the data source as both an output parameter and in the first returned record.

The sample code contains two event handlers:

Form.Load

Sets up the sample by creating a DataTable and programmatically defining the schema to match the Categories table in Northwind. The AutoIncrementSeed and AutoIncrementStep property values are both set to -1 for the AutoIncrement primary key column, the CategoryID. A DataAdapter is created and used to fill the DataTable. The insert command and its parameters are defined for the DataAdapter so that new rows can be added to the data source and the CategoryID value generated by the data source can be retrieved using either the output parameter values or first returned record from the InsertCategories stored procedure. The default view of the table is bound to the data grid on the form.

Add Button.Click

Creates a new row in the Categories DataTable using the entered CategoryName and Description values and the automatically generated CategoryID field. The Update( ) method of the DataAdapter is used to insert the row into the data source and synchronize the identity value generated by the data source to the AutoIncrement column value—its value, both before and after is displayed.

The C# code is shown in Example 4-2.

Example 4-2. Stored procedure: InsertCategories

CREATE PROCEDURE InsertCategories
    @CategoryId int output,
    @CategoryName nvarchar(15),
    @Description ntext
AS
    SET NOCOUNT ON
    
    insert Categories(
        CategoryName,
        Description)
    values (
        @CategoryName,
        @Description)
    
    if @@rowcount=0
        return 1
    
    set @CategoryID = Scope_Identity( )

    select Scope_Identity( ) CategoryId

    return 0

The C# code is shown in Example 4-3.

Example 4-3. File: IdentityValueForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

// Table name constants
private const String CATEGORIES_TABLE        = "Categories";

// Field name constants
private const String CATEGORYID_FIELD        = "CategoryID";
private const String CATEGORYNAME_FIELD      = "CategoryName";
private const String DESCRIPTION_FIELD       = "Description";

// Stored procedure name constants
public const String GETCATEGORIES_SP         = "GetCategories";
public const String INSERTCATEGORIES_SP      = "InsertCategories";

// Stored procedure parameter name constants for Categories table
public const String CATEGORYID_PARM          = "@CategoryID";
public const String CATEGORYNAME_PARM        = "@CategoryName";
public const String DESCRIPTION_PARM         = "@Description";

private DataTable dt;
private SqlDataAdapter da;

//  . . . 

private void IdentityValueForm_Load(object sender, System.EventArgs e)
{
    // Create the Categories table.
    dt = new DataTable(CATEGORIES_TABLE);

    // Add the identity column.
    DataColumn col = dt.Columns.Add(CATEGORYID_FIELD,
        typeof(System.Int32));            
    col.AllowDBNull = false;
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    // Set the primary key.
    dt.PrimaryKey = new DataColumn[] {col};

    // Add the other columns.
    col = dt.Columns.Add(CATEGORYNAME_FIELD, typeof(System.String));
    col.AllowDBNull = false;
    col.MaxLength = 15;
    dt.Columns.Add(DESCRIPTION_FIELD, typeof(System.String));

    // Create the DataAdapter.
    da = new SqlDataAdapter(GETCATEGORIES_SP,
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    da.SelectCommand.CommandType = CommandType.StoredProcedure;

    // Create the insert command for the DataAdapter.
    da.InsertCommand = new SqlCommand(INSERTCATEGORIES_SP,
        da.SelectCommand.Connection);
    da.InsertCommand.CommandType = CommandType.StoredProcedure;
    // Add the output parameter.
    SqlParameter param = da.InsertCommand.Parameters.Add(CATEGORYID_PARM,
        SqlDbType.Int, 0, CATEGORYID_FIELD);
    param.Direction = ParameterDirection.Output;
    // Add the other parameters.
    da.InsertCommand.Parameters.Add(CATEGORYNAME_PARM, SqlDbType.NVarChar,
        15, CATEGORYNAME_FIELD);
    da.InsertCommand.Parameters.Add(DESCRIPTION_PARM, SqlDbType.NText,
        0, DESCRIPTION_FIELD);

    // Fill the table with data.
    da.Fill(dt);

    // Bind the default table view to the grid.
    dataGrid.DataSource = dt.DefaultView;
}

private void addButton_Click(object sender, System.EventArgs e)
{
    // Add the row to the Category table.
    DataRow row = dt.NewRow( );
    row[CATEGORYNAME_FIELD] = categoryNameTextBox.Text;
    row[DESCRIPTION_FIELD] = descriptionTextBox.Text;
    dt.Rows.Add(row);

    resultTextBox.Text = "Identity value before update = " +
        row[CATEGORYID_FIELD] + Environment.NewLine;

    // Set the method used to return the data source identity value.
    if(outputParametersCheckBox.Checked &&
        firstReturnedRecordCheckBox.Checked)
        da.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
    else if(outputParametersCheckBox.Checked)
        da.InsertCommand.UpdatedRowSource =
            UpdateRowSource.OutputParameters;
    else if(firstReturnedRecordCheckBox.Checked)
        da.InsertCommand.UpdatedRowSource =
            UpdateRowSource.FirstReturnedRecord;
    else
        da.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

    // Update the data source.
    da.Update(dt);

    resultTextBox.Text += "Identity value after update = " +
        row[CATEGORYID_FIELD];
}

Discussion

As discussed in Recipe 4.1, the AutoIncrementSeed and AutoIncrementStep property values for the AutoIncrement column should both be set to -1 to prevent conflict with the positive identity values generated by the data source.

The values created for an AutoIncrement column will have new identity values generated by the data source when they are updated back to the data source. There are two ways in which the data source generated value can be retrieved and this solution demonstrates both. The UpdatedRowSource property of the Command object specifies how results from calling the Update( ) method of the DataAdapter are applied to the DataRow. Table 4-1 lists possible values.

Table 4-1. Values for the UpdateRowSource enumeration

Value	Description
Both	Both the data in the first returned row and the output parameters are mapped to the DataSet row that has been inserted or updated.This is the default value unless the command is generated by a CommandBuilder.
FirstReturnedRecord	The data in the first returned row is mapped to the DataSet row that has been inserted or updated.
None	Return values and parameters are ignored.This is the default value if the command is generated by a CommandBuilder.
OutputParameters	Output parameters are mapped to the DataSet row that has been inserted or updated.

The stored procedure InsertCategories has a single output parameter @CategoryId that is used to return the value of the data source generated identity value. The value is set to the new identity value by the stored procedure statement:

set @CategoryID = Scope_Identity( )

The column to be updated in the row is identified by the source column of the Parameter object, in this case, the fourth argument in the constructor.

The stored procedure also returns a result set containing a single row with a single value—CategoryId—containing the new identity value generated by the data source. The result set is returned by the stored procedure statement:

select Scope_Identity( ) CategoryId

The columns are updated from the data source to the row matching column names, taking into account any column mappings that might be in place.

You can also apply the FirstReturnedRecord when using a batch SQL statement. Replace the InsertCommand command constructor for the DataAdapter with the following code:

// Create the insert command for the DataAdapter.
String sqlText="INSERT Categories(CategoryName, Description) VALUES" +
    "(@CategoryName, @Description);" + 
    "SELECT Scope_Identity( ) CategoryId";
da.InsertCommand = new SqlCommand(sqlText, da.SelectCommand.Connection);
da.InsertCommand.CommandType = CommandType.Text;

Batch SQL commands do not support output parameters, so only the FirstReturnedRecord method will work with a batch SQL command.

The SCOPE_IDENTITY( ) function was introduced in SQL Server 2000 to make it easier to work with identity values. While SCOPE_IDENTITY( ) and @@IDENTITY both return the last identity value generated in any column in the current session, SCOPE_IDENTITY( ) returns values inserted within the current scope while @@IDENTITY is not limited to the current scope. For more information, see Microsoft SQL Server Books Online.