Using Data Relation with Data Table

		Last updated on 07, Apr, 2007
		download the source code for this article

Introduction

Ado.Net provides much more than just retrieving and submitting data. We can create in memories 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 Data Relation between two Data Tables to show the records on the basis of Master Child relationship.

Problem Statement

To display the product on the basis of selected category using a Data Table and Data Relation.

Using the code

Important Classes:
System.Data.DataTable
System.Data.DataRelation

To understand the Data relation we need to create two tables, Category and Products.<br>
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)
obj_DataTable.PrimaryKey = obj_PrimaryClmn 'Assigning the CategoryId column as Primary Key

'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

Category table have two columns. CategoryId column is primary column. This is specified by adding the array of primary columns to primary key property of Category Data Table

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

Product table contains the products that fall under a specific category. Each product contains a category id that specify the Category for this product. Given code suggest the column of Product Table.

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"))

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

'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 colums 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)

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

End Sub

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

After, this relation is added to the dataset that contains the both table.

obj_DataSet.Relations.Add(obj_DataRelation)

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

obj_ChildRows = obj_ParentRow.GetChildRows("relation_Category_Product")

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

Like Getchildrows() we can use Getparentrows() and Getparentrow() to fetch the parent row(s). Honestly speaking, Getchildrows() method can be used in no. of ways. This depends on the architecture of your application.

.Net Geek: A blog by Aakash Jain

.Net, Technology, Stuff and Musings

Using Data Relation with Data Table

Navigation