How to get the Menus Based on Role in XML Format and Bind them to the Menu Control in ASP.NET

Introduction

Following are the steps of how to get the Menus based on Role in XML format and bind them to the Menu control in ASP.NET :

1. Create the GroupMaster where we can have define the Roles or Group. This table contains the following columns, pkGroupId as Primary Key And GroupName and Description.

   

2. Create another Table where we can have the Menu table where we can have columns like pkMenuId as Primary Key which defines the menu, ParentID (defines the submenu under which menu the submenu should appear), Title, URL, description, Roles.

   

3. Create the third table, GroupMenuDetails where we can place relation of the above two tables primary key i.e., pkGroupId and pkMenuId. This table has the flag IsDisplay:

   

4. Now create stored procedure which accepts the Role as input parameter and produces as output the XML String:

   set ANSI_NULLS ON
   set QUOTED_IDENTIFIER ON
   GO
   Create PROCEDURE [dbo].[GetXMLMenus]
   (
       @Role as Varchar(100)
   )
   AS
   BEGIN
       --<span class="code-comment"> SET NOCOUNT ON added to prevent extra result sets from
   </span>    --<span class="code-comment"> interfering with SELECT statements.
   </span>    --<span class="code-comment"> SET NOCOUNT ON;
   </span>--<span class="code-comment"> SET @Role = 1;
   </span>    SELECT
       --<span class="code-comment"> Map columns to XML attributes/elements with XPath selectors.
   </span>    MainMenus.Title AS '@Text',
       Url AS '@Url',
       (
           --<span class="code-comment"> Use a sub query for child elements.
   </span>        SELECT
               SubMenus.Title AS '@Text',
               SubMenus.Url AS '@Url'
           FROM
               (
               SELECT
               MD.pkMenuId AS pkMenuId,
               MD.ParentId AS ParentID,
               MD.Title,
               MD.Description,
               MD.Url,
               GMD.pkGroupMenuId,
               GMD.IsDisplay
               FROM GroupMenuDetails AS GMD
               INNER JOIN Menu AS MD ON GMD.pkMenuId = MD.pkMenuId
               AND GMD.pkGroupId =  @Role
                ) AS SubMenus
           WHERE
               SubMenus.ParentID = MainMenus.pkMenuID
           AND SubMenus.ParentID IS NOT Null
               ORDER BY SubMenus.pkMenuID
           FOR
               XML PATH('SubMenu'),--<span class="code-comment">The element name for each row.
   </span>            TYPE --<span class="code-comment"> Column is typed so it nests as XML, not text.
   </span>    ) --<span class="code-comment">AS 'products' -- The root element name for this child
   </span>        collection.
   FROM
       (
               SELECT
               MD.pkMenuId AS pkMenuId,
               MD.ParentId AS ParentId,
               MD.Title,
               MD.Url,
                     MD.Description,
               GMD.pkGroupMenuId,
               GMD.IsDisplay
               FROM GroupMenuDetails AS GMD
               INNER JOIN Menu AS MD ON GMD.pkMenuId = MD.pkMenuId
               AND GMD.pkGroupId = @Role
               AND MD.ParentId IS Null
                ) AS MainMenus
   FOR
       XML PATH('Menu'), --<span class="code-comment">The element name for each row.
   </span>    ROOT('Menus') --<span class="code-comment">The root element name for this result set.
   </span>END

5. Now create the aspx page and place the Menu control and xmlDataSource on it.

   

   

6. Now write the code on Page load to call the stored procedure by passing the parameter as role.

   protected void Page_Load(object sender, EventArgs e)
       {
           if (!Page.IsPostBack)
           {
   populatemenu();
           }
      }
   
   protected void populatemenu()
   {
              --Create the sql connection .
               SqlConnection conn = new
              SqlConnection(ConfigurationManager.AppSettings["SqlConn1"]);
               --Role Parameter
               int Role =1;
               SqlCommand cmd = new SqlCommand();
               cmd.CommandType = CommandType.StoredProcedure;
               --Call to Stored Procedure
               cmd.CommandText = "GetXMLMenus";
               if ((Role !=0))
               {
                   cmd.Parameters.AddWithValue("@Role", Role);
                   cmd.Connection = conn;
                   SqlDataAdapter da_1 = new SqlDataAdapter(cmd);
                   DataSet ds_1 = new DataSet();
                   string result = "";
                   Xml XmlData = new Xml();
                   XmlDocument XmlDocument = new XmlDocument();
                   try
                   {
                       conn.Open();
                       --get the xml string
                       result =Convert.ToString(cmd.ExecuteScalar());
                       da_1.Fill(ds_1);
                       ds_1.DataSetName = "Menus1";
                       ds_1.Tables[0].TableName = "Menu1";
                       --Bind the get the xml string to xmldatasourse
                       XmlDataSource1.Data = result;
                       XmlDataSource1.XPath = "Menus/Menu";
                       --Bind the datasource to the menu  control
                       Menu1.DataSourceID= "XmlDataSource1";
                       Menu1.DataBind();
                   }
                   catch (Exception ex)
                   {
                       Response.Write(ex.Message);
                   }
                   finally
                   {
                       conn.Dispose();
                       cmd.Dispose();
                       da_1.Dispose();
                       ds_1.Dispose();
                   }
               }
           }

Advantage / Benefits

We can use the same code for TreeView control or for Menu control without any change in Stored Procedure or code. We will only have to place the TreeView instead of the Menu control.

History

• 29^th January, 2012: Initial version