Imposing Constraints on DataSet
By Tribikram Rath
DataTables, Part 3
In Part 2 we examined adding AutoIncrement columns and foreign key constraints. In this final part we'll examine how to have a DataSet's schema information automatically set to the schema information of the underlying database table!
Automatically Adding the Database Constraints to a DataSet
In Part 1 of this article we discussed how to create and impose constraints in
DataTableinside Datasets. But what if we want to add the constraints that are defined in the database level? This section will discuss how to add the appropriate constraints to our
DataTable(s) that match those in the database. Here we are populating one or more
DataTables in a DataSet object through an appropriate DataAdapter object.
Fill method of the DataAdapter does not add detailed schema information that is
defined at the data source except the column name, its data type, the table columns and rows from the
source into the dataset. To fill a DataSet with existing constraint information from your data source,
you can either call the
FillSchema method of the DataAdapter, or set the
MissingSchemaAction property of the DataAdapter to
AddWithKey before calling
FillSchema method requires three parameters, as shown below:
SchemaType enumeration has two values:
|Apply any existing table mappings to the incoming schema. Configure the DataSet with the transformed schema.|
|Ignore any table mappings on the DataAdapter. Configure the DataSet using the incoming schema without applying any transformations.|
Please note that the
SchemaType usually should be set to
Mapped, because any
established table and column mappings are used.
The following code example adds schema information to a DataSet using
Now, let us examine the output generated by
FillSchema method of DataSet to know its
capability of mapping the constraints from the data source table. In order to print the schema
information to an XML file you may add the following line of code, which will generate
schemafile.xml on your Desktop.
Before you see the
SchemaFile.xml, let us have a look at the screenshot on the right, which
represents all the column and constraints information of the
Orders table graphically in SQL Server's
Enterprise Manager. For example, in this table the
OrderID column (a primary key) is to
int datatype of length 4 and it doesn't allow insertion of null values. Apart from
all the column information, the screenshot also shows the foreign key information on this table.
If you are familiar with XML syntax then, a little consideration will show that the schema information
Orders table is written to
SchemaFile.xml. The above XML listing
represents that the
OrderId column is a Primary Key Column, AutoIncremented, ReadOnly and
it accepts Integer data type.
If you further compare the schema information in the screenshot and the
then you will find that while certain schema is contained within the XML file, there are two bits of
schema information that are missing. Specifically, the schema information missing is:
schema Information for Foreign Key constraints; and schema information for AutoIncrement Seed and
AutoIncrement step for AutoIncrement column (
FillSchema method adds a structure of a
DataTable to the destination
DataSet and configures only the following
DataColumn properties if they exist at the data source:
AutoIncrement(You must set
FillSchema to populate a DataSet with schema information before filling the
DataSet with data ensures that primary key
constraints are included with the
DataTable objects in the DataSet. However, use of
MissingSchemaAction in your code requires extra run-time
processing so if you know the primary key information at design-time, it is advisable to add primary
key explicitly on the column(s) after filling the dataset as opposed to having this done automatically.
It is to be noted that all the constraints at the source cannot be enforced in the Dataset at run
time. Note that primary keys and unique constraints are added to the
ConstraintCollection, but other
constraint types are not added. Foreign key constraint information is not included and will need to
be created explicitly as we discussed earlier in this article.
As we saw in this article, the DataSet object supports constraints in order to ensure the integrity of data. Constraints are rules that are applied when rows are inserted, updated, or deleted in a table. You can define following types of Constraints/Columns in the
DataTables of a DataSet:
- Unique constraint - that checks that the new values in a column are unique in the table.
- Foreign-key constraint - that defines rules for updating in child table when a record in a master table is updated or deleted.
- AutoIncrement columns - ensures that the values in that column are unique and the column values to increment automatically.
- Primary Key Constraints - which checks values in the column are unique and not Null.
The dataset itself supports a Boolean
EnforceConstraints property that specifies whether
constraints are to be enforced or not. The default value of this property is set to True. When you
feel it is desired to turn the constraints off, simply change the value of this property to False.
You create foreign-key constraints by creating a
DataRelation object in a DataSet. In
addition to allowing you to programmatically get information about related records, a
object allows you to define foreign-key constraint rules.
FillSchema method of the Dataset can be used to copy the constraint definitions of the
tables at data source. But it is not possible to map all the constraints this way. Foreign-key
constraints, for example, must be explicitly declared on the tables inside DataSet. It is always
advisable to enforce these constraints explicitly at the design time to avoid extra processing at
About the Author
Currently working on ASP.Net/VB.Net as Senior Developer at i-Vantage India (P) Ltd., Secunderabad, India, which is an Offshore Development Center for i-vantage Inc, Cambridge, (www.i-vantage.com). He is a Bachelor of Engineering in Production Engineering and certified by Microsoft (MCP) and Brainbench.com (ASP 3.0). He can be reached at firstname.lastname@example.org. i-Vantage India is an offshore partner of i-Vantage Inc, Boston USA. I-Vantage main focus is on custom software development. i-Vantage has implemented successful off-shore/onshore model. i-Vantage believes in - Six Sigma is the Way We Work.