Imposing Constraints on DataSet DataTables
By Tribikram Rath
Introduction
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 GetChanges
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 EnforceConstraints property 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
CustomerID column:
Dim Con As SqlConnection
Dim DA As SqlDataAdapter
Con = New SqlConnection("server=local;uid=sa;" & _
"pwd=sa;database=Northwind")
DA = New SqlDataAdapter("select * from Customers", Con)
Dim DS As New DataSet()
DA.Fill(DS, "Customers")
DA = New SqlDataAdapter("select * from Orders", Con)
DA.Fill(DS, "Orders")
'Let us add a relations between "CustomerID" columns as follows:
DS.Relations.Add("CustomerOrders", _
DS.Tables("Customers").Columns("CustomerID"), _
DS.Tables("Orders").Columns("CustomerID"))
'Alternatively, You can add relations as follows:
'Dim ParentCol As DataColumn
'Dim ChildCol As DataColumn
'ParentCol = DS.Tables("Customers").Columns("CustomerID")
'ChildCol = DS.Tables("Orders").Columns("CustomerID")
'Dim CustOrderRel As New DataRelation("CustomerOrders", ParentCol, _
' ChildCol, True)
'DS.Relations.Add(CustOrderRel)
Let us examine the following code line in the above listing:
This line of code will establish a Parent-Child relationship between the Customers and
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
- i.e. createConstraints = false - when creating the relation. This value is True by
default.
This Add method can optionally take four parameters as follows:
Adding a Unique Constraint UniqueConstraints ensure that all data in the specified column or columns is unique per
row (although multiple values of NULL are allowed). You can create a unique constraint
for a column by using the UniqueConstraint constructor.
The following example creates a UniqueConstraint for the CustomerId column of
the CustomersDataTable in the DataSet.
Dim custTable As DataTable = DS.Tables("Customers")
Dim custUC As UniqueConstraint = _
New UniqueConstraint("UC1", custTable.Columns("CustomerID"))
DS.Tables("Customers").Constraints.Add(custUC)
A unique constraint can also be created and imposed on a column by setting the Unique
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 NULL
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 UniqueConstraint rules
you can add a Try-Catch block to handle the related Exceptions as follows:
Try
'Add some relevant code here.
Catch Exp1 As System.Data.ConstraintException
Label1.Text = "This CustomerID is already present!"
Catch Exp As System.Data.NoNullAllowedException
Label2.Text = "CustomerID should not be Null!"
'Catch other exceptions
...
End Try
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.
