Skip to main content

Building a Database Driven Hierarchical Menu using ASP.NET 2.0

Introduction
This tutorial will show you how to store hierarchical menu data in a single database table and how to retrieve and transform that data for display in Microsoft's Menu.
Each step is explained and illustrated so that you can quickly extrapolate from this article to build your web application's menu.
Note: This tutorial requires Visual Studio 2005 and SQL Server.
Step 1 - Create and Fill a Database Self Join Table
Our menu's table will use a self-join relationship which is the simplest method of storing hierarchical data. Child rows will use ParentID to establish a relationship with the MenuID of a parent row as shown below.
Figure 1 - Table Overview
 
Let us start by creating a database called MenuDb and a Table called Menu.  This can be done by running the following script from Microsoft's Query Analyzer.
Listing 1 - Database Script
CREATE DATABASE MenuDb
GO
USE MenuDb
GO
CREATE TABLE [Menu] (
      [MenuID] [int] IDENTITY (1, 1) NOT NULL ,
      [Text] [varchar] (50) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,
      [Description] [varchar] (255) COLLATESQL_Latin1_General_CP1_CI_AS NULL ,
      [ParentID] [int] NULL ,
      CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED 
      (
            [MenuID]
      )  ON [PRIMARY] 
) ON [PRIMARY]
GO
INSERT INTO MENU
Select 'Product','A List of Products', NULL
UNION ALL Select 'Applications','Appliations',NULL
UNION ALL Select 'Document','Documentation', NULL
UNION ALL Select 'Support','Support', NULL
UNION ALL Select 'Download','Download', NULL
UNION ALL Select 'Background','ProductBackground', 1
UNION ALL Select 'Details','Product Details', 1
UNION ALL Select 'Mobile Device','Mobile DeviceApplications', 2
UNION ALL Select 'Portal','Portal Applications',2
UNION ALL Select 'Web Applicaitons','WebApplications', 2
UNION ALL Select 'Demo','Demo Applicaitons', 2
UNION ALL Select 'Performance Tests','ApplicationPerformance Tests', 2
UNION ALL Select 'Tutorials','TutorialDocumentation', 3
UNION ALL Select 'Programmers','ProgrammDocumentation', 3
UNION ALL Select 'FAQ','Frequently AskedQuestions', 4
UNION ALL Select 'Forum','Forum', 4
UNION ALL Select 'Contact Us','Contact Us', 4
UNION ALL Select 'InternetRestrictions','Internet Restrictions', 6
UNION ALL Select 'Speed Solution','Speed Solutions',6
UNION ALL Select 'Application Center Test','Application Center Test Results', 12
UNION ALL Select 'Modem Results','Modem Results',12
GO
The table you created, displayed below, uses self-join relationships.  Rows that have a MenuID between 1 through 5 do not have parents and are considered root menu nodes.  Rows with MenuID of 6 and 7 are children of MenuID 1, and so on.
Figure 2- Parent, Child Relationships

 
Step 2 - Add a Web Page That Implements a Menu and an XmlDataSource
Add a new WebForm to your web application. Drag and drop a Menu and an XmlDataSource from the toolbar onto the WebForm with the following properties.
Listing 2 - Menu and XmlDataSource Web Controls
<asp:Menu ID="menu"DataSourceID="xmlDataSource" runat="server" 
BackColor="#FFFBD6"DynamicHorizontalOffset="2" Font-Names="Verdana" 
ForeColor="#990000"StaticSubMenuIndent="10px" StaticDisplayLevels="1" >
<DataBindings>
  <asp:MenuItemBindingDataMember="MenuItem" NavigateUrlField="NavigateUrl"
  TextField="Text" ToolTipField="ToolTip"/>
</DataBindings>
<StaticSelectedStyleBackColor="#FFCC66" />
<StaticMenuItemStyleHorizontalPadding="5px" VerticalPadding="2px" />
<DynamicMenuStyle BackColor="#FFFBD6"/>
<DynamicSelectedStyleBackColor="#FFCC66" />
<DynamicMenuItemStyle HorizontalPadding="5px"VerticalPadding="2px" />
<DynamicHoverStyleBackColor="#990000" Font-Bold="False"ForeColor="White"/>
<StaticHoverStyle BackColor="#990000"Font-Bold="False" ForeColor="White" />
</asp:Menu>
<asp:XmlDataSource ID="xmlDataSource"TransformFile="~/TransformXSLT.xsl"  
XPath="MenuItems/MenuItem"runat="server"/> 
At runtime, the XmlDataSource object is assigned an XML string (shown in step 3) which is then transformed by the XSLT file, TransformXSLT.xsl to another XML format (XSLT is covered in step 4).  The transformed XML is then consumed by the Menu as specified by the Menu's DataSourceID property.
Note: We are using the XmlDataSource property, XPath, to introduce an optional way to exclude the XML root node, MenuItems.  If the root node is not excluded then the menu will contain an extra level which can be controlled using the menu property StaticDisplayLevels.  The MenuItems root nodes can also be excluded in XSLT.
Step 3 - Retrieve Data and Create Nested Relationships
Now it is time to retrieve and begin formatting the menu data for use by Microsoft's menu.  The challenge is to establish parent child relationships and then create a hierarchical representation of the data.   A DataSet object is perfect for this because it can store the structure of an entire database, including relationships, and then convert that relational data into nested XML. 
Add the following C# code to your Page_Load method.  This code uses a DataAdapter to retrieve data from the single database table and to fill a DataSet.  Once filled, a DataRelation is applied to the DataSet to establish MenuID and ParentID dependencies.  Finally, a call to GetXML() retrieves a hierarchical XML representation of all relational data within the dataset.  
Listing 3 - The C# Code
using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;
 
public partial class _Default:System.Web.UI.Page
{
  protected void Page_Load(object sender,EventArgs e)
  {
    DataSet ds = new DataSet();
    string connStr = "server=localhost;Trusted_Connection=true;database=MenuDb";
    using(SqlConnection conn = newSqlConnection(connStr))
    {
      string sql = "Select MenuID, Text,Description, 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["MenuID"],
     ds.Tables["Menu"].Columns["ParentID"], true);
 
    relation.Nested = true;
    ds.Relations.Add(relation);
 
    xmlDataSource.Data = ds.GetXml();
 
    if (Request.Params["Sel"] != null)
      Page.Controls.Add(newSystem.Web.UI.LiteralControl("You selected " +
        Request.Params["Sel"]));
  }
}
Note: You will have to supply your relevant SQL Server name, Username and Password in the above database connection string, connStr.
As shown in the screenshot below, the call to ds.GetXml() correctly displays the nested hierarchy.
Figure 3 - XML Generated By Using DataSet Relationships

Step 4 - Using XSLT to convert XML for Microsoft's Menu
The XML returned from ds.GetXml() now needs to be reformatted for Microsoft's Menu. XmlDataSource is perfect for this task because it can use XSLT to transform the above XML to another XML format and then provide that to Microsoft's menu.
The below XSLT code does just this.  It first finds the root node called Menus and applies the MenuListing template to its root children. Next, each Menu node's elements are converted to MenuItem attributes.  Finally, each Menu node is checked for the existence of children.  If children exist, then the MenuListing will be recursively called until all children are processed.
Add an XSLT file to your project named TransformXSLT.xsl with the following code.
Listing 5 - XSLT
<?xml version="1.0"encoding="utf-8"?>
<xsl:stylesheet version="1.0"xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="xml"indent="yes" encoding="utf-8"/>


  <!-- Replace root node name Menus with MenuItems
       and call MenuListing for its children-->
  <xsl:template match="/Menus">
    <MenuItems>
      <xsl:call-templatename="MenuListing" />
    </MenuItems>
  </xsl:template>
  
  <!-- Allow for recursive child nodeprocessing -->
  <xsl:templatename="MenuListing">
    <xsl:apply-templatesselect="Menu" />
  </xsl:template>
  
  <xsl:template match="Menu">
    <MenuItem>
      <!-- Convert Menu child elements to MenuItem attributes -->
      <xsl:attributename="Text">
        <xsl:value-of select="Text"/>
      </xsl:attribute>
      <xsl:attributename="ToolTip">
        <xsl:value-ofselect="Description"/>
      </xsl:attribute>
      <xsl:attributename="NavigateUrl">
        <xsl:text>?Sel=</xsl:text>
        <xsl:value-ofselect="Text"/>
      </xsl:attribute>
      
      <!-- Recursively call MenuListing forchild menu nodes -->
      <xsl:if test="count(Menu) >0">
        <xsl:call-templatename="MenuListing" />
      </xsl:if>
    </MenuItem>
  </xsl:template>
</xsl:stylesheet>
Note: You can interactively debug an XSLT and an XML file from the IDE's XML Menu.
The XmlDataSource object's transformed XML that is provided to Microsoft's Menu is shown below.
Figure 4
    
Step 5 - Run the Web Page
Run your web page to display the following menu.
Figure 5 - The Final Output
Downloads
[Download Sample]
Conclusion
This article shows how to create a menu using a self-join database table, SqlDataAdapter, DataSet, DataRelation, XML, XmlDataSource and XSLT using just a few lines of code.   The self-join table stores parent child menu relationships.  The SqlDataAdapter fills the Dataset.  The DataSet employs a DataRelation which is used to create a nested XML.  XmlDataSource then applies the XSLT to the nested XML transforming it for use by Microsoft's Menu.
My next article will build upon this example and show how to create a WebForm that manipulates a database driven hierarchical menu.
Please provide feedback, letting me know how you liked this article.  Happy coding!
References
Use the below references to learn how to create table relationships, format menu styles and convert XML using XSLT.
·         MSDN - Adding a Relationship between Tables
·         Menu Quick Start Tutorial
·         Transforming XML with XSLT and ASP.NET

Comments

Post a Comment

Popular posts from this blog

Amway India New Business Plan 2015

Amway India Enterprises has changed its basic business plan which will put more money in to the packets of new comers or people who give little volume. It comes into effect from September 2015. It is seen as a historical move by industry experts. Amway rewards through many types of bonus of which Performance Bonus is the basic and important one. This is given on the turnover given by an individual and his team. So far the Point Value(PV) slabs are 300, 1000, 2000, 4000 ,7000 and 10000. The business value (BV) is calculated as 1 PV = Rs. 76/- as of now. The incentives for those PV slabs  are 6%, 9%, 12%, 15%, 18% and 21% respectively. Apart from retail income, if a distributor wishes to earn the performance bonus, he has to make a sale of approximately Rs. 28500/- ( 300 PV) in that particular month either individually or with a tea m. This was bit tough for those who does the business in their spare time. This resulted in attrition of the recruited people. To main...
Post a Comment
Read more

Amway India preferred customer program.

  Amway India preferred customer program is expected to be launched by November 2015. After making aggressive changes in its basic compensation plan in September 2015, the company announced another major restructuring. Read Amway India New Business Plan September 2015 Let us see in detail what is there in it for its preferred customers. The program starts effectively from 4th November 2015. Till now the only way to associate with Amway is to become their distributor by submitting required documents.  The major function of a distributor is selling products and build a sales team to sell more. Now the company opens up its membership to people who just wants to be their loyal customers. Through Amway India Preferred Customer Program, the customers will get Products at Distributor Price. (Discounted Price) Can participate in Loyalty Program Loyalty points can be redeemed as products For every 25 Rupees spent, 1 point is awarded. One point is One ru...
1 comment
Read more

Vastu shastra for Factory

It is difficult to start an industrial factory, but it more difficult to run it successfully. Because of this person who is successful in industry is respectable and admirable.  Designing factory as per vastushastra is challengeable for vastushastra itself. Because every Machine in the factory have crucial place of Tatvas (factors) among Panchmahabhute i.e. Compressor has Element of earth (Pruthvi Tatva), Air flow (Vayu Tatva) and Electric Meter (Agni Tatva). These three elements (Tatvas) are important. At that point which element should be given priority and where to place the machine depends upon innovation and intelligence of Vastu Expert. That makes it challenging. While choosing a plot for factory one should choose square or rectangle plot. Do not choose plot which is abnormal size. If possible slope of the plot should be from North to East direction and South to West portion should be high. Entrance door, free space or parking for two wheelers, Lawn, Garden ...
Post a Comment
Read more