ADO.NET 3.5 Cookbook, 2nd Edition

5.1. Using Autoincrementing Columns Without Causing Conflicts

Problem

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

Solution

Use the AutoIncrementSeed and AutoIncrementStep properties of the AutoIncrement column.

The solution creates a DataTable and uses a DataAdapter to load schema and data from the HumanResources.Department table in AdventureWorks. The AutoIncrementSeed and AutoIncrementStep property values are both set to –1 for the AutoIncrement primary key column DepartmentID. Two new records are added to the DataTable. The contents of the DataTable are written to the console.

The C# code in Program.cs in the project AutoIncrementWithoutConflict is shown in Example 5-1.

Example 5-1. File: Program.cs for AutoIncrementWithoutConflict solution

using System; using System.Data; using System.Data.SqlClient; namespace AutoIncrementWithoutConflict { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "SELECT * FROM HumanResources.Department"; // Create and fill a DataTable with schema and data DataTable dt = new DataTable( ); SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString); da.FillSchema(dt, SchemaType.Source); // Configure the primary key column DepartmentID dt.Columns["DepartmentID"].AutoIncrementSeed = -1; dt.Columns["DepartmentID"].AutoIncrementStep ...