ADO.NET 3.5 Cookbook, 2nd Edition

2.8. Creating an Autoincrementing Primary Key

Problem

You need to create a primary key on a single column in a table that increments its value automatically for each new row.

Solution

Create a primary key and set the AutoIncrement, AutoIncrementSeed, and AutoIncrementStep properties of the column.

The solution creates a table with two columns. The first column is made of the primary key that increments automatically as rows are added, starting with 100 and incrementing by 10 for each new record. Records are added to the table and the contents of the table are output to the console.

The C# code in Program.cs in the project CreateAutoIncrementColumn is shown in Example 2-8.

Example 2-8. File: Program.cs for CreateAutoIncrementColumn solution

using System;
using System.Data;

namespace CreateAutoIncrementColumn
{
    class Program
    {
        static void Main(string[] args)
        {
            // Create a table and add two columns
            DataTable dt = new DataTable();
            DataColumn pkCol = dt.Columns.Add("Id", typeof(int)); dt.Columns.Add("Field1", typeof(string)).MaxLength = 50; // Make the Id column the primary key dt.PrimaryKey = new DataColumn[] {dt.Columns["Id"]}; // Make the primary key autoincrementing starting at 100 // and incrementing by 10 with each new record added pkCol.AutoIncrement = true; pkCol.AutoIncrementSeed = 100; pkCol.AutoIncrementStep = 10; // Add five rows to the table for (int i = 1; i <= 5; i++) dt.Rows.Add(new object[] {null, "Value " + i }); // Output the table rows to the console foreach (DataRow row ...