Sometimes, we need to nest data – say for instance – List the Categories, and under each Category, list the Products for that particular Category.
This sample uses the SQL Managed Provider to show exactly that, using the Categories and Products tables from the NorthWind Database.
As usual, just copy this code directly into a new page, change the database/connection information to match your system, and run it from your own computer. There is an updated portion here, explaining how to format the price for each of the products in the table, also.
You can read a Tutorial about this code here.
See this code in C# (provided by Mike Houston)
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SQLClient" %>
<html>
<head>
<meta name="GENERATOR" Content="ASP Express 2.1">
<title>Nested DataLists</title>
<script language="VB" runat="server">
Sub Page_Load(Source as Object, E as EventArgs)
Dim strConn as string = "server=YourServer;uid=UID;pwd=PWD;database=Northwind"
Dim MySQL as string = "Select CategoryID, CategoryName from Categories"
Dim MyConn as New SQLConnection(strConn)
Dim ds as DataSet=New DataSet()
Dim Cmd as New SQLDataAdapter(MySQL,MyConn)
Cmd.Fill(ds,"Categories")
Dim cmd2 As SqlDataAdapter = New SqlDataAdapter("select * from Products", MyConn)
cmd2.Fill(ds, "Products")
ds.Relations.Add("myrelation", ds.Tables("Categories").Columns("CategoryID"), ds.Tables("Products").Columns("CategoryID"))
dlCategories.Datasource=ds.Tables("Categories").DefaultView
DataBind()
End Sub
Function doFormat(sItem as Decimal)
Dim s as String
s=String.Format("{0:c}", sItem)
doFormat=s
End function
</script>
</head>
<body>
<asp:DataList runat="server"
Id="dlCategories"
GridLines="Both"
Bordercolor="black"
cellpadding="3"
cellspacing="0"
Headerstyle-BackColor="#DDDDDD"
Headerstyle-Forecolor="#777777"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="14"
Headerstyle-Font-Bold="true"
Font-Name="Arial"
Font-Bold="true"
Font-Italic="true"
Font-Size="11"
ForeColor="Red"
RepeatColumns="1">
<HeaderTemplate>
Categories & Products
</HeaderTemplate>
<ItemTemplate><%# Container.DataItem("CategoryName")%>
<br>
<asp:DataList runat="server"
Id="ChildDataList"
GridLines="None"
Bordercolor="black"
cellpadding="3"
cellspacing="0"
Headerstyle-BackColor="#8080C0"
Headerstyle-Font-Name="Arial"
Headerstyle-Font-Size="8"
Font-Name="Arial"
Font-Size="8"
datasource='<%# Container.DataItem.Row.GetChildRows("myrelation") %>'
RepeatColumns="3">
<ItemTemplate>
<%# Container.DataItem("ProductName") %>
- <%# doFormat(Container.DataItem("UnitPrice")) %>
</ItemTemplate>
</ASP:DataList>
</ItemTemplate>
</ASP:DataList>
</body>
</html>