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 table 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 the 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 relation.

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

Parent-Child Relation

In 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));

            //Initalize the privary 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 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 propery. 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 a foreign relationship 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));



           //Initalize the privary 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 table
           UniqueConstraint custUnique = new UniqueConstraint(new DataColumn[] { ClassTable.Columns["ID"], StudentTable.Columns["Name"] });
           dataSet.Tables["Class"].Constraints.Add(custUnique);

 

 Summery

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