Adding Records with a GUID Primary Key

Problem

You want to add records to a DataTable that uses a GUID as its primary key and has related child records.

Solution

Use the DataTable.RowChanging event handler.

The sample code contains three event handlers:

Form.Load

Sets up the sample by creating a DataSet containing two tables, a parent and child, both having a GUID primary key column with the DefaultValue set to a new GUID. A relation based on the parent table GUID and a foreign key GUID in the child table is added to the DataSet . The default view of the parent table is bound to the data grid on the form. Event handlers are added for the RowChanging event in both the parent and child DataTable objects.

Parent DataTable.RowChanging

Sets the default value of the primary key column in the parent table to a new GUID value when a new row has been added to the parent table.

Child DataTable.RowChanging

Sets the default value of the primary key column in the child table to a new GUID value when a new row has been added to the child table.

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

Example 4-9. File: AddGuidPKRecordForm.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 AddGuidPKRecordForm_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(Guid));
 col.DefaultValue = Guid.NewGuid( );
 parentTable.PrimaryKey = new DataColumn[] {col};
 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(Guid));
 col.DefaultValue = Guid.NewGuid( );
 childTable.PrimaryKey = new DataColumn[] {col};
 cols.Add(PARENTID_FIELD, typeof(Guid)).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;

 // Event handlers to generate new GUIDs for primary keys
 parentTable.RowChanging +=
 new DataRowChangeEventHandler(parentTable_RowChanging);
 childTable.RowChanging +=
 new DataRowChangeEventHandler(childTable_RowChanging);
}

private void parentTable_RowChanging(object sender,
 DataRowChangeEventArgs e)
{
 if(e.Action == DataRowAction.Add)
 ds.Tables[PARENTTABLENAME].Columns[
 PARENTID_FIELD].DefaultValue = Guid.NewGuid( );
}

private void childTable_RowChanging(object sender,
 DataRowChangeEventArgs e)
{
 if(e.Action == DataRowAction.Add)
 ds.Tables[CHILDTABLENAME].Columns[
 CHILDID_FIELD].DefaultValue = Guid.NewGuid( );
}

Discussion

A Globally Unique Identifier (GUID) is a 128-bit integer that is statistically unique; you can use it wherever a unique identifier is needed. The System.Guid type is a .NET structure that contains members to facilitate working with GUIDs.

The RowChanging event of the DataTable is raised when a DataRow is changing. The action that occurred on the row can be determined by the Action property of the DataRowChangingEventArgs argument of the event handler. The Action property is set to one of the DataRowAction values detailed in Table 4-2.

Table 4-2. DataRowAction enumeration

Value

Description

Add

The row has been added to the table.

Change

The row has been changed.

Commit

The changes made to the row have been committed.

Delete

The row has been deleted from the table.

Nothing

The row has not been changed.

Rollback

The changes made to the row have been rolled back.

The DefaultValue of the GUID primary key column in both parent and child tables is set to a new GUID using the NewGuid( ) method of the Guid structure when the tables are defined. This causes a new GUID value to be assigned to the primary key when the first row is added. Because the DefaultValue for the column is calculated once when the property is set rather than as each new row is added, it must be changed after each row is added to the table so that each row has a different GUID primary key value. This is done by handling the RowChanging event for each table. When a row has been added, that is, the Action property of the DataRowChangingEventArgs argument is Add , the DefaultValue for the primary key column is set to a new GUID.

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

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net