Click here to Skip to main content
15,500,220 members
Articles / Web Development / ASP.NET
Article
Posted 30 Jan 2012

Tagged as

Stats

18.8K views
18 bookmarked

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

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
30 Jan 2012CPOL1 min read
This article describes 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.

    image001.jpg

  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.

    image002.jpg

  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:

    image003.jpg

  4. Now create stored procedure which accepts the Role as input parameter and produces as output the XML String:
    SQL
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    GO
    Create PROCEDURE [dbo].[GetXMLMenus]
    (
        @Role as Varchar(100)
    )
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        -- SET NOCOUNT ON;
    -- SET @Role = 1;
        SELECT
        -- Map columns to XML attributes/elements with XPath selectors.
        MainMenus.Title AS '@Text',
        Url AS '@Url',
        (
            -- Use a sub query for child elements.
            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'),--The element name for each row.
                TYPE -- Column is typed so it nests as XML, not text.
        ) --AS 'products' -- The root element name for this child
            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'), --The element name for each row.
        ROOT('Menus') --The root element name for this result set.
    END
  5. Now create the aspx page and place the Menu control and xmlDataSource on it.

    image004.jpg

    image005_small.jpg - Click to enlarge image

  6. Now write the code on Page load to call the stored procedure by passing the parameter as role.
    C#
    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

  • 29th January, 2012: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
QuestionPls Help Me Pin
kanamala subin11-Apr-12 21:31
Memberkanamala subin11-Apr-12 21:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.