Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

How to create DataSet Relations in ASP.NET

Author: Richard Wand
Download Source Code : 1379_DataSetRelations.zip

This article will explain how we can create DataSet relations in ASP.NET.

 

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.

 

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to import data from Excel sheet to SQL Server database table in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net