New Media Specialist Aquent US-CO-Denver Post A Job | Post A Resume

Published: Wednesday, October 16, 2002

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 DataTable inside 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.

The 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 Fill.

The FillSchema method requires three parameters, as shown below:

SqlDataAdapter.FillShema("DataSet","SchemaTypeEnumeration","Tablename")

The SchemaType enumeration has two values:

Enumeration	Description
Mapped	Apply any existing table mappings to the incoming schema. Configure the DataSet with the transformed schema.
Source	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 FillSchema.

Dim DS As DataSet = New DataSet() DA.FillSchema(DS, SchemaType.Mapped, "Orders") '... SchemaType could be SchemaType.Mapped or SchemaType.Source DA.Fill(DS, "Orders") '... OR ... 'DA.MissingSchemaAction = MissingSchemaAction.AddWithKey 'DA.Fill(DS, "Orders")

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.

DS.WriteXmlSchema("C:\Documents and Settings\Administrator\Desktop\schemafile.xml")

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 accept 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.

Now let's examine the open SchemaFile.xml to check whether this schema information is written. In the SchemaFile.xml file you will see some lines like the following:

<xs:element name="OrderID" msdata:ReadOnly="true" msdata:AutoIncrement="true" type="xs:int" /> ....... Some other schema here....... <xs:unique name="Constraint1" msdata:PrimaryKey="true"> <xs:selector xpath=".//Orders" /> <xs:field xpath="OrderID" /> </xs:unique> ...

If you are familiar with XML syntax then, a little consideration will show that the schema information for the 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 SchemaFile.xml file, 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 (OrderId).

The 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:

• AllowDBNull
• AutoIncrement (You must set AutoIncrementStep and AutoIncrementSeed separately)
• MaxLength
• ReadOnly
• Unique

Using 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 FillSchema and 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.

Summary
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 DataRelation object allows you to define foreign-key constraint rules.

The 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 run-time.

Happy Programming!

Jupitermedia Corporation has two divisions: Jupiterimages and JupiterOnlineMedia

Jupitermedia Corporate Info

Legal Notices, Licensing, Reprints, Permissions, Privacy Policy.
Advertise | Newsletters | Tech Jobs | Shopping | E-mail Offers