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.

Customizing the ChangePassword control and removing the required CurrentPassword field

It's very rare that what is already provided in asp.net under the Login controls fits my requirements out of the box without some tweaking. Not that any of these controls offer anything specialized, but certainly they are a big time saver if we can re-utilize their functionality.

First some background as to why i personally want to customize the ChangePassword control to suit my needs :

Password recovery is what i was after today, however i have hashed passwords, and recovery is impossible. If the user lost their password, then there is no way for me to know what their password is and send it back in clear text.

The ideal solution is to reset the password, however the autogenerated password is quite ugly and quite hard to remember. What I've decided to do is send the email during password recovery, but as part of the email, instead of telling the user their old password(which i can't).

I'm instead going to ask them to click on a tokenized link that will guarantee to me that they are indeed the ones that requested the password, send them to the page where they can provide a new password, in the background i'd be autogenerating a password first ofcourse, then updating the password with their new password because the MembershipUser.ChangePassword(oldPassword, newPassword) method requires Old password as one of it's two parameters.

This password change step, i'd like to be done using the ChangePassword control, however to my big surprise CurrentPassword Field is a required field that i cannot remove. This is also a field that I do not want asked for during the password change request(since my user has forgotten their password and are now going to provide their new pasword).

There is ofcourse no property or method in this control that removes the CurrentPassword field requirement, below is a screenshot of the ChangePassword control in designview, as you can note, the highlighted field is the CurrentPassword field i do not want.

I've done a quick look on google and in the asp.net/forums and didn't find anybody providing any proper solutions either, mostly vague replies : http://forums.asp.net/p/1189347/2038354.aspx

As you can read from the posts there, the issue seems to be two things which were also my same issues :
1) Remove the current password label/TextBox
2) Pass the new resetpassword to CurrentPassword Property which by the way is a getter only and not settable (SAD SAD)

Both of these things are not supported in this control. So let's quickly fix requirement 1 and there are a couple of ways to fix this :
a) You have to define a custom  <ChangePasswordTemplate>. This can be easily done by taking your ChangePassword control into DesignView in Visual studio, right click on the control and select "Convert to template". You can then switch to HtmlView and set the visibility of CurrentPasswordLabel, CurrentPassword and CurrentPasswordRequired controls.

b) If you prefer to do this in code, then you can find the Label and TextBox for CurrentPassword and set its visiblity to false. Since a is a nobrainer, i'm including a sample code of method (b) :

Label l = (Label)changePassword1.ChangePasswordTemplateContainer.

         FindControl("CurrentPasswordLabel");

if (l != null)

{
    l.Visible = false;
}
TextBox tb = (TextBox)changePassword1.ChangePasswordTemplateContainer.
FindControl("CurrentPassword");

if (tb != null)

{
    tb.Visible = false;
}
RequiredFieldValidator rfv = 

        (RequiredFieldValidator)changePassword1.

ChangePasswordTemplateContainer.FindControl("CurrentPasswordRequired");

if (rfv != null)
{
    rfv.Visible = false;

}

Now that we have the fields we want disabled, let's head onto fix issue 2 :
We can't pass the Autogenerated password to the CurrentPassword Property because its a getter only, however this getter returns the value from our CurrentPassword TextBox, and this job is done immidiately after ChangingPassword event fires. This is good news for us, so we can resolve issue 2 like this :

void changePassword1_ChangingPassword(object sender, 
LoginCancelEventArgs e)
{
    changePassword1.UserName = user.UserName;
    TextBox currentPassword = (TextBox)changePassword1.
    ChangePasswordTemplateContainer.FindControl("CurrentPassword");

    if (currentPassword != null)

    {
        currentPassword.Text = user.ResetPassword();

    }

}

Note that in the above code, user is a reference to a field of type MembershipUser. Ok, that's it. Now we have what were after, look at the screenshot below :