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()
{
shabeer kbPosted Sep 6, 2007, 3:04 AM
is this code neseccary _dsOrderList.Relations.Add("categoryProduct",_dsOrderList.Tables[0].Columns["CategoryId"],_dsOrderList.Tables[1].Columns["CategoryId"]); _dsOrderList.Relations["categoryProduct"].Nested = true;