Modeling Relational Data with the DataSet

Modeling Relational Data with the `DataSet`

You now understand enough about the DataSet to populate it with a DataTable , access the data, and enforce some forms of constraints on the data. In this section we build on that knowledge and learn to perform common relational database operations with data inside a populated DataSet .

Deriving `DataColumn` Values with Expressions and Computed Fields

The value for a DataColumn can be computed based on the value of another DataColumn in the same DataRow . To do this, use the DataColumn.Expression property to describe the computed value for the DataColumn . The Expression property is a string value that describes the computation that derives the value for the DataColumn .

The syntax for the expression is rich and supports a wide variety of arithmetic and string operators. Table 6.1 shows all of the expression operators that are supported by the .NET Compact Framework.

Table 6.1. Framework-Supported Expression Operators

OPERATOR	FUNCTION
`Sum`	Computes the sum of the arguments
`Avg`	Computes the average of the arguments
`Min`	Selects the minimum of the arguments
`Max`	Selects the maximum of the arguments
`+` , `-` , `*` , `/`	Addition, subtraction, multiplication, division
`%`	Modulus (remainder)
`+`	String concatenation operator

Creating Expressions by Example

The easiest way to understand how to create an Expression is to look at an example. For our first example, consider a DataTable called l_newTable that has three columns named FirstName , LastName , and FullName . The goal is to create an Expression that sets the FullName column to be the string concatenation of the FirstName and LastName columns. The code to do that appears below:

 C# l_newTable.Columns["FullName"].Expression = "FirstName + ' ' + LastName"; VB l_newTable.Columns("FullName").Expression = "FirstName + ' ' + LastName"

For our second example, consider a DataTable named l_newTable . We want the TotalPrice column to be the value of the MSRP column minus the value of the Discount column:

 C# l_newTable.Columns["TotalPrice"].Expression = "MSRP - Discount"; VB l_newTable.Columns("TotalPrice").Expression = "MSRP - Discount"

Finally, imagine that l_newTable is a DataTable with four numeric columns: FinalGrade , Exam1 , Exam2 , and Exam3 . We want to set the value for FinalGrade to the average of Exam1 , Exam2 , and Exam3 , as follows :

 C# l_newTable.Columns["FinalGrade"].Expression = "Avg(Exam1, Exam2, Exam3)"; VB l_newTable.Columns("FinalGrade").Expression = "Avg(Exam1, Exam2, Exam3)"

Expressing Parent-Child Relationships in a `DataSet`

The essential ingredients of a relational database are tables with rows and the ability to create a parent-child relationship, or a relation, between two tables. A relation between two tables is made by linking the two tables by one or more data columns called the primary key . In the parent table, the primary key is unique to all of the rows in the table. The rows in the child table have a column called the foreign key , which does not have to be unique in the child table. It points back to the corresponding row in the parent table.

For example, consider Table 6.2, a parent table that describes the main contacts for a physician 's office.

Table 6.2. `MainContactTable`

COLUMN NAME	DATA TYPE
`CustID`	Integer, PRIMARY KEY
`FirstName`	String
`LastName`	String

A child table that holds cholesterol readings might look like Table 6.3.

Table 6.3. `CholesterolTable`

COLUMN NAME	DATA TYPE
`CustID`	Integer, FOREIGN KEY
`Reading1`	Decimal
`Reading2`	Decimal
`Reading3`	Decimal
`Average`	Decimal

In the CholesterolTable , the CustID references a specific, unique entry in the MainContactTable . Tables 6.4 and 6.5 show what these parent-child tables might look like when populated.

Table 6.4. `MainContactTable`

`CustID`	`FirstName`	`LastName`
001	George	Washington
002	Ben	Franklin
003	Alexander	Hamilton

Table 6.5. `CholesterolTable`

`CustID`	`Reading1`	`Reading2`	`Reading3`	`Average`
001	87	78	66	77.0
001	99	54	89	80.667
002	90	88	55	77.667

In this parent-child table example, there were two entries in the CholesterolTable for George Washington and one entry for Ben Franklin. What would happen if the entry for George Washington were deleted from the MainContactTable ? The system should delete all of the corresponding entries in the CholesterolTable , or the database would be in an invalid state.

Good database systems understand the notion of a relation between two tables and can delete child rows automatically if the parent row is deleted. Alternately, they should at least report an error if the user does something that puts the data into an invalid state, such as deleting George Washington's record from MainContactTable without also removing his entries from the CholesterolTable .

The .NET Compact Framework offers two classes that can do this bookkeeping automatically: DataRelation and ForeignKeyConstraint .

Creating a `DataRelation` to Express Parent-Child Relationships

When you set up a DataRelation between two tables, you specify which DataColumn serves as the primary key and which serves as the foreign key. After the DataRelation has been created, it will ensure that the DataSet 's relational data, as described by the DataRelation , remains valid. For example, if you delete the first row from the MainContactTable , the DataRelation automatically deletes all of the child rows in the CholesterolTable .

To set up a DataRelation between two tables in a DataSet , first create an instance of the DataRelation using its constructor to pass in the DataColumn s that comprise the primary and foreign keys. The DataRelation constructor overloads that are available on the .NET Compact Framework are as follows:

DataRelation(String relName, DataColumn parent, DataColumn child) creates a named DataRelation between the parent and child DataColumn s.
DataRelation(String relName, DataColumn[] parent, DataColumn[] child) creates a named DataRelation between two tables by using multiple columns per table for the relation.
DataRelation(String relName, DataColumn parent, DataColumn child, bool createConstraints) creates a named DataRelation between the parent and child DataColumn s, with the option to create associated constraints to enforce the relation.
DataRelation(string relName, DataColumn[] parent, DataColumn[] child, bool createConstraints) creates a named DataRelation between two tables by using multiple columns per table for the relation, with the explicit option to create associated constraints to enforce the relation.
DataRelation(string relName, string parentTableName, string childTableName, string[] parentColNames, string[] childColNames, bool isNested) is the constructor used by the Smart Device Extensions environment.

Then simply add the DataRelation to the DataSet.Relations collection.

Writing Code to Create a `DataRelation`

This sample code is from an updated PhoneBook example application that demonstrates the use of the Expression and DataRelation classes. The code creates a new DataRelation that binds the ContactID column from the PhoneContactsMainTable and the Cholesterol table inside the DataSet . The order in which the columns are passed to the constructor dictates which is the parent table and which is the child table. It is important to be careful about the ordering to avoid strange behavior and exceptions from occurring in applications.

 C# DataRelation l_newRelation = new DataRelation(         "MainContactToCholesterolRelation",         l_DataSet.Tables["PhoneContactsMainTable"].Columns["ContactID"],         l_DataSet.Tables["Cholesterol"].Columns["ContactID"]); l_DataSet.Relations.Add(l_newRelation); VB Dim l_newRelation As DataRelation l_newRelation = New         DataRelation("MainContactToCholesterolRelation",         l_DataSet.Tables("PhoneContactsMainTable"). Columns("ContactID"),         l_DataSet.Tables("Cholesterol").Columns("ContactID")) l_DataSet.Relations.Add(l_newRelation)

Enhancing the PhoneBook Application with `DataRelations` and `Expressions`

The full sample application that demonstrates using a DataRelation and an Expression is located in the folder SampleApplications\Chapter6\PhoneBook_Relations_Expressions_CSharp and PhoneBook_Relations_Expressions_VB . This sample application sets up two tables in the DataSet named PhoneContactsMainTable and Cholesterol . A DataRelation links the tables, with PhoneContactsMainTable as the parent and Cholesterol as the child. The tables are populated such that the entry for George Washington in PhoneContactsMainTable has a child row in the Cholesterol table.

The Cholesterol table has a DataColumn named AverageReading , which is computed by averaging the values of the three other columns. This demonstrates how to use a DataExpression to create a computed column.

When the button labeled "Delete row, trigger DataRelation" is clicked, the application deletes the row for George Washington from the PhoneContactMainTable . Because there is a child entry for this row in the Cholesterol table, the DataRelation forces the child rows also to be deleted. On the other hand, pressing the button labeled "Delete row ”don't trigger" deletes the row for Alexander Hamilton from the PhoneContactMainTable . This row has no children, so deleting it does not require deleting any child rows.

Enforcing Foreign Key Relationships with the `ForeignKeyConstraint`

The ForeignKeyConstraint is very much like the DataRelation , but it provides extra flexibility. As with a UniqueConstraint , the ForeignKeyConstraint is added to a DataTable.Constraints collection. Specifically, the ForeignKeyConstraint gets added to the Constraints collection of the child table.

When a row with children is deleted from a parent table, the ForeignKeyConstraint can cause the following behaviors:

It can cause all child rows to be deleted. This behavior is thus identical to using a DataRelation .
It can set the child column values, that is, the foreign keys, to NULL . Thus, they no longer point to a parent row that does not exist.
It can set the child column value to a default value. This is useful because it makes it easy to see all of the "orphaned" child rows by pointing to a default "orphan parent" in the parent table.
It can throw an exception.

To set up a ForeignKeyConstraint , first create a new ForeignKeyConstraint through one of the constructors available on the .NET Compact Framework. The available constructors on the .NET Compact Framework are as follows:

ForeignKeyConstraint(DataColumn parentCol, DataColumn childCol) creates a ForeignKeyConstraint between parent and child DataColumn s.
ForeignKeyConstraint(String name, DataColumn parentCol, DataColumn ChildCol) creates a ForeignKeyConstraint between a parent and child, but the constraint gets a name.
ForeignKeyConstraint(DataColumn[] parentCols, DataColumn[] childCols) creates a ForeignKeyConstraint between two tables by using multiple DataColumn s for the constraint.
ForeignKeyConstraint(String name, DataColumn[] parentCols, DataColumn[] childCols) creates a ForeignKeyConstraint between two tables by using multiple DataColumn s for the constraints, but every constraint gets a name.
ForeignKeyConstraint(string cName, string pName, string[] pColNames, string[] cColNames, AcceptRejectRule arRule, Rule dRule, Rule uRule) is used internally by the Smart Device Extensions environment.

Next set the ForeignKeyConstraint 's DeleteRule , UpdateRule , and AcceptRejectRule . The DeleteRule controls what happens when a parent row is deleted. The UpdateRule controls what happens when a parent row is modified. The AcceptRejectRule controls what happens when a parent row is modified and DataSet.AcceptChanges() is called. The UpdateRule and DeleteRule are Rule types, while the AcceptRejectRule is a AcceptRejectRule type.

For example, consider a ForeignKeyConstraint that is used to express a parent-child relationship between two tables. Imagine that a row from the parent table is deleted. In this case the value for the Delete rule is examined to determine what happens to the child tables:

Rule.Cascade The delete is cascaded, so the child rows are also deleted.

Rule.SetDefault The child rows' values are set to a default value.

Rule.SetNull The child rows' values are set to DBNull .

Rule.None An exception is thrown.

The AcceptRejectRule is examined only when DataSet.AcceptChanges is called. The AcceptRejectRule type has two values: Cascade and None . If the AcceptRejectRule is set to the Cascade value, then the DataSet attempts to cascade changes made in a parent row to its children when DataSet.AcceptChanges is called.

The ForeignKeyConstraint is now set up. To use it, add it to the ForeignKeyConstraint to the Constraints collection of the child table.

Creating a `ForeignKeyConstraint` with Sample Code

The following sample code creates a ForeignKeyConstraint that cascades when a parent row is deleted. This means that when a parent row is deleted, the children rows are also deleted. The code is taken from the modified PhoneBook sample application using ForeignKeyConstraints .

 C# ForeignKeyConstraint l_ForeignKC = new ForeignKeyConstraint("MainToCholesterolFKConstraint",         l_DataSet.Tables["PhoneContactsMainTable"].Columns         ["ContactID"], l_DataSet.Tables["BloodPressure"].         Columns["ContactID"]); l_ForeignKC.DeleteRule = Rule.Cascade; l_ForeignKC.UpdateRule = Rule.Cascade; l_ForeignKC.AcceptRejectRule = AcceptRejectRule.Cascade; l_DataSet.Tables["BloodPressure"].Constraints.Add(l_ForeignKC); l_DataSet.EnforceConstraints = true; VB Dim l_ForeignKC As ForeignKeyConstraint l_ForeignKC = New ForeignKeyConstraint         ("MainToCholesterolFKConstraint",         l_DataSet.Tables("PhoneContactsMainTable").Columns          ("ContactID"), l_DataSet.Tables("BloodPressure").         Columns("ContactID")) l_ForeignKC.DeleteRule = Rule.Cascade l_ForeignKC.UpdateRule = Rule.Cascade l_ForeignKC.AcceptRejectRule = AcceptRejectRule.Cascade l_DataSet.Tables("BloodPressure").Constraints.Add(l_ForeignKC) l_DataSet.EnforceConstraints = True

Enhancing the PhoneBook Application with a `ForeignKeyConstraint`

The full sample application that demonstrates ForeignKeyConstraints is located in the folders SampleApplications\Chapter6\PhoneBook_ForeignKeyConstraint_CSharp and PhoneBook_ForeignKeyConstraint_VB . This sample application sets up two tables in the DataSet , named PhoneContactsMainTable and BloodPressure . A ForeignKeyContraint links the tables, with PhoneContactsMainTable as the parent and BloodPressure as the child. The tables are populated such that the entry for George Washington in PhoneContactsMainTable has a child row in the BloodPressureTable .

The BloodPressure table has a DataColumn named AverageReading , which is computed by averaging the values of the three other columns. This demonstrates how to use a DataExpression to create a computed column.

When the button labeled Trigger ForeignKeyConstraint is clicked, the application deletes the row for George Washington from the PhoneContactMainTable . Because there is a child entry for this row in the BloodPressure table, the ForeignKeyContraint triggers, throwing a ConstraintException .

On the other hand, pressing the button labeled "Delete row ”no trigger" deletes the row for Alexander Hamilton from the PhoneContactMainTable . This row has no children, so deleting it does not put the database into an invalid state. Thus, no exception is thrown.