[ Team LiB ]

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
p
rimary 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

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)

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 &&

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" +