Display Hierarchical Data with Menu Control in ASP.NET using C# VB.NET
This post will show you:
Each step is explained and illustrated so that you can quickly grasp things from this post to build your web application's menu.
Before we start, lets see what we are trying to display:
- How to store Hierarchical Menu Data in a Single Database Table
- How to Display Hierarchical Data with ASP.NET Menu Control using C# and VB.NET.
Each step is explained and illustrated so that you can quickly grasp things from this post to build your web application's menu.
Before we start, lets see what we are trying to display:
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
Listing 1 - Database Script
CREATE TABLE [dbo].[Menu](
[MenuID] [int] IDENTITY(1,1) NOT NULL,
[Text] [varchar](50) NULL,
[Description] [varchar](255) NULL,
[ParentID] [int] NULL,
CONSTRAINT [PK_Menu] PRIMARY KEY CLUSTERED
(
[MenuID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
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 an asp:Menu and an XmlDataSource
Point to be noted here, we are going to implement two Menu's
- A simple menu
- A menu with CSS styling
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 below contains Mark up for both menu, have a look.
Listing 2 - Menu and XmlDataSource Web Controls
<link rel="stylesheet" type="text/css" href="jqueryslidemenu.css" />
<!--[if lte IE 7]>
<style type="text/css">
html .jqueryslidemenu{height: 1%;} /*Holly Hack for IE7 and below*/
</style>
<![endif]-->
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.2.6/jquery.min.js"></script>
<script type="text/javascript" src="jqueryslidemenu.js"></script>
<div>
<div>
Plain Menu Example:</div>
<asp:Menu ID="Menu1" DataSourceID="XmlDataSource1" runat="server" BackColor="#336699"
DynamicHorizontalOffset="2" Font-Names="Verdana" ForeColor="#CCFFFF" StaticSubMenuIndent="10px"
StaticDisplayLevels="1" Orientation="Horizontal">
<DataBindings>
<asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl" TextField="Text"
ToolTipField="ToolTip" />
</DataBindings>
<StaticSelectedStyle BackColor="#300000" HorizontalPadding="5px" VerticalPadding="2px" />
<DynamicMenuStyle BackColor="#300000" />
<DynamicSelectedStyle BackColor="#FF66CC" />
<DynamicMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" />
<DynamicHoverStyle BackColor="#336000" Font-Bold="False" ForeColor="White" />
<StaticHoverStyle BackColor="#336000" Font-Bold="False" ForeColor="White" />
</asp:Menu>
<br />
<br />
<br />
<div>
Menu With CSS Example:</div>
<asp:Menu ID="myslidemenu" DataSourceID="XmlDataSource1" runat="server" DynamicHorizontalOffset="2"
StaticSubMenuIndent="10px" StaticDisplayLevels="1" Orientation="Horizontal" CssClass="jqueryslidemenu">
<DataBindings>
<asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl" TextField="Text"
ToolTipField="ToolTip" />
</DataBindings>
<StaticSelectedStyle HorizontalPadding="5px" VerticalPadding="2px" />
<DynamicMenuStyle />
<DynamicSelectedStyle />
<DynamicMenuItemStyle HorizontalPadding="5px" VerticalPadding="2px" />
<DynamicHoverStyle Font-Bold="False" />
<StaticHoverStyle Font-Bold="False" />
</asp:Menu>
<asp:XmlDataSource ID="XmlDataSource1" TransformFile="~/Menu/TransformXSLT.xslt"
XPath="MenuItems/MenuItem" runat="server"></asp:XmlDataSource>
</div>
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 - a: The C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
namespace MenuApp
{
public partial class Menu : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DataSet ds = new DataSet();
string connStr = @"Data Source=Talha-PC\SQLExpress;Initial Catalog=MenuDB;User Id=talha;Password=YourPassword;Trusted_Connection=True;";
using (SqlConnection conn = new SqlConnection(connStr))
{
string sql = "Select MenuID, Text,Description, ParentID from Menu";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(ds);
da.Dispose();
}
ds.DataSetName = "Menus";
ds.Tables[0].TableName = "Menu";
DataRelation relation = new DataRelation("ParentChild",
ds.Tables["Menu"].Columns["MenuID"],
ds.Tables["Menu"].Columns["ParentID"], true);
relation.Nested = true;
ds.Relations.Add(relation);
XmlDataSource1.Data = ds.GetXml();
if (Request.Params["Sel"] != null)
Page.Controls.Add(new System.Web.UI.LiteralControl("You selected " + Request.Params["Sel"]));
}
}
}
Listing 3 - b: The VB.NET Code
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.Data
Imports System.Data.SqlClient
Namespace MenuApp
Partial Public Class Menu
Inherits System.Web.UI.Page
Protected Sub Page_Load(sender As Object, e As EventArgs)
Dim ds As New DataSet()
Dim connStr As String = "Data Source=Talha-PC\SQLExpress;Initial Catalog=MenuDB;User Id=talha;Password=YourPassword;Trusted_Connection=True;"
Using conn As New SqlConnection(connStr)
Dim sql As String = "Select MenuID, Text,Description, ParentID from Menu"
Dim da As New SqlDataAdapter(sql, conn)
da.Fill(ds)
da.Dispose()
End Using
ds.DataSetName = "Menus"
ds.Tables(0).TableName = "Menu"
Dim relation As New DataRelation("ParentChild", ds.Tables("Menu").Columns("MenuID"), ds.Tables("Menu").Columns("ParentID"), True)
relation.Nested = True
ds.Relations.Add(relation)
XmlDataSource1.Data = ds.GetXml()
If Request.Params("Sel") IsNot Nothing Then
Page.Controls.Add(New System.Web.UI.LiteralControl("You selected " + Request.Params("Sel")))
End If
End Sub
End Class
End Namespace
Note: You will have to supply your relevant SQL Server name, Username and Password in the above database connection string, connStr.
Figure 3 - XML Generated By Using DataSet Relationships
Step 4 - Add a Web Page That Implements an asp:Menu and an XmlDataSource
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.xslt 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-template name="MenuListing" />
</MenuItems>
</xsl:template>
<!-- Allow for recursive child nodeprocessing -->
<xsl:template name="MenuListing">
<xsl:apply-templates select="Menu" />
</xsl:template>
<xsl:template match="Menu">
<MenuItem>
<!-- Convert Menu child elements to MenuItem attributes -->
<xsl:attribute name="Text">
<xsl:value-of select="Text"/>
</xsl:attribute>
<xsl:attribute name="ToolTip">
<xsl:value-of select="Description"/>
</xsl:attribute>
<xsl:attribute name="NavigateUrl">
<xsl:text>?Sel=</xsl:text>
<xsl:value-of select="Text"/>
</xsl:attribute>
<!-- Recursively call MenuListing forchild menu nodes -->
<xsl:if test="count(Menu) >0">
<xsl:call-template name="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
Figure 5 - The Final Output
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
for CSS & JS:
I hope its easy to understand.
However, for any query, feel free to ask!
Happy Coding!
Happy Shoding!
