ADO.NET Cookbook

4.1. Using Auto-Incrementing Columns Without Causing Conflicts

Problem

You want to use an AutoIncrement column in a table without producing values that may be duplicated in records added by other users.

Solution

Use the AutoIncrementSeed and AutoIncrementStep properties of the AutoIncrement column.

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 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 C# code is shown in Example 4-1.

Example 4-1. File: AutoIncrementWithoutConflictForm.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";

private DataTable dt;

//  . . . 

private void AutoIncrementWithoutConflictForm_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));

    // Fill the table.
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Categories",
        ConfigurationSettings.AppSettings["Sql_ConnectString"]);
    da.Fill(dt);

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

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

Discussion

An AutoIncrement column generates a series of values beginning with the AutoIncrementSeed value and is incremented by the AutoIncrementStep value with each new value. This easily allows you to generate unique values for an integer-type column. A potential problem occurs when new rows are being inserted into an existing table for an identity field (in SQL Server) where the generated values conflict with existing values in the table because of, perhaps, new records added to the data source by other users. In this case, instead of being interpreted as new records by the data source, these records are incorrectly interpreted as updates of existing records.

The problem can be avoided by setting the AutoIncrementSeed value to -1 and the AutoIncrementStep value to -1 thereby generating a sequence of negative values that does not conflict with the values generated by the data source, as long as the data source does not generate negative values. When the disconnected data is reconciled with the underlying data (see Recipe 4.2), the data source correctly identifies the records that have negative AutoIncrement field values as new records, adds them to the data source, and in the process generates new values for the AutoIncrement field. Recipe 4.2 discusses synchronizing these data source-generated values with the disconnected data.