Display Hierarchical Data with Menu Control in ASP.NET using C# VB.NET

Posted by Talha July 20, 2011

Display Hierarchical Data with Menu Control in ASP.NET using C# VB.NET

This post will show you:

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:

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 Table called Menu in our database.

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]

SET ANSI_PADDING OFF

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

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

<!--[if lte IE 7]>

html .jqueryslidemenu{height: 1%;} /*Holly Hack for IE7 and below*/

</style>

<![endif]-->

<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">

<asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl" TextField="Text"

ToolTipField="ToolTip" />

</DataBindings>

</asp:Menu>

<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">

<asp:MenuItemBinding DataMember="MenuItem" NavigateUrlField="NavigateUrl" TextField="Text"

ToolTipField="ToolTip" />

</DataBindings>

</asp:Menu>

<asp:XmlDataSource ID="XmlDataSource1" TransformFile="~/Menu/TransformXSLT.xslt"

XPath="MenuItems/MenuItem" runat="server"></asp:XmlDataSource>

</div>

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 - 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 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.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">

<xsl:call-template name="MenuListing" />

</MenuItems>

</xsl:template>

<xsl:template name="MenuListing">

<xsl:apply-templates select="Menu" />

</xsl:template>

<xsl:template match="Menu">

<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>

<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

Run your web page to display the following menu.

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:

http://www.dynamicdrive.com/cssexamples/media/jqueryslidemenu.css

http://www.dynamicdrive.com/cssexamples/media/jqueryslidemenu.js

I hope its easy to understand.
However, for any query, feel free to ask!

Happy Coding!
Happy Shoding!

Search This Blog

Code Shode

Display Hierarchical Data with Menu Control in ASP.NET using C# VB.NET

Popular Posts

Validate File Extension for ASP.NET FileUpload Control

MS SQL Server Function to Insert Line Break in String