Click here to Skip to main content
15,443,778 members
Articles / Programming Languages / Visual Basic
Posted 6 Apr 2007


34 bookmarked

Using Data Relation with Data Table

Rate me:
Please Sign up or sign in to vote.
3.26/5 (21 votes)
6 Apr 2007CPOL2 min read
This article exemplifies how to use the Data Relation of ADO.NET with Data Table to create the in memory Data Relation between two tables.


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.

Screenshot - snap1.png

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_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 1
        obj_DataRow.Item(1) = "Computers"

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

        obj_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 3
        obj_DataRow.Item(1) = "Snacks"

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_DataRow = obj_DataTable.NewRow()
        obj_DataRow.Item(0) = 2
        obj_DataRow.Item(1) = "Dew"
        obj_DataRow.Item(2) = 2

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


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

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.

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.


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]


  • 7th April, 2007: Initial post


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Technical Lead NIIT Technologies Ltd.
India India

Comments and Discussions

QuestionCreate Childrow/parentrow??? Pin
sweety 2224-Feb-16 19:45
Membersweety 2224-Feb-16 19:45 
GeneralMy vote of 5 Pin
Nguyen Viet Loc5-Aug-14 17:13
MemberNguyen Viet Loc5-Aug-14 17:13 
QuestionGood article but does it handle lazy loading Pin
amaiz15-Nov-13 5:49
professionalamaiz15-Nov-13 5:49 
GeneralMy vote of 4 Pin
Rockstar_17-Jun-13 19:43
professionalRockstar_17-Jun-13 19:43 
GeneralMy vote of 1 Pin
Inba karthik21-Jan-13 18:15
MemberInba karthik21-Jan-13 18:15 
GeneralMy vote of 3 Pin
shiva861-Oct-10 16:08
Membershiva861-Oct-10 16:08 
QuestionHow to Use "GetChildRows" Pin
miss_neha_mishra9-Sep-08 2:31
Membermiss_neha_mishra9-Sep-08 2:31 
QuestionAwesome Pin
Ahmadnet29-Oct-07 20:20
MemberAhmadnet29-Oct-07 20:20 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.