When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips
Search

Sections:
Book Reviews
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
Web Hosts
XML
Information:
Advertise
Feedback
Author an Article

ASP ASP.NET ASP FAQs Message Board Feedback
 
Print this Page!
Published: Wednesday, October 16, 2002

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.

- continued -

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:

  1. Unique Constraints
  2. Primary Key Constraints
  3. Creating AutoIncrement Columns, and
  4. 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:

DS.Relations.Add("CustomerOrders", _
         DS.Tables("Customers").Columns("CustomerID"), _
         DS.Tables("Orders").Columns("CustomerID"))

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:

DS.Relations.Add("Relation Name", "Parent Column", _
                 "Child Column", "Bool createConstraints")

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 Customers DataTable 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:

DS.Tables("Customers").Columns("CustomerID").Unique = True
DS.Tables("Customers").Columns("CustomerID").AllowDBNull = False

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.

  • Read Part 2!



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article