ASP.NET Code
  Home arrow ASP.NET Code arrow Adding Constraints to DataSet
Iron Speed
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
ASP Web Hosting  
ASP.NET Web Hosting 
Dedicated Servers 
Download TestComplete 
Windows Web Hosting
 
IBM® developerWorks 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
ASP.NET CODE

Adding Constraints to DataSet
By: Bipin Joshi
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 4 stars4 stars4 stars4 stars4 stars / 10
    2003-06-01

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    Iron Speed
     
    ADVERTISEMENT

    Free Web 2.0 Code Generator! Generate data entry and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

    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 

    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 

    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

     

    IBM® developerWorks developerWorks - FREE Tools!


    Build Forge Express demo: Enabling software delivery excellence for small and midsized businesses

    This demonstration gives you an overview of IBM® Rational® Build Forge Express Edition, a global offering that provides a framework to automate and execute software processes. Rational Build Forge provides a software assembly line that can support all of your tools, technologies, and platforms so you can achieve a repeatable, reliable, and traceable build and release process.
    FREE! Go There Now!


    Check out the new Jazz space on developerWorks

    <a href="http://zeus.developershed.com/shonuff.php?blackbird=3853&zoneid=442&source=&dest=http%3A%2F%2Fwww.ibm.com%2Fdeveloperworks%2Fspaces%2Fjazz%3FS_TACT%3D105AGY31%26S_CMP%3DDEVSHED&ismap="><img src="http://images.devshed.com/corp/img/news/jazz01.gif" alt="developerWorks Jazz space" align="left"></a>You've heard the buzz about Jazz... want to know more about it from a developer's perspective? Check out the Jazz space on developerWorks. This space is an up-to-date resource for developers, including technical information about Jazz and products built on Jazz, like Rational Team Concert Express. The Jazz space includes content from a wide variety of sources, including links, feeds, and comments from experts.
    FREE! Go There Now!


    NEW! Demystifying the automation of custom controls: Part 2. A step-by-step example of using IBM Rational Functional Tester to automate custom controls

    This tutorial applies the concepts that were covered in the first part of this two-part series to a real-world example.
    FREE! Go There Now!


    NEW! Develop with Java and PHP technology on AIX Version 5.3, Part 6: Building the Java business application

    Set up a PHP Web interface for the Java(TM) business application using a database created in earlier in this series. The PHP Web interface collects information from users and sends the session data to the Java business application for processing and for a response.
    FREE! Go There Now!


    NEW! Improve your build process with IBM Rational Build Forge, Part 1: Create a continuous build and integration environment

    Learn how to implement a build management system that uses and extends your existing automation technologies. This tutorial shows, step-by-step, how to install and configure IBM Rational Build Forge to manage builds for Jakarta Tomcat from source code.
    FREE! Go There Now!


    NEW! Rational Talks to You: Grady Booch on Architecture

    Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
    FREE! Go There Now!


    NEW! Rational Talks to You: Scott Ambler on being agile in a global development environment

    Join this Rational Talks to You teleconference on December 6 at 1:00 pm ET to participate in an agile application development discussion and get your questions answered on using IBM Rational Method Composer in a distributed environment.Get your questions answered!
    FREE! Go There Now!


    NEW! Successful Change and Release Management for .NET

    Join this webcast to discover the key requirements for successful change and release management. Learn how to extend your .NET environment to improve productivity and collaboration, and address core problems afflicting team development. In this webcast, we’ll review typical challenges faced by customers and how to resolve them with the IBM Rational Change and Release Management solution, including Rational ClearCase, Rational ClearQuest and Rational Build Forge. Replay is available for 9 months.
    FREE! Go There Now!


    NEW! Trial download: IBM Rational Performance Tester V7.0.1

    Get a free trial download of the latest version of IBM Rational Performance Tester V7.0.1, a load and performance testing solution for teams concerned about the scalability of their Web-based applications. Combining multiple ease-of-use features with granular detail, Rational Performance Tester simplifies the test-creation, load-generation and data-collection processes that help teams ensure the ability of their applications to accommodate required user loads.
    FREE! Go There Now!


    NEW! Web development with Eclipse Europa, Part 1: The Java EE for Eclipse

    It's a good time to be a Web developer. You've never had more choices in terms of technologies. There are so many great open source Web servers, databases, programming languages, and development frameworks. No matter what combination of technologies you prefer to work with, there is an integrated development environment (IDE) that can increase your productivity: Eclipse. In this tutorial, Part 1 of a three-part "Web development with Eclipse Europa" series on how to use Eclipse for Web development with Java technology, PHP, and Ruby, we'll see how the latest release of Eclipse -- Europa -- can be used to rapidly develop Java Web applications. We'll use Java Platform, Enterprise Edition 5 (Java EE) for Eclipse to build a Web application for tracking and calculating baseball statistics.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    ASP.NET CODE ARTICLES

    - How to Use the ListBox Control in ASP.NET 2.0
    - How to Load XML Documents in ASP.NET 2.0
    - DataGrid Code
    - ASP.NET Guestbook
    - User Controls and Client Side Scripting
    - ASP.NET Programming with Microsoft's AS...
    - ASP.NET Basics (part 3): Hard Choices
    - ASP.NET Basics (part 2): Not My Type
    - ASP.NET Basics (part 1): Nothing But .Net
    - Directory Tree Browser
    - How to get the confirmation of Yes/No from a...
    - Complete example using custom errors and wri...
    - Paging Certain # records per page .NET style
    - General Methods of formatting and Subtractin...
    - .NET LinkButton web control

     
    Accelerating Trading Partner Performance
     
    Competing on Analytics
     
    Cost Effective Scaling with Virtualization and Coyote Point Systems
     
    Five Checkpoints to Implementing IP Telephony
     
    Hosted Email Security: Staying Ahead of New Threats
     




    © 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway