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