ASP.NET 2.0: How to bind an asp:Menu to an SqlDataSource?

Question

i've found how to bind an asp:Menu to XML. i've found how to bind an asp:Menu to a site map (which is really binding it to XML). How do you bind an asp:Menu to a database?

The .NET Framework provides multiple data sources:

• HierarchicalDataSourceControl
• • XmlDataSource
• • SiteMapDataSource
• DataSourceControl
• • SqlDataSource
• • AccessDataSource
• • LinqDataSource

i want to use one that represents data from an SQL Server table. The data is stored in the standard hierarchical format that everyone uses:

NodeID    ParentNodeID    Caption        Url
========  ==============  =========      =================
{3234...  {3632...        stackoverflow  http://stackov...
{3632...  (null)          Questions      ~/questions.aspx
{3233...  (null)          Tags           ~/tags.aspx
{3235...  {3632...        google         http://www.goo...

And the query to return all the rows would be:

SELECT * FROM Nodes

What is the secret method that Microsoft intended me to use to mash that data into an asp:Menu?

---

Update: There is a good article on aspalliance.com: Building a Database Driven Hierarchical Menu using ASP.NET 2.0. Unfortunatly it describes how to perform XML data binding; while i'm interested in database binding.

Answer 1

There is a good article on aspalliance.com: Building a Database Driven Hierarchical Menu using ASP.NET 2.0. Each step is explained and nicely illustrated.

"In this article, Michael demonstrates how to create a database driven hierarchical menu with only a few lines of code using ASP.NET 2.0. This is a must read tutorial for everyone who needs a professional menu that is powerful and flexible with simplistic design."

The code for could be:

protected void LoadData()
{
    DataSet ds = new DataSet();
    string connStr = YOUR_CONNECTION_STRING_HERE;
    using(SqlConnection conn = newSqlConnection(connStr))
    {
      string sql = "Select NodeID, Caption, Url, ParentID from Menu";
      SqlDataAdapter da = newSqlDataAdapter(sql, conn);
      da.Fill(ds);
      da.Dispose();
    }
    ds.DataSetName = "Menus";
    ds.Tables[0].TableName = "Menu";
    DataRelation relation = newDataRelation("ParentChild",
     ds.Tables["Menu"].Columns["NodeID"],
     ds.Tables["Menu"].Columns["ParentID"], true);

    relation.Nested = true;
    ds.Relations.Add(relation);

    xmlDataSource.Data = ds.GetXml();
}

Answer 2

The menu does not support binding to SqlDataSource because it is a HierarchicalDataBoundControl - only hierarchical datasources are supported. You should implement your own HierarchicalDataSourceControl. Check here for an example. Alternatively you could create a custom sitemap provider and use the SiteMapDataSource as demonstrated here. Finally you can use a 3'rd party control which can bind to SqlDataSource.