Pages

Search Code Shode

Wednesday, July 20, 2011

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

This post will show you:

  1. How to store Hierarchical Menu Data in a Single Database Table
  2. 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]

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>

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

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

Happy Coding!
Happy Shoding!

اشتہارات