Creating a DataRelation Between Two Tables in a DataSet...

A DataRelation allows you to relate two tables in a DataSet using a key-foreign key approach. This allows you to manipulate the tables in ways you might not otherwise be able to do.

By: John Kilgo

Date: June 22, 2003

Download the code.

A DataRelation allows you to establish a Parent-Child relationship between two tables in a DataSet. This allows you to present the data in an orderly fashion quite easily. The only trick to learning about the DataRelation is learning its specific syntax for creating the relationship between key and foreign key columns in the two tables, which must exist for the DataRelation to work.

In the example we will use for this article we will use only a Label control to present the data from the two tables we will be dealing with (Categories and Products from the Northwind database). That being the case, we can present the .aspx file very quickly and get on the code-behind file where all the coding is done. Following is the DataRelation.aspx file, which as you can see, contains only a simple Label control.

<%@ Page Language="vb" AutoEventWireup="false" Src="DataRelation.aspx.vb" Inherits="DataRelation"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>DataRelation</title>
<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:Label ID="lblDisplay" Runat="Server" />
</form>
</body>
</html>

We can now get to the code-behind file DataRelation.aspx.vb where the coding is done and where we explain the process of creating the DataRelation. In the first three-fourths of the file, shown immediately below, we access the database twice to first fill the DataSet with the Categories table data, and then again to fill the DataSet with the Products table data. In order to present our data later on, we also create two DataRow objects, one for the parent (Categories) table and one for the child (Products) table. Here is the top part of the code-behind file.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Public Class DataRelation
  Inherits System.Web.UI.Page

  Protected lblDisplay As System.Web.UI.WebControls.Label

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim objConn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet
    Dim dtrParent As DataRow
    Dim dtrChild As DataRow

    objConn = New SqlConnection(ConfigurationSettings.Appsettings("NorthwindConnection"))
    da = New SqlDataAdapter("SELECT * FROM Categories", objConn)
    ds = New DataSet()
    Try
      objConn.Open()
      da.Fill( ds,"Categories")
      da.SelectCommand = New SqlCommand("SELECT * FROM Products", objConn)
      da.Fill(ds, "Products")
    Catch exc As SqlException
      Response.Write(exc.ToString())
    Finally
      objConn.Dispose()
    End Try

Now we can get to the creation of the DataRelation and the presentation of data using it. Below is the remainder of the code-behind file. On the second line we use the ds.Relations.Add syntax to create the DataRelation. The syntax takes the form ds.Relations.Add("<RelationName>", ds.Tables("<ParentTableName>").Columns("<KeyColumnName>"),ds.Tables("<ChildTableName>").Columns("<ForeignKeyColumnName>")). There are other ways to do it, but this gets it done with the least amount of code.

After we have created the DataRelation we will use nested For Each loops to display the data in the Label control. In the outer loop we cycle through the parent table (Categories) getting the CategoryName using the dtrParent DataRow object. In the inner loop we use the dtrChild object to cycle through all of the child rows within each parent row getting the ProductName using the GetChildRows method of the dtrParent object. Notice that the argument passed to the GetChildRows method is the name ("Cat_Prod") of the DataRelation.

    'Create the Data Relationship
    ds.Relations.Add("Cat_Prod",ds.Tables("Categories").Columns("CategoryID"), _
                                ds.Tables("Products").Columns("CategoryID"))

    'Display the Category and Child Products Within
    For each dtrParent in ds.Tables("Categories").Rows
      lblDisplay.Text &= "<h3>" & dtrParent("CategoryName") & "</h3><ul>"
      For each dtrChild in dtrParent.GetChildRows("Cat_Prod")
        lblDisplay.Text &= "<li>" & dtrChild("ProductName") & "</li>"
      Next
      lblDisplay.Text &= "</ul>"
    Next
  End Sub

End Class

I think the hardest part of this to understand is the looping structure to display all of the rows in the two tables. But if you will run the program a time or two, and then examine the code again at the bottom of the code pages listed above, I believe you will begin to understand how it works.

You may download the code here.