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
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.
Comments
Post a Comment