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.vb ===============
Imports System
Imports System.Collections
Imports System.ComponentModel
Imports System.Data
Imports System.Drawing
Imports System.Web
Imports System.Web.SessionState
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Data.SqlClient
Namespace NestedRepeater
Public Class NRepeaterInherits System.Web.UI.Page
Protected rptCategory As System.Web.UI.WebControls.Repeater
Private _dsOrderList As DataSet
Private _cmd As SqlCommand
Private _da As SqlDataAdapter
Private _con As SqlConnection
Private _strFilter As String
Private _productId As Integer
Private _conStr As String = "server=(local);uid=sa;pwd=;database=northwind"
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
' Put user code to initialize the page here
If (Not Page.IsPostBack) Then GetDataSet()
End If
End Sub 'PageLoad
Private Sub 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()
End Sub'GetDataSet
' GetOrderDetails method get executed on ItemBound event on rptProduct repeater
Protected Sub GetOrderDetails([source] As Object, e As RepeaterItemEventArgs)
'**** Get ProductId current populated row of rptProduct repeater
_productId = CInt(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
Dim rpt As Repeater = CType(e.Item.FindControl("rptOrder"), Repeater)
If Not (rpt Is Nothing) Then
'*** bind nested rptOrder repeater with default view
rpt.DataSource = _dsOrderList.Tables(2).DefaultView
rpt.DataBind()
End If
End Sub 'GetOrderDetails
Protected Overrides Sub OnInit(e As EventArgs)
' CODEGEN: This call is required by the ASP.NET Web Form Designer.
InitializeComponent()
MyBase.OnInit(e)
End Sub 'OnInit
Private Sub InitializeComponent()
End Sub 'InitializeComponent
End Class 'NRepeater End Namespace 'NestedRepeater
ScreenShot: 
NOTE: THIS ARTICLE IS CONVERTED FROM C# TO VB.NET USING A CONVERSION TOOL. ORIGINAL ARTICLE CAN BE FOUND ON C# CORNER (WWW.C-SHARPCORNER.COM).