ADO.NET DataSet is can be thought of an in-memory representation of the database. It can also contain various constraints namely Primary Key, Foreign Key and Unique Key. ADO.NET allows you to put constraints on DataTables irrespective of the same are present in actual database or not (However, most of the times you will mimic the database constraints in the dataset). This article illustrates how to do just that.IntroductionADO.NET DataSet is can be thought of an in-memory representation of the database. It can also contain various constraints namely Primary Key, Foreign Key and Unique Key. ADO.NET allows you to put constraints on DataTables irrespective of the same are present in actual database or not (However, most of the times you will mimic the database constraints in the dataset). This article illustrates how to do just that. Sample 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
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More ASP.NET Code Articles
More By Bipin Joshi
 developerWorks - FREE Tools!
|
This tutorial shows new users of IBM WebSphere Business Monitor Version 6.0.2 how to perform the "Hello World" equivalent for monitoring business process applications. It is intended to help you get familiar with the capabilities of the product.
FREE! Go There Now!
|
|
|
|
Learn the basics of the IBM Customer Information Control System (CICS). With a hands-on exercise, learn how to get your first CICS application up and running on your desktop using TXSeries V6.1 for Windows. The tutorial shows you how to download and install a free trial version of TXSeries V6.1.
FREE! Go There Now!
|
|
|
|
In this webcast, you'll get an introduction to the eXtreme Transaction Processing (XTP) features of WebSphere Extended Deployment and the common architectural traits required by XTP applications. See how WebSphere Extended Deployment's ObjectGrid feature provides a state-of-the-art infrastructure for hosting XTP applications.
FREE! Go There Now!
|
|
|
|
You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
|
|
|
|
As organizations integrate software into every aspect of business, they are constantly pressured to deliver faster, better, and cheaper results. Unfortunately, a “dis-integrated” software delivery approach reduces returns while increasing costs. This IBM Rational White Paper shows how Integrated Requirements Management aligns organizations around maximizing value and keeping pace with change.
FREE! Go There Now!
|
|
|
|
Asset Reuse is a key strategy for companies looking to create innovative solutions to solve complex software development problems. Searching for, identifying, updating, using and deploying software assets can be a difficult challenge. Listen to this webcast, to learn about strategies and tools that you can leverage for a successful project, including Rational Asset Manager, Rational Software Architect and WebSphere Service Registry and Repository.
FREE! Go There Now!
|
|
|
|
Manage, govern, and share services across your organization by using WebSphere Service Registry and Repository. Follow the hands-on exercises to learn how to navigate the Web interface to publish, find, reuse, and update services.
FREE! Go There Now!
|
|
|
|
David Barnes, Lead Evangelist for IBM Emerging Internet Technologies will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of IBM Rational Business Developer V7.1. Rational Business Developer offers rapid and simplified development of business applications and services through Enterprise Generation Language (EGL) tools, generating Java or mainframe solutions while shielding developers from technical complexities.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for process. The SOA Sandbox for process focuses on providing a trial environment with the necessary tooling and components required to gain a better understanding of business processes and how to best improve existing business processes to derive value quickly.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
/ 13 
