Establishing Relation and Constraints in a DataTable Using C#

Introduction

 

This article explains how to create a relationship built between two DataTables and the constraints in ADO.Net using C#. In ADO.NET, you can navigate through multiple tables to validate and summarize the data using the DataRelation object. Using the primary key and foreign key constraints that use a DataRelation object, you can create a relationship among multiple tables.

 

There are two kinds of constraints in ADO.NET: the ForeignKeyConstraint and the UniqueConstraint. By default, both constraints are created automatically when you create a relationship among two or more tables by adding a DataRelation to the DataSet. However, you can disable this behavior by specifying createConstraints = false when creating the relationship.

 

A DataSet.Relations property gets the collection of relations that link tables and allow navigation from parent tables to child tables.

 

Parent-Child Relation

 

The following code snippet shows how to add two tables, Class and Student, in a single dataset.

DataSet dataSet = new DataSet();

//Add Class Table in the dataset
DataTable ClassTable = dataSet.Tables.Add("Class");
ClassTable.Columns.Add("ID", typeof(int));
ClassTable.Columns.Add("Name", typeof(string));

//Add Student Table in the dataset
DataTable StudentTable = dataSet.Tables.Add("Class");
StudentTable.Columns.Add("ClassID", typeof(int));
StudentTable.Columns.Add("ID", typeof(int));
StudentTable.Columns.Add("Name", typeof(string));

//Initialize the primary key constraint
ClassTable.PrimaryKey = new DataColumn[] { ClassTable.Columns["ID"] };
dataSet.Relations.Add("Class_Student", StudentTable.Columns["ClassID"], ClassTable.Columns["ID"]);

In the preceding code, the Class_Student is the name of the relation that will establish the relationship between the two tables in the dataset. The relation is created between the two tables, Class and Student on a common field that is ClassID. In addition, by using the add() method, the table's relation information is added to the dataset.

 

Foreign Key Relation

 

You also can create navigation among related rows in the various tables using the DataRelation property. The DataRelation object is used to retrieve data from a parent of a child table in a DataSet.

 

Consider the following code snippet that defines constraints and relationships:

DataColumn dcClassID, dcStudentID;
//gET COLUMNS AND CREATE THE CONSTRAINT
dcClassID = dataSet.Tables["Class"].Columns["ID"];
dcStudentID = dataSet.Tables["Student"].Columns["ID"];

ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint("ClassFK", dcClassID, dcStudentID);
//Setting Rule of constraint
foreignKeyConstraint.DeleteRule = Rule.SetNull;
foreignKeyConstraint.UpdateRule = Rule.Cascade;

The preceding code creates foreign relations between the class table and the student table named ClassFK.

 

Unique Constraint

 

The following example creates a UniqueConstraint for two columns of a DataTable.

UniqueConstraint custUnique = new UniqueConstraint(new DataColumn[] { ClassTable.Columns["ID"], StudentTable.Columns["Name"] });
dataSet.Tables["Class"].Constraints.Add(custUnique);

The code above creates a Unique constraint and passes it to the constraints collection of the table. You can also create a unique constraint for a column by setting the Unique property of the column to true. Alternatively, setting the Unique property of a single column to false removes any unique constraints that may exist.

 

Full Code

DataSet dataSet = new DataSet();
//Add Class Table in the dataset
DataTable ClassTable = dataSet.Tables.Add("Class");
ClassTable.Columns.Add("ID", typeof(int));
ClassTable.Columns.Add("Name", typeof(string));

//Add Student Table in the dataset
DataTable StudentTable = dataSet.Tables.Add("Student");
StudentTable.Columns.Add("ClassID", typeof(int));
StudentTable.Columns.Add("ID", typeof(int));
StudentTable.Columns.Add("Name", typeof(string));

//Initialize the primary key constraint
ClassTable.PrimaryKey = new DataColumn[] { ClassTable.Columns["ID"] };
dataSet.Relations.Add("Class_Student", StudentTable.Columns["ClassID"], ClassTable.Columns["ID"]);

DataColumn dcClassID, dcStudentID;
//gET COLUMNS AND CREATE THE CONSTRAINT
dcClassID = dataSet.Tables["Class"].Columns["ID"];
dcStudentID = dataSet.Tables["Student"].Columns["ID"];

ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint("ClassFK", dcClassID, dcStudentID);
//Setting Rule of constraint
foreignKeyConstraint.DeleteRule = Rule.SetNull;
foreignKeyConstraint.UpdateRule = Rule.Cascade;

//Adding constraint to a table
UniqueConstraint custUnique = new UniqueConstraint(new DataColumn[] { ClassTable.Columns["ID"], StudentTable.Columns["Name"] });
dataSet.Tables["Class"].Constraints.Add(custUnique);

Summary

 

From the preceding illustration, we are able to understand how constraints and relations are created between two Data Tables in ADO.Net using C#.