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.
  1. DataSet dataSet = new DataSet();  
  2.   
  3. //Add Class Table in the dataset    
  4. DataTable ClassTable = dataSet.Tables.Add("Class");  
  5. ClassTable.Columns.Add("ID"typeof(int));  
  6. ClassTable.Columns.Add("Name"typeof(string));  
  7.   
  8. //Add Student Table in the dataset    
  9. DataTable StudentTable = dataSet.Tables.Add("Class");  
  10. StudentTable.Columns.Add("ClassID"typeof(int));  
  11. StudentTable.Columns.Add("ID"typeof(int));  
  12. StudentTable.Columns.Add("Name"typeof(string));  
  13.   
  14. //Initialize the primary key constraint    
  15. ClassTable.PrimaryKey = new DataColumn[] { ClassTable.Columns["ID"] };  
  16. 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:
  1. DataColumn dcClassID, dcStudentID;  
  2. //gET COLUMNS AND CREATE THE CONSTRAINT    
  3. dcClassID = dataSet.Tables["Class"].Columns["ID"];  
  4. dcStudentID = dataSet.Tables["Student"].Columns["ID"];  
  5.   
  6. ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint("ClassFK", dcClassID, dcStudentID);  
  7. //Setting Rule of constraint    
  8. foreignKeyConstraint.DeleteRule = Rule.SetNull;  
  9. 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.
  1. UniqueConstraint custUnique = new UniqueConstraint(new DataColumn[] { ClassTable.Columns["ID"], StudentTable.Columns["Name"] });  
  2. 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
  1. DataSet dataSet = new DataSet();  
  2. //Add Class Table in the dataset    
  3. DataTable ClassTable = dataSet.Tables.Add("Class");  
  4. ClassTable.Columns.Add("ID"typeof(int));  
  5. ClassTable.Columns.Add("Name"typeof(string));  
  6.   
  7. //Add Student Table in the dataset    
  8. DataTable StudentTable = dataSet.Tables.Add("Student");  
  9. StudentTable.Columns.Add("ClassID"typeof(int));  
  10. StudentTable.Columns.Add("ID"typeof(int));  
  11. StudentTable.Columns.Add("Name"typeof(string));  
  12.   
  13. //Initialize the primary key constraint    
  14. ClassTable.PrimaryKey = new DataColumn[] { ClassTable.Columns["ID"] };  
  15. dataSet.Relations.Add("Class_Student", StudentTable.Columns["ClassID"], ClassTable.Columns["ID"]);  
  16.   
  17. DataColumn dcClassID, dcStudentID;  
  18. //gET COLUMNS AND CREATE THE CONSTRAINT    
  19. dcClassID = dataSet.Tables["Class"].Columns["ID"];  
  20. dcStudentID = dataSet.Tables["Student"].Columns["ID"];  
  21.   
  22. ForeignKeyConstraint foreignKeyConstraint = new ForeignKeyConstraint("ClassFK", dcClassID, dcStudentID);  
  23. //Setting Rule of constraint    
  24. foreignKeyConstraint.DeleteRule = Rule.SetNull;  
  25. foreignKeyConstraint.UpdateRule = Rule.Cascade;  
  26.   
  27. //Adding constraint to a table    
  28. UniqueConstraint custUnique = new UniqueConstraint(new DataColumn[] { ClassTable.Columns["ID"], StudentTable.Columns["Name"] });  
  29. 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#.


Similar Articles