Imposing Constraints on DataSet
By Tribikram Rath
The important difference between the evolved stage of ADO.NET and classic ADO is that there exists a new object, called the DataSet, which is physically separate and distinct from any data stores. Due to this reason, the DataSet functions as a standalone entity that represents a cache of data. Inside a DataSet, there could be tables, columns, relationships, constraints, views, and so forth. This article presents the picture of constraints inside the DataSet.
The data coming from a database, an XML file or from any other sources can all be placed into DataSet
object. (For example, to learn how to populate a DataSet with XML data, read:
XML, the DataSet, and a DataGrid.)
Various methods in the DataSet allow the developer to work with a programming model that is
always consistent, regardless the source of data. If the user makes any changes to the DataSet, they
can be tracked and verified before updating the original data source. In fact, the
method of the DataSet object creates another DataSet, which contains only the changes to the data.
This DataSet is then used by a DataAdapter (or other objects) to update the original data source. This
article will help you to maintain data integrity inside the DataSet before the updated data is sent
to the data source for final update.
How to add Constraints to a Table(s) inside a DataSet?
In order to maintain the integrity of data, constraints are applied to a column or several columns. To enforce constraints in the tables inside the DataSet you will have to set the value of the
EnforceConstraintsproperty of the DataSet to True.
In this article we will discuss the following one by one:
- Unique Constraints
- Primary Key Constraints
- Creating AutoIncrement Columns, and
- Foreign Key Constraint
Let us have a look on the following chunk of code to understand how these constraints can be imposed
on our tables within a Dataset. The following lines of codes are intended to fill two tables from the
NorthWind Database in the DataSet and a relation is added between these two tables for the
Let us examine the following code line in the above listing:
This line of code will establish a Parent-Child relationship between the
Orders table for
CustomerID column. By default, the DataSet object is
designed in such a way that required constraints are imposed automatically when you create a
relationship between two tables by adding a DataRelation to the DataSet. However, you can disable this
behavior by specifying a fourth parameter in the
Add method in the above line of code
createConstraints = false - when creating the relation. This value is True by
Add method can optionally take four parameters as follows:
Adding a Unique Constraint
UniqueConstraintsensure that all data in the specified column or columns is unique per row (although multiple values of
NULLare allowed). You can create a unique constraint for a column by using the
UniqueConstraintconstructor. The following example creates a
CustomerIdcolumn of the
DataTablein the DataSet.
A unique constraint can also be created and imposed on a column by setting the
property of the column to True. Alternatively, you can remove a unique constraint by setting the
Unique property of a single column to False.
If you wish to add
UniqueConstraints to a column that shouldn't allow
values, simply set the column's
AllowDBNull property to false, like so:
Please note that at the run time, if the user violates the imposed
you can add a
Try-Catch block to handle the related
Exceptions as follows:
Now that we've examined how to add unique constraints to a
DataTable's column, let's turn
our attention to creating auto-increment columns. We'll look at this and more in Part 2
of this article.