Extracting a Menu Hierarchy from SQL 2005

I have recently started working with the Telerik radControls and one that I am using a lot is the Menu.  One thing I needed was a very flexible way to store menu hierarchies in SQL so i could edit menus without changing code, or sending out XML.

Storing straight XML is certainly an option now, however if you want relational flexibility you may be asking “Why do XML when you can link to SQL? “

Maybe you want to manipulate the XML or share it in other ways or maybe you create a View (like I do) instead of the Table as shown below so you can pull the data from some other structure...

This also lets you use caching of the XML at the Server OR Client side instead of just SQL Caching.

Or what if you want to pull them from a WebService, this surely supports that as well.

While this is based on Telerik's Menu control, it will work just as well with slight modification for any Menu Control (like the built in one) that supports XML Structures.

First you need a Links table with Appropriate Fields for the Menus:

CREATE TABLE [dbo].[MenuLinks](
   [LinkID] [int] IDENTITY(1,1) NOT NULL, 
   [ParentLinkID] [int] NULL,
   [Text] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [NavigateURL] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [LeftMenuImage] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [LinkIDName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [SortOrder] [int] NULL,
   [ToolTip] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
   [EntryDate] [dbo].[u_EntryDate] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_MenuLinks] PRIMARY KEY CLUSTERED 
([LinkID] ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Next we create a Function to do All the work:

CREATE FUNCTION [dbo].[ufn_BuildMenu](@ParentLinkID int)
RETURNS XML
WITH RETURNS NULL ON NULL INPUT 
BEGIN RETURN 
-- See --> ms-help://telerik.radmenu.37.Net2/telerik.radmenu.37.Net2/RadMenu~Telerik.WebControls.MenuItem_members.html
-- To extend the table for new capabilities
   (SELECT 
      [Text] as "@Text", 
      NavigateUrl as "@NavigateUrl", 
      LeftMenuImage as "@LeftLogo",
      ToolTip as "@Tooltip",
      'True' as "@NoWrap",
      CASE WHEN ParentLinkID=@ParentLinkID
      THEN dbo.ufn_BuildMenu(LinkID)
      END
   FROM MenuLinks 
   WHERE ParentLinkID=@ParentLinkID
   ORDER BY SortOrder
   FOR XML PATH('Item'), TYPE, ROOT('Group'))
END

Now for a Stored Procedure to call from the UI:

CREATE PROCEDURE [dbo].[usp_BuildMenu]
(@LinkIDName varchar(50) = 'System')
AS
DECLARE @LinkID int
SET @LinkID = (SELECT LinkID FROM MenuLinks WHERE LinkIDName=@LinkIDName)
SELECT 
   (SELECT dbo.ufn_BuildMenu(@LinkID))
FOR XML PATH('Menu')

Calling this StoredProc is simple and I leave that as an excercise for the user...

The StoredProc is going to return an XMLDocument or NULL, so plan accordingly, I have a function that does this in the UI called BuildMenu(). The Function returns the OuterXML of the Menu that is retrieved so I can use RadMenu.LoadXmlString(BuildMenu("MyMenuName")) and it populates the menu for me.

It has one optional Parameter, the LinkIDName so I can pass in any hierarchy base an retrieve everything for it at once, this lets me have User Defined menus inside of my SQL Database and the UI can be changed easily with no coding.

It is also really handy for creating multiple Context Menus and reaching out for them by an easy to remember name, or send the PageName for this parameter and you can embed the Menu in a Master Page and it will call a new menu for each page by passing BuildMenu() something like this:

Dim PageName As String = Request.RawUrl.Substring(Request.RawUrl.LastIndexOf("/") + 1)

You can even set it to fall back to the System Menu if none are defined for that page.

The only drawback to this with the sample table structure is that you may need to change the Flow on Groups and to do that you will have to step through the appropriate nodes to adjust them, it cannot be done easily in this technique from inside of SQL.

Of course, further experimentation to suit your needs will allow you to discover other techniques and structures to include the flow.

As an Example, this sample will duplicate the Default Functionality:

Private Function FixupMenuFlow(ByVal xdoc As XmlDocument) As XmlDocument
   Try
      Dim att As XmlAttribute = xdoc.CreateAttribute("Flow")
      att.Value = "Vertical"
      Dim node As XmlNode
      For Each node In xdoc.FirstChild
         If node.Name = "Group" Then node.Attributes.Append(att)
      Next
      att.Value = "Horizontal"
      xdoc.FirstChild.FirstChild.Attributes.SetNamedItem(att)
      Return xdoc
      Catch exc As Exception
      Return Nothing
   End Try
End Function

So, there you are, a structured result from relational data. The new XML Features in SQL2005 are really great, I am learning new ways to work with it every day.

posted @ Saturday, July 08, 2006 12:07 AM | Feedback (1)

Start experimenting with WPF in Under 15 Minutes

Lorin has written a nice little getting started article with everything included in the download so you don't have to hunt it all down. 

Its fairly safe to install this even on an “I need to use this every day... laptop”.

posted @ Thursday, July 06, 2006 9:41 PM | Feedback (1)