i have found how you can bind an asp:Menu to XML. i have found how you can bind an asp:Menu to some site map (that is really binding it to XML). How can you bind an asp:Menu to some database?

The .Internet Framework provides multiple data sources:

i wish to play one that signifies data from an SQL Server table. The information is saved within the standard hierarchical format that everybody 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 also the query to come back all of the rows could be:

SELECT * FROM Nodes

What's the secret method that Microsoft intended me to make use of to mash that data into an asp:Menu?


Update: There's a great article on aspalliance.com: Building a Database Driven Hierarchical Menu using ASP.NET 2.0. Unfortunatly it describes how you can perform XML data binding while i am thinking about database binding.

There's a great article on aspalliance.com: Building a Database Driven Hierarchical Menu using ASP.NET 2.0. Each step is described and nicely highlighted.

"In the following paragraphs, Michael helps guide you to produce a database driven hierarchical menu with merely a couple of lines of code using ASP.Internet 2.. This can be a must read tutorial for everybody who requires a professional menu that's effective and versatile with simplistic design."

The code for might 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();
}

Recption menus doesn't support binding to SqlDataSource since it is a HierarchicalDataBoundControl - only hierarchical datasources are supported. You need to implement your personal HierarchicalDataSourceControl. Check here to have an example. Alternatively you can produce a custom sitemap provider and employ the SiteMapDataSource as shown here. Finally you should use a 3'rd party control which could bind to SqlDataSource.