Microsoft.NET

……………………………………………….Expertise in .NET Technologies

ADO.NET Core Classes:Constraints – Part 9

Posted by Ravi Varma Thumati on November 19, 2009

One key to successfully using disconnected data is minimizing the possibility for invalid information. If invalid values are entered in the DataSet, the error won’t be caught until you commit changes to the data source. At this point, the problem can be much more difficult to track down and resolve.

In order to reduce the possibility of invalid data, you can use properties of the DataColumn object, as explained in DataColumns. For example, if your DataSet is correctly configured with schema information, you’ll receive an error if you try to insert a null value into a non-null column, use an invalid data type, or exceed the column length restrictions. Another way to prevent data errors is by using Constraint objects.

Constraint objects are required to enforce specialized rules that involve comparing column values in more than one row. ADO.NET currently provides two types of constraints:

  • The UniqueConstraint, which represents a rule forbidding duplicate values in a column (or across multiple columns)
  • The ForeignKeyConstraint, which represents a relationship that links a column (or set of columns) in one table to a column (or set of columns) in another table and defines the type of action to take for updates or deletions

In this chapter, you’ll learn how to create these classes, enforce constraint checking, and enforce referential integrity in the DataSet.

Constraint Object Overview

The System.Data.Constraint type is an abstract class with two derived classes: UniqueConstraint and ForeignKeyConstraint. In order to use a constraint, you must create the appropriate class and add it to the DataTable.Constraints collection.

Constraints are enforced only if the EnforceConstraints property of the containing DataSet is true, which it is by default. If you attempt to modify, delete, or insert data in such a way that it violates an existing constraint, and EnforceConstraints is true, a ConstraintException is thrown, and the change is rejected. Similarly, if the DataTable already contains data, and you add a new Constraint, all the rows are checked to ensure they agree with the new rule. If any row violates the constraint, an InvalidConstraintException is thrown, and the Constraint object isn’t added.

Here are a few more rules of thumb that come into play when working with Constraint objects:

  • If you have set DataSet.EnforceConstraints to false, you will never encounter a ConstraintException or InvalidConstraintException. However, if you later change DataSet.EnforceConstraints to true, all the rows will be examined to ensure that they don’t violate the existing constraints. If a discrepancy is found, an InvalidConstraintException is thrown, and EnforceConstraints is set to false.
  • When merging a DataSet, constraints are applied and verified after the merge is complete.
  • There is no limit to how many Constraint objects a DataTable can contain; you are free to define as many unique rows and foreign key restrictions as you need.

The UniqueConstraint

The UniqueConstraint prevents duplication in a single column or the combined value of a group of columns. The UniqueConstraint object adds two properties: Columns, which defines one or more DataColumn objects that, when taken together, must be unique; and IsPrimaryKey, which indicates whether the UniqueConstraint is required to guarantee the integrity of the primary key for the table.

The UniqueConstraint provides several overloaded constructors. Here’s how you might create a UniqueConstraint that prevents duplicate CustomerID values:

// Create the UniqueConstraint object.

UniqueConstraint uc = new UniqueConstraint(“ID”, dt.Columns[“CustomerID”]);

// Add the UniqueConstraint to the table’s Constraints collection.

dt.Constraints.Add(uc);

You can also define a UniqueConstraint that encompasses several columns, such as this first and last name combination:

// Create an array with the two columns.

DataColumn[] cols = new DataColumn[] {dt.Columns[“LastName”],

dt.Columns[“FirstName”]};

// Create the UniqueConstraint object.

UniqueConstraint uc = new UniqueConstraint(“FullName”, cols);

// Add the UniqueConstraint to the table’s Constraints collection.

dt.Constraints.Add(uc);

To create a primary key UniqueConstraint, use an overloaded version of the constructor that accepts a Boolean parameter, and specify true. Once created, you can’t change the IsPrimaryKey property of a UniqueConstraint.

// Create a UniqueConstraint object that represents the primary key.

UniqueConstraint uc = new UniqueConstraint(“ID”, dt.Columns[“CustomerID”],

true);

// Add the UniqueConstraint to the table’s Constraints collection.

dt.Constraints.Add(uc);

You can also pass constructor arguments to several overloads of the Add( ) method of a ConstraintCollection to create a UniqueConstraint. In this case, the false parameter indicates that this UniqueConstraint should not be designated as the primary key for the table.

// Add a new UniqueConstraint to the table’s Constraints collection.

dt.Constraints.Add(“ID”, dt.Columns[“CustomerID”], false);

However, it’s quite possible that you won’t use any of these approaches to generate UniqueConstraint objects because ADO.NET provides an even easier approach through the DataColumn object. As soon as you set the DataColumn.Unique property to true, a new UniqueConstraint is generated and added to the collection. Similarly, setting the Unique property of a column to false removes the UniqueConstraint. This process is transparent to your application, unless you need to modify the IsPrimaryKey property or create a UniqueConstraint that acts on multiple columns.

Having a UniqueConstraint in place doesn’t mean you won’t receive an error when updating data back to the data source. Remember, the DataSet almost always contains a subset of the total information in the database. If a unique column value conflicts with another value in the data source (but not the DataSet), the problem won’t be detected until you attempt to commit changes.

Constraints and FillSchema( )

If you fill a DataSet without using the FillSchema( ) method, ADO.NET doesn’t create any Constraint objects automatically. However, you can still create and apply Constraint objects manually, as shown earlier.

If you use the FillSchema( ) to retrieve schema information, UniqueConstraint objects are created to match the restrictions you have defined in the data source. The FillSchema( ) method also attempts to designate a primary key. If a primary key column (or group of columns) is found in the result set, it’s used to create a UniqueConstraint with IsPrimaryKey set to true. Otherwise, FillSchema( ) uses any non-nullable unique column returned by the query.

Example 10-1. Add unique constraints to a data source

// FillWithConstraints.cs – Create UniqueConstraints defined in data source

using System;

using System.Data;

using System.Data.SqlClient;

public class FillWithConstraints

{

static string connectionString = “Data Source=localhost;” +

“Initial Catalog=Northwind;Integrated Security=SSPI”;

static string SQL = “SELECT * FROM Categories”;

public static void Main()

{

// Create ADO.NET objects.

SqlConnection con = new SqlConnection(connectionString);

SqlCommand comSelect = new SqlCommand(SQL, con);

SqlDataAdapter adapter = new SqlDataAdapter(comSelect);

DataSet ds = new DataSet();

// Execute the command.

try

{

con.Open();

adapter.FillSchema(ds, SchemaType.Mapped, “Categories”);

adapter.Fill(ds, “Categories”);

}

catch (Exception err)

{

Console.WriteLine(err.ToString());

}

finally

{

con.Close();

}

foreach (UniqueConstraint uc in

ds.Tables[“Categories”].Constraints)

{

Console.WriteLine(“*** ” + uc.ConstraintName + ” ***”);

Console.Write(“Primary Key: \t”);

Console.WriteLine(uc.IsPrimaryKey);

Console.Write(“Column: \t”);

Console.WriteLine(uc.Columns[0].ColumnName);

}

}

}

If you omit the FillSchema( ) method call, you’ll find that no constraint information is retrieved. With FillSchema( ), you’ll find that exactly one UniqueConstraint has been added to the DataSet:

*** Constraint1 ***

Primary Key:    True

Column:         CategoryID

The ForeignKeyConstraint

The ForeignKeyConstraint provides an easy way to impose referential integrity rules on records in a DataSet. The ForeignKeyConstraint serves two purposes: it prevents you from making DataSet changes that violate referential integrity, and it allows you to define what action to take with child rows when parent rows are updated or deleted.

When creating a ForeignKeyConstraint, you specify the parent and child DataColumn and optionally name the constraint. You add the ForeignKeyConstraint to the child DataTable . For example, the following code creates and applies a ForeignKeyConstraint that relates product records to a specific category:

ForeignKeyConstraint fc = new ForeignKeyConstraint(“CategoryID”,

ds.Tables[“Categories”].Columns[“CategoryID”],

ds.Tables[“Products”].Columns[“CategoryID”]);

ds.Tables[“Products”].Constraints.Add(fc);

Before performing this step, you should add a UniqueKeyConstraint on the CategoryID column in the parent (Categories) table to ensure that every relation can be resolved.

You can also pass constructor arguments to several overloads of the Add( ) method of a ConstraintCollection to create a ForeignKeyConstraint. The following code is equivalent to the previous example:

// Add a new ForeignKeyConstraint to the table’s Constraints collection.

dt.Constraints.Add(“CategoryID”,

ds.Tables[“Categories”].Columns[“CategoryID”],

ds.Tables[“Products”].Columns[“CategoryID”]);

When a ForeignKeyConstraint is in place, and constraint checking is enabled for the DataSet, all child column values (except nulls, if they are allowed) must point to an existing parent row.

Referential Integrity with ForeignKeyConstraint Rules

The ForeignKeyConstraint doesn’t just restrict changes; it can also propagate them depending on the value of three key properties: DeleteRule, UpdateRule, and AcceptRejectRule:

  • The DeleteRule determines what happens to child rows when a parent is deleted. By default, this is Cascade, which means all child rows are deleted along with the parent.
  • The UpdateRule determines what happens if the parent’s key column is modified. The default is Cascade, which means that the child rows are updated to point to the new value.
  • The AcceptRejectRule determines what happens when the DataRow.AcceptChanges( ) method is called (usually as part of a data source update). By default, this is None.

Generally, DeleteRule is the most important of these settings. The UpdateRule isn’t frequently used because you rarely change the value in a primary key column. In fact, this field often corresponds to an identity value that is generated by the data source. Tables 10-1 and 10-2 present the options for the Rule enumeration when used to set the DeleteRule and UpdateRule properties,

Table 10-1. Values for the DeleteRule
Value Used for DeleteRule
Cascade If the parent row is deleted, the child rows are also deleted.
None No action is taken on child rows. Thus, if you try to delete a parent that has linked children, an exception will be thrown. This is the default SQL Server behavior.
SetDefault If the parent row is deleted, the child rows have the default value placed in their foreign key column, if allowed (otherwise an exception is thrown).
SetNull If the parent row is deleted, the foreign key column of all children is set to null. If the DataColumn.AllowDbNull property disallows this, an exception is thrown.

Table 10-2. Values for the UpdateRule
Value Used for UpdateRule
Cascade If the linked column is changed in the parent, the foreign key column in all child rows is updated accordingly.
None No action is taken on child rows. Thus, if you change the linked column of a parent that has children, an exception is thrown. This is the traditional SQL Server behavior.
SetDefault If the linked column of a parent row is changed, the foreign key column in the child rows is reset to the default value, if allowed (otherwise an exception is thrown).
SetNull If the linked column of the parent row is changed, the foreign key column of all children is set to null. If the DataColumn.AllowDbNull property disallows this, an exception is thrown.

The AcceptRejectRule, on the other hand, can accept a value of Cascade or None from the AcceptRejectRule enumeration. If you set this value to Cascade, the AcceptChanges( ) method is called on child rows when the parent row is updated. This is rarely the behavior you want. In fact, because the AcceptChanges( ) method resets the DataRow.RowState to Unchanged, this technique may actually prevent child rows from being updated when you merge changes back into the data source! Thus, it’s strongly recommended that you use the default of None, unless you aren’t intending to commit DataSet changes.

Like UniqueConstraints, the usefulness of ForeignKeyConstraints may be limited by the fact that the DataSet contains only a subset of information from the DataSet. Thus, if you use a ForeignKeyConstraint, you can’t insert child rows that don’t have corresponding parent rows in the DataSet, even if these parent rows legitimately exist in the data source. However, you can still create and use DataRelation objects, as described in DataRelations.

Advertisements

3 Responses to “ADO.NET Core Classes:Constraints – Part 9”

  1. Excellent blog here! Also your web site quite a bit up fast! What web host are you the usage of? Can I get your affiliate hyperlink for your host? I want my site loaded up as quickly as yours lol

  2. This one is an inspiration personally to uncover out way more related to this subject. I must confess your data prolonged my sentiments in addition to I’m going to right now take your feed to stay updated on every coming weblog posts you would possibly possibly create. You might be worthy of thanks for a job perfectly accomplished!

  3. This is certainly one of the better entries I’ve discovered about this particular topic, you undoubtedly know your information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: