Using Data Relation with Data Table

Download example - 23.18 KB

Introduction

ADO.NET provides much more than just retrieving and submitting data. We can create in-memory representation of tables. This is not limited; we can define the Parent and Child relation between those tables. This article will show how to use DataRelation between two DataTables to show the records on the basis of Master Child relationship.

Problem Statement

To display the product on the basis of a selected category using a DataTable and DataRelation.

Using the Code

The important classes are as follows:

System.Data.DataTable
System.Data.DataRelation

To understand the data relation we need to create two tables, Category and Products.

The code below will create the category table:

Private Function CreateCategoryTable()

        Dim obj_DataTable As New System.Data.DataTable("Category")
        'Declaring the array of DataColum to hold the Primary Key Columns
        Dim obj_PrimaryClmn(1) As System.Data.DataColumn
        Dim obj_DataRow As System.Data.DataRow

        obj_DataTable.Columns.Add(New System.Data.DataColumn("CategoryId"))
        obj_DataTable.Columns.Add(New System.Data.DataColumn("CategoryName"))

        obj_PrimaryClmn(0) = obj_DataTable.Columns(0)
        'Assigning the CategoryId column as Primary Key
        obj_DataTable.PrimaryKey = obj_PrimaryClmn

        'Entering the data in Category Table
        obj_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 0
        obj_DataRow.Item(1) = "Select Category"
        obj_DataTable.Rows.Add(obj_DataRow)

        obj_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 1
        obj_DataRow.Item(1) = "Computers"
        obj_DataTable.Rows.Add(obj_DataRow)

        obj_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 2
        obj_DataRow.Item(1) = "Drinks"
        obj_DataTable.Rows.Add(obj_DataRow)

        obj_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 3
        obj_DataRow.Item(1) = "Snacks"
        obj_DataTable.Rows.Add(obj_DataRow)
        obj_DataSet.Tables.Add(obj_DataTable)

End Function

The Category table has two columns. CategoryId column is the primary column. This is specified by adding the array of primary columns to the primary key property of Category DataTable.

'Specifying the primary key of data table
obj_DataTable.PrimaryKey = obj_PrimaryClmn

The Product table contains the products that fall under a specific category. Each product contains a categoryid that specifies the Category for this product.

Private Function CreateProductTable()

        Dim obj_DataRow As System.Data.DataRow
        Dim obj_DataTable As New System.Data.DataTable("Product")

        obj_DataTable.Columns.Add(New System.Data.DataColumn("ProductId"))
        obj_DataTable.Columns.Add(New System.Data.DataColumn("ProductName"))
        obj_DataTable.Columns.Add(New System.Data.DataColumn("CategoryId"))

        obj_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 1
        obj_DataRow.Item(1) = "PHP"
        obj_DataRow.Item(2) = 1
        obj_DataTable.Rows.Add(obj_DataRow)

        obj_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 2
        obj_DataRow.Item(1) = "Dew"
        obj_DataRow.Item(2) = 2
        obj_DataTable.Rows.Add(obj_DataRow)

        obj_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 3
        obj_DataRow.Item(1) = "ASP.Net"
        obj_DataRow.Item(2) = 1
        obj_DataTable.Rows.Add(obj_DataRow)

      .......

End Function

The CategoryId column of Product table contains the id of Category under in which the product falls.

'Tables are added in a Dataset
obj_DataSet.Tables.Add(obj_DataTable)

Now create a data relation between Category and Product tables:

Private Sub CreateDataRelation()

        Dim obj_ParentClmn, obj_ChildClmn As DataColumn

        'Get the reference of columns to create a relation between.
        obj_ParentClmn = obj_DataSet.Tables("Category").Columns("CategoryId")
        obj_ChildClmn = obj_DataSet.Tables("Product").Columns("CategoryId")

        'Creates a relation object, Parameters required are
        'New Relation Name, Object of Parent & Child column respectively.
        obj_DataRelation = New DataRelation("relation_Category_Product", _
                    obj_ParentClmn, obj_ChildClmn)

    'Adding Relation to the dataset that holds the tables.
    obj_DataSet.Relations.Add(obj_DataRelation)

End Sub

The DataRelation object is used to create a data relation between the two tables. To create a relation – relation name, object of Parent & Child columns are passed to the constructor of DataRelation.

Later, this relation is added to the dataset that contains both the tables.

obj_DataSet.Relations.Add(obj_DataRelation)

Now to fetch the child rows on the basis of the selected parent row, we use the Getchildrows() method:

 obj_ChildRows = obj_ParentRow.GetChildRows("relation_Category_Product")

The Getchildrows() method returns the collection of child rows of the Product table.

Points of Interest

Like Getchildrows(), we can use Getparentrows() and Getparentrow() to fetch the parent row(s). Honestly speaking, the Getchildrows() method can be used in a number of ways. This depends on the architecture of your application.
[Courtesy: .NET Geek]