The following code shows how to display hierarchal data from multiple tables using an ASP.Net repeater control. This article shows hierarchal data from the Categories, Products, Orders and Order Details tables of the Northwind database.
Stored Procedure to get multiple recordset from Northwind Database :
The following stored procedure is used to obtain records from the Categories, Products, Orders and Order Details tables of only Categories Ids 4 and 6.
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>
/// Summary description for NRepeater.
/// </summary>
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);
}
/// <summary>
/// Required method for Designer support - do not modify
/// the contents of this method with the code editor.
/// </summary>
private void InitializeComponent()
{
}
#endregion
}
}
ScreenShot: