To read the article online, visit http://www.4GuysFromRolla.com/articles/101602-1.2.aspx

Imposing Constraints on DataSet DataTables, Part 2

By Tribikram Rath


  • Read Part 1

  • In Part 1 we examined the basics of DataSets and DataTables, and how to specify relations among DataTables as well as add unique constraints to the columns in a DataTable. In this part we'll continue our investigation into imposing constraints on DataSet DataTables.

    Creating AutoIncrement Columns


    The AutoIncrement property of the column may be set to True to ensure that the values in that column are unique so that the column values to increment automatically when new rows are added to the table. The other associated properties of an AutoIncrement column are AutoIncrementSeed, AutoIncrementStep and ReadOnly. AutoIncrementSeed is the starting number of increment and AutoIncrementStep is the amount the auto-increment column is increased when each new record is added. (Typically these values are set to 1 and 1.)

    The following example creates a column that starts with a value of 1,000 and is automatically incremented by one step when a new row is inserted to the DataTable.

    Dim myTable As DataTable = New DataTable("EmployeeTable")
    
    ' Create a DataColumn and set various properties. 
    Dim myColumn As DataColumn = New DataColumn("EmployeeId")
    myColumn.DataType = System.Type.GetType("System.Int32")
    myColumn.ReadOnly = True
    myColumn.AutoIncrement = True
    myColumn.AutoIncrementSeed = 1000
    myColumn.AutoIncrementStep = 1
            
    ' Add the column to the table. 
    myTable.Columns.Add(myColumn)
    
    ' Let us Add 10 rows to the table.
    Dim myRow As DataRow
    Dim intX As Integer
    
    For intX = 0 To 9
        myRow = myTable.NewRow()
        myTable.Rows.Add(myRow)
    Next intX
    

    Creating a Primary Key Column for a Table


    When a DataColumn is created as the PrimaryKey for a DataTable, the table automatically sets the Unique property to True and the AllowDBNull property of the column to False. Alternatively, if you remove a column from the PrimaryKey property of a DataTable, the UniqueConstraint is removed. The following example defines a single column as the primary key:

    CustomerTable.PrimaryKey = New DataColumn() {CustomerTable.Columns("CustID")}

    The following example defines two columns as a primary key:

    CustomerTable.PrimaryKey = _
         New DataColumn() { CustomerTable.Columns("CustLName"), _
                            CustomerTable.Columns("CustFName")}
    
    ' ... Or you can use ...
    Dim PrimKey(2) As DataColumn
    PrimKey(0) = CustomerTable.Columns("CustLName")
    PrimKey(1) = CustomerTable.Columns("CustFName")
    
    CustomerTable.PrimaryKey = PrimKey
    

    Foreign Key Constraint


    Generally, a ForeignKeyConstraint is used to impose rule for updates and deletes in related tables. For example, if a particular value in a row of one table is updated or deleted, and that same value is also used in one or more related tables, a ForeignKeyConstraint will take decision what are the changes to be made in the related tables. That is, in order to take different actions on Delete and Update, the DeleteRule and UpdateRule properties of the ForeignKeyConstraints are set with different values as follows:

    RuleDescription
    CascadeUpdates or Deletes related rows. [default]
    NoneSpecifies that no action be taken on related rows.
    SetDefaultSets values in related rows to the default value.
    SetNullSets values in related rows to DBNull

    When creating a ForeignKeyConstraint, you can pass the DeleteRule and UpdateRule values to the constructor as arguments, or you can set them as properties as in the following example (where the UpdateRule value is set to the default, Cascade).

    Dim custOrderFK As ForeignKeyConstraint = _
               New ForeignKeyConstraint("CustOrderFK", _
                      custDS.Tables("CustTable").Columns("CustomerID"), _
                      custDS.Tables("OrdersTable").Columns("CustomerID"))
    
    
    ' Don't allow a customer with existing orders be deleted.
    custOrderFK.DeleteRule = Rule.None  
    custDS.Tables("OrdersTable").Constraints.Add(custOrderFK)
    

    AcceptRejectRule


    If you are well versed with the DataSet, DataTable and DataRow objects then you are probably aware of the AcceptChanges and RejectChanges methods that are common to all three of these objects. When a DataSet contains ForeignKeyConstraints, invoking the AcceptChanges or RejectChanges methods causes the AcceptRejectRule to be enforced. The AcceptRejectRule property of the ForeignKeyConstraint determines which action is to be taken on the child rows when AcceptChanges or RejectChanges is called on the parent row.

    The following table shows the list of the values to which the AcceptRejectRule can be set.

    AcceptRejectRuleDescription
    CascadeAccepts or rejects changes to child rows. [default]
    NoneSpecifies that no action be taken on child rows.

    The following line of code shows how to set the value for AcceptRejectRule property of the ForeignKeyConstraint:

    custOrderFK.AcceptRejectRule = AcceptRejectRule.None

    Now that we've looked at how to add the various types of constraints to a DataSet, you may be wondering if there is an easier way to apply such constraints to a DataSet other than explicitly specifying all constraints (as we've done thus far). The answer to that question is, Yes. There exists a FillSchema method which will populate a DataSet's schema with the schema information of an underlying database table. We will examine this method, and how to use it, in Part 3.

  • Read Part 3!

  • Article Information
    Article Title: ASP.NET.Imposing Constraints on DataSet Tables, Part 2
    Article Author: Tribikram Rath
    Published Date: October 16, 2002
    Article URL: http://www.4GuysFromRolla.com/articles/101602-1.2.aspx


    Copyright 2017 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers