Click here to Skip to main content
Click here to Skip to main content

Using Data Relation with Data Table

, 6 Apr 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
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.

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.

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_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]

History

  • 7th April, 2007: Initial post

License

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

Share

About the Author

Aakash Jain
Technical Lead NIIT Technologies Ltd.
India India
http://www.aakashjain.com/about+me.aspx
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 PinmemberNguyen Viet Loc5-Aug-14 18:13 
QuestionGood article but does it handle lazy loading Pinprofessionalamaiz15-Nov-13 6:49 
GeneralMy vote of 4 PinmemberRockstar_17-Jun-13 20:43 
GeneralMy vote of 1 PinmemberInba karthik21-Jan-13 19:15 
GeneralMy vote of 3 Pingroupshiva861-Oct-10 17:08 
QuestionHow to Use "GetChildRows" Pinmembermiss_neha_mishra9-Sep-08 3:31 
QuestionAwesome PinmemberAhmadnet29-Oct-07 21:20 

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

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

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141223.1 | Last Updated 7 Apr 2007
Article Copyright 2007 by Aakash Jain
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid