Understanding Constraints

The DataSet allows you to specify special rules that the data held inside the DataSet.Tables collection must follow. The Constraint base class specifies the rules that the data within a DataTable must follow in order to maintain database integrity. Two descendents from the Constraint class represent specific restrictions that the data must follow. The UniqueConstraint specifies that a particular value for a DataColumn must be unique in the table.

The ForeignKeyConstraint is used to enforce specifc behavior when altering or deleting the primary key column for a table. Because the ForeignKeyConstraint is intended for use in modeling parent-child relationships across tables, it is discussed in more detail in the "Enforcing Foreign Key Relationships with the ForeignKeyConstraint" section later in this chapter.

Adding Constraints to a DataSet

Each DataTable held in the DataSet.Tables collection holds a ConstraintCollection in the Constraints property. For example, to access the ConstraintCollection in the first table of a DataSet , use

 C# m_phonebookDS.Tables[0].Constraints VB m_phonebookDS.Tables(0).Constraints 

The steps involved in creating and initializing constraints differ according to what kind of constraint you are trying to create. Once the constraint is created and initialized , you must perform these steps to make it active:

1. Add the constraint to the Constraints collection of the appropriate table(s).

2. Set the DataSet.EnforceConstraints flag to true to turn on enforcement. When you set the flag back to true, each constraint in each DataTable.Constraints collection is checked, and an exception is thrown if a check fails.

Adding a UniqueConstraint

To add a UniqueConstraint to a DataSet , follow these steps:

1. Create a UniqueConstraint by using one of the four meaningful constructors available on the .NET Compact Framework. Each constructor is now discussed in detail:

   UniqueConstraint(String name , DataColumn col) Creates a UniqueConstraint with specified name that enforces uniqueness on a single DataColumn .

   UniqueConstraint(DataColumn col) Creates a UniqueConstraint that enforces uniqueness on a single DataColumn .

   UniqueConstraint(String name, DataColumn[] cols) Creates a UniqueConstraint that enforces uniqueness for multiple columns in a row. The columns are specified by passing them as an array.

   UniqueConstraint(DataColumn[] cols) Same as above except the UniqueConstraint is nameless.

   UniqueConstraint(String name, string[] colNames, bool isPrimaryKey) This fifth public constructor is useful only to the Smart Device Extensions environment.

2. Add the UniqueConstraint to the Constraints collection of the desired DataTable .

3. Set DataSet.EnforceConstraints to true to turn on enforcement.

BE CAREFUL WITH DataSet.EnforceConstraints

When DataSet .EnforceConstraints is true, you may see an exception at any time that you insert, delete, or update data in the DataSet . Whether an exception is thrown depends on the specifics of the constraints that are in the DataSet . If an exception is thrown, remember that you can revert the changes you made by catching the exception and calling DataSet.RejectChanges() . The contents of the DataSet roll back to their previous valid state.

TEMPORARILY DISABLING CONSTRAINT ENFORCEMENT

If you want to alter a DataSet significantly and you know that you will violate some constraints while you are making changes, you can temporarily set DataSet.EnforceConstraints to false and then set it back to true when you are ready. It is a good practice to turn off constraint checking while manipulating large DataSet s on the .NET Compact Framework, because you will avoid the performance penalty of checking constraints until the very end of the updates.

Working with a UniqueConstraint by Example

This code sample comes out of an updated PhoneBook sample application that is described in the section "Updating the PhoneBook Application with Constraints and Autoincremented Fields." This sample application is available in the folder SampleApplications\Chapter6\ PhoneBook_Constraint_AutoIncrement_CSharp and PhoneBook_Constraint_AutoIncrement_VB . It sets up a UniqueConstraint that prevents any two contacts from sharing the same phone number.

 C# // Add a UniqueConstraint to the phone number column // Note: Using indexing by the string "PhoneNumber" is slower UniqueConstraint l_UniqueConstraint = new UniqueConstraint(l_DataSet.Tables[0].         Columns["PhoneNumber"]); l_DataSet.Tables[0].Constraints.Add(l_UniqueConstraint); VB ' Add a UniqueConstraint to the phone number column Dim l_UniqueConstraint As UniqueConstraint l_UniqueConstraint = New UniqueConstraint(l_DataSet.Tables(0).         Columns("PhoneNumber")) l_DataSet.Tables(0).Constraints.Add(l_UniqueConstraint) 

Preventing NULL Values in a DataColumn

The DataColumn.AllowDBNull property is very useful for disallowing a DataColumn from having a DBNull value. If you create a new DataRow and don't assign a value to one of the columns, it receives the default value of DBNull .

The following code sample is taken out of the updated PhoneBook sample application. This sample disallows the Name field of the PhoneBook from being DBNull .

 C# l_newTable.Columns["Name"].AllowDBNull = false; VB l_newTable.Columns("Name").AllowDBNull = False 

If a DataColumn that has AllowDBNull false is set to DBNull , a System.Data.NoNullAllowed exception is thrown when the new row is added to a DataTable held inside the DataSet . For example, the following code attempts to add a row where the Name column was never set, and so executing this code would result in a System.Data.NoNullAllowed exception:

 C# DataRow l_newRow = m_phonebookDS.Tables[0].NewRow(); l_newRow[0] = "Violator" l_newRow[1] = "5555587"; // This is going to throw an exception because the "Name" // DataColumn was never set, so it is DBNull, and that is // not allowed for the DataColumn m_phonebookDS.Tables[0].Rows.Add(l_newRow);] VB l_newRow = m_phonebookDS.Tables(0).NewRow() l_newRow(0) = "Violator" l_newRow(1) = "5551212" ' This is going to throw an exception because the "Name" ' DataColumn was never set, so it is DBNull, and that is not ' allowed for the DataColumn m_phonebookDS.Tables(0).Rows.Add(l_newRow)