Database Driven XmlDataSource

C01H0047 I saw a question on Twitter a couple of days ago asking for an easy-to-use Menu control for ASP.NET.  I responded by asking what was wrong with the one that is built into ASP.NET 2.0 and above.

I never got a satisfactory answer.

The only real problem I’ve had with either the Menu control or the Tree control in .NET is that both of them expect an XMLDataSource.  But what if you want to generate the information from data in a database?

Obviously, you need to do some sort of translation.  And with the help of the XML DOM classes available in .NET, this is easy to do.

I’ll be discussing the Menu controls in this post, but the process is similar when using the Tree control.

The first thing you’ll want to do is to create a table in your database to hold the menu information.  You’ll need the following fields at a minimum:

  • ID
    Autoincrementing integer field
  • ParentID
    Points back to the record that is the parent node of this record.  If this is the top level Menu item (or Tree item) then this would be zero.
  • Label
    String representing the text to display to the user
  • URL
    String representing the URL that clicking this item will lead to.

You may also want to add:

  • Selectable
    Boolean value indicating if this item can be clicked or not
  • Target
    URL target (ie, _new, _top, or other…)
  • Sort
    Numeric Field that controls the sort order

A simple menu structure might look like:

ID ParentID Label URL
1 0 Menu One http://…
2 0 Menu Two http://…
3 1 One A http://…
4 1 One B http://…
5 1 One C http://…

Once you have the table, you’ll need an entry method to pull the data and turn it into an XMLDataSource.  Actually, you’ll need two, but the first one will look like this:

public static XmlDataSource GetNav()
{
    XmlDataSource returnItems = new XmlDataSource();
    returnItems.EnableCaching = false;
    returnItems.Data = "<siteMap></siteMap>";
    XmlDocument document = returnItems.GetXmlDocument();
    XmlElement sitemap = document.DocumentElement;
    FillSiteMapNode(sitemap, 0);
    returnItems.XPath = "/*/*";
    return returnItems;
}

Most of what this does is create the XmlDataSource object and fill it with data so that we can retrieve the XmlElement that we will need to fill with the information from our database.

The FillSiteMapNode() method is a recursive method that takes an XmlElement object and the parentId for all the child nodes.  To retrieve  the top-level menu items, we pass in zero.

Here is the actual method from one of my projects:

private static void FillSiteMapNode(
    XmlElement parentNode, int parentID)
{
    DataRowCollection rows =
        MainNavTableAdapter.
          GetDataByParentID(parentID,
          SessionVars.Instance.Role).Rows;
    foreach (MainNavRow row
        in rows)
    {
        XmlElement siteMapNode =
            parentNode.OwnerDocument.
              CreateElement("siteMapNode");
        // url attribute
        XmlAttribute url =
            parentNode.OwnerDocument.
              CreateAttribute("url");
        url.Value = row.URL;
        siteMapNode.Attributes.Append(url);
        // title attribute
        XmlAttribute title =
            parentNode.OwnerDocument.
              CreateAttribute("title");
        title.Value = row.DisplayName;
        siteMapNode.Attributes.Append(title);
        XmlAttribute target =
            parentNode.OwnerDocument.
              CreateAttribute("target");
        target.Value = row.Target;
        siteMapNode.Attributes.Append(target);
        XmlAttribute selectable =
            parentNode.OwnerDocument.
              CreateAttribute("selectable");
        selectable.Value = row.Selectable.ToString();
        siteMapNode.Attributes.Append(selectable);
        parentNode.AppendChild(siteMapNode);

        FillSiteMapNode(siteMapNode,
            row.CampbellSmartNavId);
    }
}

There is a lot of code here but it is basically the same stuff repeated about five times.  All we are doing is

  1. creating a new XmlElement for each record we retrieved from the database
  2. attaching attributes to it that our Menu control is expecting
  3. adding the new XmlElement to the XmlElement that was passed into the method
  4. calling the FillSiteMapNode() again to retrieve any child nodes from the database.

You can use this same basic pattern with any code that requires an XmlDataSource.

Related Post

  • DotNetNuke Modules – Creating Base ModulesDotNetNuke Modules – Creating Base Modules Now that we have DotNetNuke installed into Visual Studio we can go ahead and create our first modules. Actually, creating the modules is pretty simple. But it is even easier to do it […]
  • Using DataSets to Process XMLUsing DataSets to Process XML I started a project recently that requires me to process an XML file from Google. Being the lazy sort, I'd really rather just use the data as though it were part of a database and forget […]
  • Silverlight – DatabindingSilverlight – Databinding When I was learning DataBinding in both .NET 1.0 and .NET 2.0, I quickly discovered that most of what I learned about DataBinding for ASP.NET was useless as I moved to Windows Forms and […]
  • DotNetNuke Skins – ASCX vs HTML modeDotNetNuke Skins – ASCX vs HTML mode I got a question yesterday from a designer who is unfamiliar with ASP.NET asking what the difference is between ASCX mode and HTML mode when developing skins and containers for […]
  • ASP.NET Three Tiered w/ Client Side DataASP.NET Three Tiered w/ Client Side Data Last week, I created a tool that would allow the user to upload an XML file and have the web site process the file and return a report. All pretty standard stuff until you realize that if […]

About Dave Bush

Dave Bush is a .NET programmer and Certified ScrumMaster who is passionate about managing risk as it relates to developing software. When he is not writing or speaking about topics related to Application Lifecycle Risk Management (ALRM), he is an example to his peers as he develops web sites in the ASP.NET environment using industry best practices.

Awards & Certs