Nested Repeater : Display Hierarchal Data in Web Form by Using ASP.Net Repeater

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:  

 

Jorder.JPG