Using Auto-Incrementing Columns Without Causing Conflicts


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


Use the AutoIncrementSeed and AutoIncrementStep properties of the AutoIncrement column.

The sample code contains two event handlers:


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

 // 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;


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.

Connecting to Data

Retrieving and Managing Data

Searching and Analyzing Data

Adding and Modifying Data

Copying and Transferring Data

Maintaining Database Integrity

Binding Data to .NET User Interfaces

Working with XML

Optimizing .NET Data Access

Enumerating and Maintaining Database Objects

Appendix A. Converting from C# to VB Syntax

ADO. NET Cookbook
ADO.NET 3.5 Cookbook (Cookbooks (OReilly))
ISBN: 0596101406
EAN: 2147483647
Year: 2002
Pages: 222
Authors: Bill Hamilton © 2008-2020.
If you may any questions please contact us: