Building and binding hierarchical data from the database to the ASP.NET Navigation Controls

If we need to bind our navigations controls to hierarchical data we define manually ourselves in an xml file, this is easy as pie. However, things can get rather complicated or not so obvious when we need to generate this data from a database. First off, what can we use that is already provided to us for binding hierarchical data to our navigation controls in ASP.NET ?

The already out of the box approach and ideal solution is to use the XmlDataSource control. This is quite a flexible datasource control since it not only enables us to define the path to our xml file containing the structure we need but also it allows us to define xml data to it via it's "Data" property. As you may have already guessed, because our data is going to be retrieved from our database, this is the property we shall be using :-)

But first let's look at a sample data structure we may have in our database. I'm using the classic Northwind database. Let's imagine we want to display products grouped by category. So in short, for every category node, we want to show products under it. Following is a screenshot of the categories and products table and how they relate :

The most common thing i see done is to manually loop through the records returned, create TreeNodes again manually and keep adding till you've build the TreeView or Menu, etc. Nothing wrong with this approach, since it works, however it is quite lengthy in code and time consuming too.

But that's not the main reason why I'm writing this article. The main reason is that all these navigation controls in ASP.NET know how to consume hierarchical data. Once they have this, they know how to render themselves without you needing to do anything special. This is indeed some powerful databinding support that we miss out on when we go the manual approach. Here i am going to list two different approaches :

1) DataSet and XSL Transformations, which while being clean and gives a more declarative model to work with (XSLT) versus the manual c# code approach.

2)The second approach uses SqlServer's XML generating capabilities which allows us to skip XSLT and the dataset all together.

DataSet and XSL Transformation approach:

Note below how our select statements retrieves all categories, while the second statement retrieves all products. We then relate both these tables using a key field they have in common "CategoryID". We also use a dataset since it's providing us a lot of functionality like relating the tables after data retrieval and representing the data in xml.

string GetHierarchicalData()
{
string queryString =
"SELECT CategoryID, CategoryName, Description FROM Categories AS Category;";
queryString +=
"SELECT ProductID, CategoryID, ProductName FROM Products AS Product";
DataSet ds = new DataSet("TreeView");
string connectionString = ConfigurationManager
.ConnectionStrings["LocalSqlServer"].ConnectionString;
using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(
queryString, connection);
adapter.Fill(ds);

ds.Tables[0].TableName = "Category";
ds.Tables[1].TableName = "Product";
// relate our tables
DataRelation dr = new DataRelation("FK_Products_Categories",
ds.Tables["Category"].Columns["categoryId"],
ds.Tables["Product"].Columns["categoryId"]);
// we'd like the products nested within 
// each Category Node. Thank you :-)
dr.Nested = true;
ds.Relations.Add(dr);
}
return ds.GetXml();
}

A small sample output of the generated xml we get by calling dataset's GetXml() method is here ->  DataSet Generated XML

As you can see, we have our rootnode "TreeView", then a childNode "Category", which in turn will contain every product node within it that belongs to this category. This nesting was established when we created a relationship btw the Categories table and the Products table using a DataRelation, where we set Nested = true; on it. Enabling the Nested property did just what the name says. It nested all our products within it's specific Category node.

While this is great, it does not help much with the TreeView control. Notice how every field our select statement returned is now an xml node and the data for the field is contained as inner text in our node. The TreeNodeBinding instead expects the fields to be contained as attributes, and the data as attribute values. Following screenshot is what we get when we run our TreeView bound to XmlDatasource

Definately, not what we are after. This is because the datasets GetXml method returned :

<Category>
<CategoryID>1</CategoryID>
<CategoryName>Beverages</CategoryName>
<Description>Soft drinks, coffees, teas, beers, and ales</Description>

while, what we need instead for the TreeNodeBinding to work is :

<Category CategoryID="1" CategoryName="Beverages" 

     Description="Soft drinks, coffees, teas, beers, and ales">

Had we the above xml structure with the fields defined as attributes we could easily create a TreeNodeBinding like this :

<asp:TreeNodeBinding Depth="2" DataMember="Category" 

  TextField="CategoryName" ValueField="CategoryID" ToolTipField="Description" />

So, our next task is to transform our xml to represent fields as attributes and their data as attribute values. We can accomplish this easily using XSL Transformations. By defining some instructions in our XSL file, we can read our XML(what our dataset GetXml method output) and transform it into what the Navigation Controls expect, outputting a totally new XML document.

<?xml version="1.0" encoding="utf-8"?>
<xsl:transform version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"/>
<xsl:template match="/">
<xsl:element name="TreeView">
<xsl:for-each select="TreeView/Category">
<xsl:element name="Category">
<xsl:attribute name="id">
<xsl:value-of select="CategoryID/text()" />
</xsl:attribute>
<xsl:attribute name="name">
<xsl:value-of select="CategoryName/text()" />
</xsl:attribute>
<xsl:attribute name="description">
<xsl:value-of select="Description/text()" />
</xsl:attribute>
<xsl:for-each select="Product">
<xsl:element name="Product">
<xsl:attribute name="id">
<xsl:value-of select="ProductID/text()" />
</xsl:attribute>
<xsl:attribute name="name">
<xsl:value-of select="ProductName/text()" />
</xsl:attribute>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>
</xsl:transform>

As you can see from the above XSL, we have defined two for-each loops, one that loops through category nodes and the other that loops through product nodes. Very simple but powerful stuff indeed.
The output xml after this transformation is XSL Transformed Xml output

<TreeView>
<Category>
<CategoryName name="" id="" description="" />
<Product name="" id="" />
...
</Category>
...
</TreeView>

ohh this is perfect. It was fun using XSL for the transmformation. Now, now, while it was fun, it can be a big pain in the behind if I'd have to do this all over again and again (Fortunately, i don't. Atleast now right now) :P

So, can we have made this job easier ? Surely. SQL Server and FOR XML Queries to the rescue

SQL Server and FOR XML Queries approach :

Now, what if, instead of having to do all this manual labor, we could get SQL Server to do all the xml generation for us, the way we wanted it ? Very much possible indeed. In effect this does not even need any explainations. Code speaks a thousand words, so here it is, the same xml output but this time we didn't use XSLT, nor did we use a dataset and the code is even more minimized.

string GetHierarchicalDataFromSqlServer()
{
string xml = string.Empty;
string queryString = @"
SELECT Category.categoryName as [name], Category.categoryId as id, 
Category.description as description, 
Product.productName as name, Product.productId as id
FROM categories AS Category 
INNER JOIN products AS Product
ON Category.categoryId = Product.categoryId
ORDER BY Category.categoryId                    
FOR XML Auto, ROOT('TreeView')";

string connectionString = ConfigurationManager.
ConnectionStrings["LocalSqlServer"].ConnectionString;

using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand SelectCommand = new SqlCommand(
queryString, connection);
connection.Open();
XmlReader xr = SelectCommand.ExecuteXmlReader();
xr.MoveToContent();// move to the root node
xml = xr.ReadOuterXml();
}
return xml;
}

The c# code to pass the XML data to our XmlDataSource bound to a treeView :

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
XmlDataSource1.Data = GetHierarchicalDataFromSqlServer();
}

That's it. No fuss, SQL Server's xml generation capabilities are just outstanding. And here is the declarative code i used to bind this hiearchical data to a treeview :

<form id="form1" runat="server">
<div>
<asp:TreeView ID="TreeView1" DataSourceID="XmlDataSource1" 

                 runat="server">
<DataBindings>
<asp:TreeNodeBinding Depth="1" DataMember="Category" 
TextField="name" ValueField="id" 

                                  ToolTipField="Description" />
<asp:TreeNodeBinding Depth="2" DataMember="Product" 
TextField="name" ValueField="id" />
</DataBindings>
</asp:TreeView>
</div>
<asp:XmlDataSource ID="XmlDataSource1" 

                  runat="server"></asp:XmlDataSource>
</form>

And here below is a screenshot of the treeview rendering itself. Fantastic!

Update 21 May 2008

A note i forgot to mention is that the XmlDataSource control has caching turned on by default. So in case you made a change in your xslt file and didn't see the change occuring, then you know it's using a cached copy. So make sure you disable caching during development.