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

I wrote about how we can make use of some of the databinding capabilities of controls, that can bind to hierarchical data like the TreeView around last week. While I covered pretty much everything, the data i was binding to was not deeply nested. Instead the data we were consuming was just 3 levels deep and that was that.

Today, we will be binding to a hierarchical datasource whose nesting structure is not known and can go as deep as it wants. Here, also SQL servers XML capabilities seem to come short since it does not support recursions. so if our data had this kind of deep nesting, SQL Servers FOR XML queries are not helping out much and we'd need to combine XSLT to our FOR XML Query.

Because of that, and since we end up using XSL for the transformation anyway, i'm going to be using the Dataset/XSLT approach I discussed in my previous post.

First, lets create a "pages" table in Sql server with the following schema as you can see in the screen shot below :

As you can note from above, we have a parentId field which is a self related field, related to the pageId in the same table. I personally prefer to move this field out to a junction table but each approach has it's pro's and con's, and this is simpler and serves our purpose :-)

Now, let's fill it with sample data, as you can note in the screenshot below :

ohh perfect. Now it's time to put together some c# code :

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
XmlDataSource1.Data = GetHierarchicalData();
}
string GetHierarchicalData()
{
string queryString =
"SELECT pageId, pageDisplayName, parentId FROM pages;";
DataSet ds = new DataSet("Pages");
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 = "PageItem";
// relate our tables
DataRelation dr = new DataRelation("FK_pageId_parentId", 
ds.Tables["PageItem"].Columns["pageId"],
ds.Tables["PageItem"].Columns["parentId"]);
// we'd like the page items nested within 
// each node for every child page.
dr.Nested = true;
ds.Relations.Add(dr);
}
return ds.GetXml();
}

The xml returned by our dataset looks like this : Dataset generated XML

Notice how every pageId that had a corresponding parentId was nested within it's parent, forming a hiearchical set of data. This is just perfect, all thanks to the dataset's DataRelation capabilities and our setting Nested=true on the DataRelation. This kind of recusive nesting didn't seem supported by SQL Servers FOR XML queries, and if it was, then it's probably trival. I did find a few implicit hints in the documentation that recursive nesting was not supported on SQL Server FOR XML Queries, however i could be wrong (so please do your homework).

Now our XSL file that does the recursion, but it's easier to make the transformation now, because the data is already structured out nicely with the proper hierarchical structure. Only thing missing and why we need to perform transformation is as i explained in the previous post, we need the fields as attributes and not as xml elements. So here is our recursive xslt that does just that :

<?xml version="1.0" encoding="utf-8"?>

<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml"/>
<xsl:template name="Menu" match="/">
<xsl:element name="Pages">
<xsl:for-each select="Pages/PageItem">
<xsl:variable name="parentId" select="parentId/text()"/>

<xsl:element name="PageItem">
<xsl:attribute name="pageId">
<xsl:value-of select="pageId/text()" />
</xsl:attribute>
<xsl:attribute name="pageDisplayName">
<xsl:value-of select="pageDisplayName/text()" />
</xsl:attribute>

<xsl:call-template name="processChildren" />

</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>
<!-- recursive template -->
<xsl:template name="processChildren">
<xsl:for-each select="PageItem">
<xsl:element name="PageItem">
<xsl:attribute name="pageId">
<xsl:value-of select="pageId/text()" />
</xsl:attribute>
<xsl:attribute name="pageDisplayName">
<xsl:value-of select="pageDisplayName/text()" />
</xsl:attribute>
<xsl:call-template name="processChildren" />
</xsl:element>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>

And here is the output of this miraculous transformation : XSL Transformed XML

Ohhh what beauty :-) and to complete, here is the declarative code to bind our asp.net menu to the XmlDataSource control that consumes this data :

<form id="form1" runat="server">
<div>
<asp:Menu ID="Menu1" MaximumDynamicDisplayLevels="1000" 
Orientation="Horizontal"
DataSourceID="XmlDataSource1" runat="server">
<DataBindings>
<asp:MenuItemBinding DataMember="PageItem" 
TextField="pageDisplayName" ValueField="pageId" />
</DataBindings>
</asp:Menu>
<asp:XmlDataSource ID="XmlDataSource1" XPath="Pages/PageItem"
TransformFile="~/MenuTransform.xsl" runat="server"></asp:XmlDataSource>
</div>
</form>

One gotcha you want to watch out for is the root node showing in the menu. Since we do not want to show the starting node, we explain this to the XmlDataSource nicely by setting an XPath expression : XPath="Pages/PageItem".

And below is our menu, when previewed in the browser. Time to congratulate ourselves on a job well done :-)

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.