Adding Constraints to DataSet

IntroductionSample Database For our sample I will use an Access database with following tables : Customers : CustID, CustName Orders : OrdId, CustId, OrdAmt The tables contain only the fields that are relevant to our code. In reality you might have many other fields. Namespaces Involved We will be using following namespaces : System.Data System.Data.OleDbFilling up of the dataset Before starting any of our core examples we need to fill the DataSet with desired DataTables. Following code does this work Listing 1.1

Dim connstr As String 

connstr = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=c:db.mdb;Mode= ReadWrite"

Dim cnn As OleDbConnection 

Dim da As OleDbDataAdapter 

Dim ds As New DataSet() 

cnn = New OleDbConnection(connstr) 

da = New OleDbDataAdapter("select * from customers", cnn) 

da.Fill(ds, "customers") 

Here we populated the dataset with Customers table. Primary Key Constraint Now, we will add primary key constraint to the customers table from our dataset.

Dim pk(1) As DataColumn 

pk(0) = ds.Tables(0).Columns("custid") 

ds.Tables("customers").PrimaryKey = pk 

We have declared an array of DataColumn object. Our customers table has only one field that acts as primary field i.e. CustID hence we declared array to hold only one element. In case you have more fields simple increase size of the array. Next, we assigned which column to be treated as primary key. Finally, we set PrimaryKey property of the DataTable object. In order to test our code simply try inserting duplicate values in the data table. You may use code similar to following fragment :

Dim r As DataRow 

r = ds.Tables("customers").NewRow() 

r.Item("custid") = "CUST1" 

r.Item("custname") = "MyCustomer1" 

ds.Tables("customers").Rows.Add(r)

Foreign Key Constraints Before proceeding further we will add some code to the listing 1.1 that fills the dataset with another table "Orders"

Da.SelecteCommand.CommandText="select * from orders" 

da.Fill(ds, "orders") 

In our example database the two tables are related on field CustID. CustID is foreign key in Orders table. Add following code that establishes foreign key constraints between them.

Dim fk As ForeignKeyConstraint 

fk = New ForeignKeyConstraint("fk", ds.Tables(0).Columns("custid"), 

ds.Tables(1).Columns("custid"))

fk.DeleteRule = Rule.Cascade 

fk.UpdateRule = Rule.Cascade 

ds.Tables(1).Constraints.Add(fk)

ds.EnforceConstraints = True 

Here we have created an object of ForeignKeyConstraint with name "fk" that sets foreign key of orders table. Next, we have also set rulaes for cascade update and deletes. Finally we have added this constraint to the constraints collection of the datatable. To test our code add some record that violates the constraint

Dim r As DataRow 

r = ds.Tables(1).NewRow 

r.Item("OrdId") = "New_OrdID" 

r.Item("custid") = "CustID_not_available" 

ds.Tables(1).Rows.Add(r)

You will get error indicating that the dataset is being tested for foreign key constraints. Unique Constraints This final example shows how to add unique key constraints to the datatable.

Dim uc As New Data.UniqueConstraint(ds.Tables(0).Columns(1)) 

ds.Tables(0).Constraints.Add(uc)

Here, we added unique constraint for Customer name column of the customers table via UniqueConstraint class. As before you can check that the constraint is working by adding some duplicate values in the customer name field. [bold]About the author[/bold]Bipin Joshi works as a Software Engineer in Mumbai (India). His personal web site at www.bipinjoshi.com provides lot of information related to .NET. He also contributes to other web sites in the form of articles, tutorials and source code. He can be reached at bipinjoshi@yahoo.com blog comments powered by Disqus