First, a couple of tables, Categories and Products.
Categories:
Categories_ID Category Parent_ID
1 Fruit NULL
2 Meat NULL
3 Dairy NULL
4 Apples 1
5 Oranges 1
6 Chicken 2
7 Beef 2
8 Milk 3
9 Cheese 3
Products:
Products_ID ProductName Categories_ID
1 Red Apples 4
2 Green Apples 4
3 Bananas 1
4 Oranges 5
5 Chicken Wings 6
6 Breast Meat 6
7 Pork Chops 2
8 Ground Beef 7
First notice that with Parent_ID we can have multiple levels, but in this example we're only going to have Category and Subcategory. Some products are assigned to subcategories such as Green Apples (category: Fruit, subcategory: Apples) but others are just assigned to a top-level category (Pork Chops, Bananas).
Now for the stored procedure to build the navigation:
CREATE PROC [dbo].[mysp_catnav]
@Categories_ID int=NULL
AS
SELECT Categories_ID, Category As CategoryName
FROM Categories Category
WHERE Parent_ID Is NULL
ORDER BY Category
SELECT Categories_ID As Subcategories_ID, Category As SubcategoryName,
Parent_ID As Categories_ID
FROM Categories Subcategory
WHERE Parent_ID=@Categories_ID
OR Parent_ID=(SELECT Parent_ID FROM Categories WHERE Categories_ID=@Categories_ID)
ORDER BY Category
This returns two datasets which is why we'll use a SqlDataAdapter to fill the dataset. But first let's drop some controls on the page, a Treeview and XmlDatasource.
<asp:treeview id="tvCats" runat="server" showlines="True" datasourceid="xmlCats">
<databindings>
<asp:treenodebinding datamember="Categories" valuefield="ID" textfield="Name" navigateurlfield="URL" depth="0">
<asp:treenodebinding datamember="Category" valuefield="ID" textfield="Name" navigateurlfield="URL" depth="1">
<asp:treenodebinding datamember="Subcategory" value="ID" textfield="Name" navigateurlfield="URL" depth="2">
</asp:treenodebinding>
</asp:treenodebinding>
<asp:xmldatasource id="xmlCats" runat="server" transformfile="/nav.xsl" enablecaching="False">
</asp:xmldatasource></asp:treenodebinding></databindings></asp:treeview>
Notice we don't specify the DatasourceID of the XmlDataSource, that's because we're going to generate it on the fly. In the code file, set the .Data attribute during Page.Load:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
xmlCats.Data = xmlNav()
End Sub
Then the function to fill the dataset and manipulate it to fit a Treeview control:
Private oConn As New SqlConnection(ConnectionStrings("myConnectionString").ConnectionString)
Function xmlNav() As String
Dim daCats As New SqlDataAdapter("mysp_catnav", oConn)
Dim dsCats As New DataSet("Categories")
Dim sqlCats As SqlCommand = daCats.SelectCommand
With sqlCats
.CommandType = CommandType.StoredProcedure
.Parameters.Add("@Categories_ID", SqlDbType.Int).Value = IIf(Request.QueryString("c") = "", DBNull.Value, Request.QueryString("c"))
End With
daCats.Fill(dsCats, "Category")
If dsCats.Tables.Count > 1 Then
dsCats.Tables(1).TableName = "Subcategory"
Dim dtCats As DataTable = dsCats.Tables("Category")
Dim dtSubs As DataTable = dsCats.Tables("Subcategory")
Dim relSC As New DataRelation("Subcategories", dtCats.Columns("Categories_ID"), dtSubs.Columns("Categories_ID"))
relSC.Nested = True
dsCats.Relations.Add(relSC)
End If
Return dsCats.GetXml
End Function
You might want to validate that the QueryString "c" is actually a number. The datarelation is necessary to get the dataset to put the second table in the proper hierarchy in the XML data. .Nested = True is especially important.
And then the XSL to transform the raw dataset XML into what the Treeview expects. The values for URL, ID, and Text have to be attributes, not elements so you can't get a dataset to output it that way without transforms:
nav.xsl:
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:formatdata="urn:xsldataformatter">
<xsl:template name="top" match="/Categories">
<xsl:element name="Categories">
<xsl:attribute name="Name">All Products</xsl:attribute>
<xsl:attribute name="ID"></xsl:attribute>
<xsl:attribute name="URL">products.aspx</xsl:attribute>
<xsl:apply-templates select="Category" mode="cat" />
</xsl:element>
</xsl:template>
<xsl:template name="cat" match="Category" mode="cat">
<xsl:element name="Category">
<xsl:attribute name="Name">
<xsl:value-of select="CategoryName"/>
</xsl:attribute>
<xsl:attribute name="ID">
<xsl:value-of select="Categories_ID"/>
</xsl:attribute>
<xsl:attribute name="URL">products.aspx?c=<xsl:value-of select="Categories_ID"/>
</xsl:attribute>
<xsl:apply-templates select="Subcategory" mode="child" />
</xsl:element>
</xsl:template>
<xsl:template name="sub" match="Subcategory" mode="child">
<xsl:element name="Subcategory">
<xsl:attribute name="Name">
<xsl:value-of select="SubcategoryName"/>
</xsl:attribute>
<xsl:attribute name="ID">
<xsl:value-of select="Subcategories_ID"/>
</xsl:attribute>
<xsl:attribute name="URL">products.aspx?c=<xsl:value-of select="Subcategories_ID"/>
</xsl:attribute>
</xsl:element>
</xsl:template>
</xsl:stylesheet>
Finally just add a Gridview control to display the data from the Products table, using the Querystring value "c" as the filter, pass that as a QueryStringParameter for @Categories_ID.
CREATE PROC [dbo].[mysp_products]
@Categories_ID int=NULL
AS
SELECT Products_ID, ProductName
FROM Products
WHERE (Categories_ID=IsNULL(@Categories_ID, Categories_ID) OR Categories_ID IN (SELECT Categories_ID FROM Categories WHERE Parent_ID=@Categories_ID))
ORDER BY ProductName
That's about it! The product list will display all products matching the category and any child categories, so if you select Fruit you will get Bananas, Green Apples, and Red Apples but if you select Apples, you will only get Green Apples and Red Apples.
