DataSet provides in-memory representation of data. It is a standalone entity that represents cache of data. A DataSet can contain many different DataTables in it. These tables may or may not be related to each other. We can manipulate These DataTables in our ADO.NET code. ADO.NET code provides many other rich features for DataSet. We can define relations between DataTables of DataSet using DataRelation class. DataRelation class provides the facility to describe parent-child relationship between two tables in a DataSet. We can define as many relations between any two tables of DataSet.
1.Create new website and write the following HTML code in aspx page of a web form
<asp:Button ID="Button1" runat="server" Text="Create Relation and Display"
onclick="Button1_Click" />
<br />
<asp:Label ID="lblCatProds" runat="server" ></asp:Label>
2.Write code below in code behind file of C Sharp or Visual Basic
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt1 = GetCategoriesDetail();
DataTable dt2 = GetProductsDetail();
DataSet ds = new DataSet();
ds.Tables.Add(dt1);
ds.Tables.Add(dt2);
DataRelation relation = new DataRelation("CategoryToProductsRelation", ds.Tables[0].Columns["CategoryID"], ds.Tables[1].Columns["CategoryID"]);
ds.Relations.Add(relation);
foreach (DataRow categoryRow in ds.Tables[0].Rows)
{
string categoryName = categoryRow["CategoryName"].ToString();
lblCatProds.Text += "
Category Name: " + categoryName;
foreach (DataRow productRow in categoryRow.GetChildRows(relation))
{
string productName = productRow["ProductName"].ToString();
lblCatProds.Text += "
ProductName: " + productName;
}
}
}
private DataTable GetCategoriesDetail()
{
SqlConnection conn = new SqlConnection("Data Source=local;Initial Catalog=NORTHWND;Integrated Security=True");
SqlCommand cmd = new SqlCommand("SELECT * FROM Categories", conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return dt;
}
private DataTable GetProductsDetail()
{
SqlConnection conn = new SqlConnection("Data Source=local;Initial Catalog=NORTHWND;Integrated Security=True");
SqlCommand cmd = new SqlCommand("SELECT * FROM Products", conn);
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
conn.Close();
return dt;
}
}
VB.NET
Imports System.Data
Imports System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button1.Click
Dim dt1 As DataTable = GetCategoriesDetail()
Dim dt2 As DataTable = GetProductsDetail()
Dim ds As New DataSet()
ds.Tables.Add(dt1)
ds.Tables.Add(dt2)
Dim relation As New DataRelation("CategoryToProductsRelation", ds.Tables(0).Columns("CategoryID"), ds.Tables(1).Columns("CategoryID"))
ds.Relations.Add(relation)
For Each categoryRow As DataRow In ds.Tables(0).Rows
Dim categoryName As String = categoryRow("CategoryName").ToString()
lblCatProds.Text += "
Category Name: " + categoryName
For Each productRow As DataRow In categoryRow.GetChildRows(relation)
Dim productName As String = productRow("ProductName").ToString()
lblCatProds.Text += "
ProductName: " + productName
Next
Next
End Sub
Private Function GetCategoriesDetail() As DataTable
Dim conn As New SqlConnection("Data Source=local;Initial Catalog=NORTHWND;Integrated Security=True")
Dim cmd As New SqlCommand("SELECT * FROM Categories", conn)
conn.Open()
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
da.Fill(dt)
conn.Close()
Return dt
End Function
Private Function GetProductsDetail() As DataTable
Dim conn As New SqlConnection("Data Source=local;Initial Catalog=NORTHWND;Integrated Security=True")
Dim cmd As New SqlCommand("SELECT * FROM Products", conn)
conn.Open()
Dim da As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
da.Fill(dt)
conn.Close()
Return dt
End Function
End Class
First get two DataTables from database or create two DataTables dynamically. Write separate functions to get data from separate tables or write code where you want to create DataSet and create relation in DataSet. I have written two functions to get data from NORTHWIND database. First function get data from Categories table and second function get data from Products table. Categories and Products tables have parent-child relationship and we will create this relation in our ADO.NET code. Both functions return DataTable.
In button click event, create two DataTable object, call these function and add DataTable object to DataSet. Create an instance of DataRelation class and provide relation name and column name that is same for both tables to create parent-child relationship. Don’t forget to add the relation which you have created to DataSet. Now loop through each row of parent table to display a data and for each parent loop through child rows in the relation to display child table data. You can use any other functionally here to display or use parent-child relationship of two tables.
3.Now start debugging and see result of DataSet relation.
