Understanding the DataRelation and DataRelationCollection Objects

The DataRelation class represents a parent/child relationship between two tables. The DataRelation's overloaded constructors allow you to create a DataRelation object from a parent and a child column or a collection of columns. The first argument of the constructors is the name of the relation. The second and third arguments are the parent and child column(s), respectively. The last optional argument is a Boolean type, which indicates whether you want to create a constraint.

Before you create a DataRelation, let's fill a DataSet from two tables. Listing 8-7 fills a DataSet from the Customers and Orders tables.

Listing 8-7: Filling a DataSet from Two Tables

start example
 ' Create a Connection Object Dim conn As SqlConnection = New SqlConnection(ConnectionString) SQL = "SELECT * FROM Customers" conn.Open() Dim adapter As SqlDataAdapter = New SqlDataAdapter(SQL, conn) Dim ds1 As DataSet = New DataSet("Customers") adapter.Fill(ds1, "Customers") SQL = "SELECT * FROM Orders" adapter = New SqlDataAdapter(SQL, conn) Dim ds2 As DataSet = New DataSet("Orders") adapter.Fill(ds2, "Orders") ds1.Merge(ds2) 
end example

Now let's create a DataRelation between the CustomerId column of the Customers table and the CustomerID column of Orders table (see Listing 8-8).

Listing 8-8: Creating a DataRelation

start example
 ' Get the DataColumn objects from Customers ' and Orders tables of a DataSet Dim parentCol As DataColumn = New DataColumn() Dim childCol As DataColumn = New DataColumn() 'Retrieve columns from a DataSet parentCol = ds1.Tables("Customers").Columns("CustomerID") childCol = ds1.Tables("Orders").Columns("CustomerID") Dim bConstraints As Boolean bConstraints = True ' Create a DataRelation. Dim CustOrderRelation As DataRelation = _ New DataRelation("CustOrdersRelation", parentCol, childCol, bConstraints) 
end example

Now you can add a DataRelation to a DataSet using the Add method of DataRelationCollection, which you access through the DataSet.Relations property. (We dicuss DataRelationCollection in a moment.) Use this code to add the DataRelation:

 ' Add the relation to the DataSet. ds1.Relations.Add(CustOrderRelation) 

Even multiple sets of columns can participate in a relationship. The following code uses two columns to participate in a data relation:

 Dim parentCols() As DataColumn Dim childCols() As DataColumn parentCols(0) = DataSet1.Tables("Customers").Columns("CustID") parentCols(1) = DataSet1.Tables("Customers").Columns("OrdID") childCols(0) = DataSet1.Tables("Orders").Columns("CustID") childCols(1) = DataSet1.Tables("Orders").Columns("OrdID") Dim CustOrderRel As DataRelation CustOrderRel = New DataRelation("CustomersOrders", parentCols, childCols) 

After creating a DataRelation object, the next step is to see what properties are available in the DataRelation class. Table 8-4 describes the DataRelation class properties.

Table 8-4: The DataRelation Class Properties




Returns all child columns of a relation


Returns the FOREIGN KEY constraint of a relation if any available


Returns the child table (DataTable)


Returns the DataSet to which a relation belongs


Returns the collection of custom properties


Represents whether a DataRelation is nested (both get and set)


Returns all parent columns of a relation


Returns the UNIQUE constraint of the parent column


Returns the parent table (DataTable) of a relation


Represents the name of the relation (both get and set)

You can also create nested data relations. For example, if in addition to the Customers and Orders tables you have an Items table that stores data related to items such as Item ID, location, and so on, then you can create one more relation between the Orders and Items tables using the same steps you took for the Customers and Orders tables.

Now you'll learn how to access a DataRelation's properties. Listing 8-9 reads the DataSet, ParentTable, ChildTable, ParentColumns, ChildColumns, and RelationName properties of DataRelation.

Listing 8-9: Getting DataRelation Properties

start example
 ' Getting a DataRelation properties     Dim dtSet As DataSet = dtRelation.DataSet     ' Getting Tables     Dim pTable As DataTable = dtRelation.ParentTable     Dim chTable As DataTable = dtRelation.ChildTable     ListBox1.Items.Add("Parent Table: " + pTable.TableName)     ListBox1.Items.Add("Child Table: " + chTable.TableName)     'Getting columns     Dim parentCols() As DataColumn     Dim childCols() As DataColumn     parentCols = dtRelation.ParentColumns     childCols = dtRelation.ChildColumns     ' Print the ColumnName of each column.     Dim i As Integer     ListBox1.Items.Add("Parent Columns:")     For i = 0 To parentCols.GetUpperBound(0)       ListBox1.Items.Add(parentCols(i).ColumnName)     Next i     ListBox1.Items.Add("Child Columns:")     For i = 0 To childCols.GetUpperBound(0)       ListBox1.Items.Add(childCols(i).ColumnName)     Next i     ' Data relation name ListBox1.Items.Add("DataRelation Name:" + dtRelation.RelationName) 
end example

You just saw how to create a DataRelation object, add it to a DataSet, and access its properties. Now let's look at the DataRelationCollection class, which is a gateway to access data relations.

The DataRelationCollection class represents a collection of DataRelation objects of a DataSet, which is accessed through the Relations property of the DataSet. The Count property of DataRelationCollection returns the number of data relations in a collection, and the Items property allows you to access a DataRelation based on the index in the collection. Like the other collection classes, this class also provides Add, Remove, and other methods. Table 8-5 describes these methods.

Table 8-5: The DataRelationCollection Class Methods




Adds a DataRelation to the collection.


Copies an array of DataRelation objects to the end of this collection.


Returns True if the specified DataRelation can be removed from the collection; otherwise False.


Removes all DataRelation objects from the collection.


Returns True if a collection contains the specified DataRelation. You should make sure that a DataRelation exists in the collection before calling the Remove method to avoid exceptions.


Returns the index of the specified data relation.


Deletes the specified data relation from the collection.


Removes the data relation at the specified index.

Listing 8-10 calls the Contains, CanRemove, and Remove methods to delete a data relation from the collection.

Listing 8-10: Deleting a Data Relation from the Collection

start example
 Dim dtRelCollection As DataRelationCollection = dtSet.Relations If (dtRelCollection.Contains("CustOrdersRelation")) Then       If (dtRelCollection.CanRemove(dtRelation)) Then         dtRelCollection.Remove(dtRelation)      End If End If 
end example

Applied ADO. NET(c) Building Data-Driven Solutions
Applied ADO.NET: Building Data-Driven Solutions
ISBN: 1590590732
EAN: 2147483647
Year: 2006
Pages: 214

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net