Display hierarchal data in web form by using ASP.Net repeater

This below code shows how to display hierarchal data from multiple tables by using ASP.Net repeater control. This article shows hierarchal data from Categories, Products, Orders and Order Details tables of Northwind database.

Stored Procedure to get multiple recordset from Northwind Database :

The following stored procedure is used to get records from Categories, Products, Orders and Order Details table of Categories Id 4 and 6 only.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE GetOrderDetails
AS

-------- Get Category List -------------------
select
categoryid,
categoryname
from
categories
WHERE CategoryID IN (4,6)
order by
categoryname

-------- Get Product List ------------------------------
select
categoryid,
productid,
productname
from products
WHERE CategoryID IN (4,6)
order by productname

-------- Get Order List ---------------------------------
SELECT
OD.ProductID,
OD.OrderID,
dbo.Orders.OrderDate,
OD.Quantity,
OD.UnitPrice,
OD.Quantity*OD.UnitPrice Revenue
FROM
dbo.[Order Details] OD
INNER JOIN dbo.Orders
ON OD.OrderID = dbo.Orders.OrderID
where OD.ProductId IN (select productid from products WHERE CategoryID IN (4,6))
ORDER BY dbo.Orders.OrderDate
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

NRepeater.aspx.cs

===============

using System;

using System.Collections;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Web;

using System.Web.SessionState;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient ;

namespace NestedRepeater

{

///

/// Summary description for NRepeater.

///

public class NRepeater : System.Web.UI.Page

{

protected System.Web.UI.WebControls.Repeater rptCategory;

private DataSet _dsOrderList;

private SqlCommand _cmd;

private SqlDataAdapter _da;

private SqlConnection _con;

private string _strFilter;

private int _productId;

private string _conStr = "server=(local); uid=sa;pwd=;database=northwind";

private void Page_Load(object sender, System.EventArgs e)

{

// Put user code to initialize the page here

if (!Page.IsPostBack )

{

GetDataSet();

}

}

private void GetDataSet()

{

_con = new SqlConnection(_conStr);

_cmd = new SqlCommand();

_cmd.CommandType = CommandType.StoredProcedure ;

_cmd.CommandText = "GetOrderDetails";

_cmd.Connection = _con;

_da = new SqlDataAdapter(_cmd);

_dsOrderList = new DataSet();

_da.Fill(_dsOrderList);

/*

Dataset _dsOrderList is populated with three recordset

Table[0] : Categories

Table[1] : Products

Table[2] : Orders

*/

// Create relationship between CategoryId of Categories table and CategoryId of Products table

_dsOrderList.Relations.Add("categoryProduct",_dsOrderList.Tables[0].Columns["CategoryId"],_dsOrderList.Tables[1].Columns["CategoryId"]);

_dsOrderList.Relations["categoryProduct"].Nested = true;

// Bind main repeater i.e. rptCategory with dataset categories table

rptCategory.DataSource = _dsOrderList.Tables[0].DefaultView ;

rptCategory.DataBind();

_con.close();

}

// GetOrderDetails method get executed on ItemBound event on rptProduct repeater

protected void GetOrderDetails( object source, RepeaterItemEventArgs e)

{

//**** Get ProductId current populated row of rptProduct repeater

_productId = (int) DataBinder.Eval(e.Item.DataItem,"ProductId");

//**** set filter string to get filtered records from order table

_strFilter = "ProductId=" + _productId.ToString();

//**** get default view of filter rows of order table

_dsOrderList.Tables[2].DefaultView.RowFilter= _strFilter;

//**** get reference of nested rptOrder repeater of rptProduct repeater

Repeater rpt = (Repeater) e.Item.FindControl("rptOrder");

if(rpt != null)

{

//*** bind nested rptOrder repeater with default view

rpt.DataSource = _dsOrderList.Tables[2].DefaultView ;

rpt.DataBind();

}

}

#region Web Form Designer generated code

override protected void OnInit(EventArgs e)

{

//

// CODEGEN: This call is required by the ASP.NET Web Form Designer.

//

InitializeComponent();

base.OnInit(e);

}

///

/// Required method for Designer support - do not modify

/// the contents of this method with the code editor.

///

private void InitializeComponent()

{

}

#endregion

}

}

ScreenShot: