Recipe 4.5 Adding Parent/Child Rows with Auto-Incrementing Keys

Problem

You want to insert related parent/child rows 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 sample code contains two event handlers:

Form.Load

Sets up the example by creating a DataSet containing a parent and child table. Each table has an AutoIncrement column that is the primary key and has both a seed and step value of -1. A relation is created between the parent and child table. The default view of the parent table is bound to the data grid on the form.

Add Record Button.Click

Adds a new row to the parent table along with a random number of rows to the child table.

The C# code is shown in Example 4-8.

Example 4-8. File: AddParentChildAutoIncrementForm.cs

// Namespaces, variables, and constants 
using System;
using System.Data;
using System.Data.SqlClient;

// Table name constants
private const String PARENTTABLENAME     = "ParentTable";
private const String CHILDTABLENAME      = "ChildTable";

// Table column name constants for Parent table
private const String PARENTID_FIELD      = "ParentId";
private const String FIELD1_FIELD        = "Field1";
private const String FIELD2_FIELD        = "Field2";

// Table column parameter name constants for Parent table
private const String CHILDID_FIELD       = "ChildId";
private const String FIELD3_FIELD        = "Field3";
private const String FIELD4_FIELD        = "Field4";

private DataSet ds;

//  . . . 

private void AddParentChildAutoIncrementForm_Load(object sender,
    System.EventArgs e)
{
    DataColumnCollection cols;
    DataColumn col;

    // Build the parent table.
    DataTable parentTable = new DataTable(PARENTTABLENAME);
    cols = parentTable.Columns;
    col = cols.Add(PARENTID_FIELD, typeof(Int32));
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    cols.Add(FIELD1_FIELD, typeof(String)).MaxLength = 50;
    cols.Add(FIELD2_FIELD, typeof(String)).MaxLength = 50;

    // Build the child table.
    DataTable childTable = new DataTable(CHILDTABLENAME);
    cols = childTable.Columns;
    col = cols.Add(CHILDID_FIELD, typeof(Int32));
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    cols.Add(PARENTID_FIELD, typeof(Int32)).AllowDBNull = false;
    cols.Add(FIELD3_FIELD, typeof(String)).MaxLength = 50;
    cols.Add(FIELD4_FIELD, typeof(String)).MaxLength = 50;

    // Add the tables to the DataSet and create the relationship.
    ds = new DataSet( );
    ds.Tables.Add(parentTable);
    ds.Tables.Add(childTable);
    ds.Relations.Add(new DataRelation("Parent_Child_Relation",
        parentTable.Columns[PARENTID_FIELD],
        childTable.Columns[PARENTID_FIELD], true));            

    // Bind the parent table default view to the grid.
    dataGrid.DataSource = parentTable.DefaultView;
}

private void addRecordButton_Click(object sender, System.EventArgs e)
{
    // Add a record to the parent table.
    DataRow parentRow = ds.Tables[PARENTTABLENAME].NewRow( );
    parentRow[FIELD1_FIELD] = Guid.NewGuid().ToString( );
    parentRow[FIELD2_FIELD] = Guid.NewGuid().ToString( );
    ds.Tables[PARENTTABLENAME].Rows.Add(parentRow);

    // Add some child records for the parent.
    for(int i = 0; i <= new Random((int)DateTime.Now.Ticks).Next(5); i++)
    {
        ds.Tables[CHILDTABLENAME].Rows.Add(new object[]
            {null, (int)parentRow[PARENTID_FIELD],
            Guid.NewGuid().ToString(), Guid.NewGuid().ToString( )});
    }
}

Discussion

The example adds a single row to the parent table and a random number of rows—from one to five—to the child table. The auto-increment primary key of the parent table is used as the foreign key value when constructing the records added to the child table.

When a record is added to the parent table or the child table, a new primary key value is generated starting in the sequence -1, -2, -3, and so on, because of the auto-increment properties of the column. The AutoIncrementSeed and AutoIncrementStep properties are both set to -1 to ensure that when the DataSet is ultimately resolved to the data source, the values do not conflict with values within the data source (this assumes that the data source uses positive integers for the primary key values). For more information about avoiding conflicts when using AutoIncrement columns, see Recipe 4.1.