Introduction to DataRelation Objects
There are many times when your application needs to work with related tables. Although a dataset contains tables and columns as in a database, it does not inherently include a database's ability to relate tables. However, you can create DataRelation objects that establish a relationship between a parent (master) and a child (detail) table based on a common key.
For example, a dataset containing sales data might have a Customers table and an Orders table. Even if the tables contain a key in common (in this example, perhaps CustomerID), the dataset itself does not keep track of the records in one table that relate to those in another. But you can create a DataRelation object that references the parent and child tables (and their keys), and then use the object to work with the related tables.
The DataRelation object can perform two functions:
- It can make available the records related to a record you are working with. It provides child records if you are in a parent record, and a parent record if you are working with a child record.
- It can enforce constraints for referential integrity, such as deleting related child records when you delete a parent record.
It is important to understand the difference between a true join and the function of a DataRelation object. In a true join, records are taken from parent and child tables and put into a single, flat recordset. When you use a DataRelation object, no new recordset is created. Instead, the relation tracks the relationship between tables and keeps parent and child records in synch.
Accessing Related Records
If tables in a dataset have a logical relationship, a DataRelation object can make available to you the related records in another table. An example might be a dataset containing a customer database with the tables Customers (with the primary key CustomerID) and Orders (with a foreign key CustomerID). You can create a DataRelation object whose properties are set to reflect these keys.
You can then use the DataRelation object to get related records. The process is indirect, in that you do not join tables. Instead, you call the GetChildRows method of a data row in the parent table, passing to it the DataRelation object that defines the parent/child relationship. The method returns an array of related child records.
The following code illustrates a simple example of getting related records. In this example, the array
drarray is set to the child records of the first row in the Customers table.
' Visual Basic Dim RowCtr As Integer ' Dim an array of datarows to hold the child records. Dim drarray() As DataRow ' GetChildRows gets related rows. It is a method on the datatable, and ' takes a DataRelation name as a string. RowCtr = 0 drarray = dsCustomersOrders1.Customers(RowCtr).GetChildRows("CustomersOrders") // C# int rowCtr; // Declare an array of datarows to hold the child records. DataRow drarray; // GetChildRows gets related rows. It is a method on the datatable, and // takes either DataRelation object name as a string. rowCtr = 0; drarray = dsCustomersOrders1.Customers[rowCtr].GetChildRows("CustomersOrders");
Similarly, you can get the parent row of a given child record by calling the GetParentRow method of a data row in the child table. In that case, the method does not return an array, but a single data row. For more information, see Navigating a Relationship between Tables.
DataRelation Objects and Constraints
A DataRelation object is also used to create and enforce the following constraints:
- A unique constraint, which guarantees that a column in the table contains no duplicates.
- A foreign-key constraint, which can be used to maintain referential integrity between a parent and child table in a dataset.
Constraints that you specify in a DataRelation object are implemented by automatically creating appropriate objects or setting properties. If you create foreign-key constraint using the DataRelation object, instances of the ForeignKeyConstraint class are added to the data relation's ChildKeyConstraint property.
A unique constraint is implemented either by simply setting the Unique property of a data column to true or by adding an instance of the UniqueConstraint class to the DataRelation object's ParentKeyConstraint.
As part of the foreign-key constraint, you can specify referential integrity rules that are applied at three points:
- when a parent record is updated
- when a parent record is deleted
- when a change is accepted or rejected
The rules you can make are specified in the Rule enumeration and are listed in the following table.
|Foreign-key constraint rule||Action|
|Cascade||The change (update or delete) made to the parent record is made in related records in the child table as well.|
|SetNull||Child records are not deleted, but the foreign key in the child records is set to DBNull. With this setting, child records can be left as "orphans" — that is, they have no relationship to parent records.
Note Using this rule can result in invalid data in the child table.
|SetDefault||The foreign key in the related child records is set to its default value (as established by the column's DefaultValue property).|
|None||No change is made to related child records. With this setting, child records can end up containing references to invalid parent records.|
|Default||The default value is Cascade.|
For more information about updates in dataset tables, see Dataset Updates in Visual Studio .NET.
When creating a DataRelation object, you have the option of specifying that the relation will be used only to enforce constraints — that is, it will not also be used to access related records. This option allows you to generate a dataset that is slightly more efficient and that contains fewer methods than one with the related-records capability. However, you will not be able to access related records.