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:

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.

The article describes a trick: The method gets the data and exposes it as XML. — splattne, Nov 13 '08 at 10:01
Yes, it does. And this comment must be at least 10 characters long. — Ian Boyd, Nov 13 '08 at 14:30

splattne · Answer 1 · 2008-11-12T16:08:17.130

10

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();
}

edited Nov 12 '08 at 16:08

answered Nov 12 '08 at 16:02

splattne

102,760
52
202
249

i saw that, it's doing binding using XML - i'm asking about database bound. – Ian Boyd Nov 12 '08 at 19:40
Are you serious? Where so you see the XML? Which line exactly? – Slavo Nov 13 '08 at 08:51
It's a trick which works: a dataset from database is created and then exposed as XML (ds.GetXML()). – splattne Nov 13 '08 at 10:02
Just want to add, this code itself will not solve the problem. You have to download the sample project from the linked site and implement it with the rest of the code changes that includes an XLT transform as well. – TheTechGuy Nov 20 '15 at 07:34

score 4 · Accepted Answer · answered Nov 13 '08 at 23:35

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.

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

2 Answers2