ADO.NET 3.5 Cookbook, 2nd Edition

5.8. Adding Parent/Child Rows with Autoincrementing Keys

Problem

You want to insert related parent/child records into a DataSet where the primary key of the parent table is an automatically incrementing value.

Solution

Use the DataColumn object's AutoIncrementSeed and AutoIncrementStep properties.

The solution uses two tables, ParentAutoincrementKey and ChildAutoincrementKey, related on the ParentId column in each table. The tables are shown in Figures 5-13 and 5-14. The primary key in each table is an identity column with a seed of 1 and an increment of 1.

Figure 5-13. Schema for table ParentAutoincrementKey

Figure 5-14. Schema for table ChildAutoincrementKey

The T-SQL DDL code to create the parent and child tables together with the relationship between them is shown in Example 5-14.

Example 5-14. Create tables ParentAutoincrementKey and ChildAutoincrementKey

USE AdoDotNet35Cookbook GO CREATE TABLE ParentAutoincrementKey( ParentId int IDENTITY(1,1) NOT NULL PRIMARY KEY, a nvarchar(50) NOT NULL, b nvarchar(50) NOT NULL ) GO CREATE TABLE ChildAutoincrementKey( ChildId int IDENTITY(1,1) NOT NULL PRIMARY KEY, ParentId int NOT NULL, c nvarchar(50) NOT NULL, d nvarchar(50) NOT NULL ) GO ALTER TABLE ChildAutoincrementKey WITH CHECK ADD CONSTRAINT FK_ChildAutoincrementKey_ParentAutoincrementKey FOREIGN ...