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: