Click here to Skip to main content
15,867,308 members
Articles / Web Development / ASP.NET / ASP.NET4.0

Generating menu from database according to user privilege

Rate me:
Please Sign up or sign in to vote.
4.97/5 (23 votes)
25 Sep 2012CPOL3 min read 127K   5.6K   47   24
Dynamic menu generation according to user privileges from database.

Introduction

This menu is designed to work with user privilege. Navigation is such an important part of our website. It’s how visitors navigate to the main areas of our site and makes it easy for them to find your good content. And if it is done from the database according to user privileges, that is is one of the plus points for the website.

This article is going to explain "How we can generate a menu from the database for users based on their privileges given by the site admin".

Front-End demonstration  

However, the front-end part for generating the menu will be similar to the generating the menu from database. Here I gonna modify the back-end part and generate the items from there only.

Before moving to the back-end, let's check out some basics of Generating Menu from Database. Here while creating this menu, I used some of the basic functions, like:

Getting the menu data from the database 

This function I am using to fetch the menu items for the particular user(Here I hard-coded the UserID as K010. you can set the session value here).  

C#
private DataTable GetMenuData()
{
    try
    {
        using (SqlConnection con = new SqlConnection(
          ConfigurationManager.ConnectionStrings["MenuWithCustomPrivs"].ConnectionString))
        {
            SqlCommand cmd = new SqlCommand("spMenuItem", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UserID", "K010");
            //K010 : Here I hardcoded the UserID. You can set your session's value UserID
            DataTable dtMenuItems = new DataTable();
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            sda.Fill(dtMenuItems);
            cmd.Dispose();
            sda.Dispose();
            return dtMenuItems;
        }
    }
    catch (Exception ex) {
        //Show the error massage here
    }
    return null;
}

Adding the top/parent menu items: 

Here with this code I'll add the menu items whose Parent ID is NULL. And also we'll call the function to bind the child menu. In this function I am calling another function AddChildMenuItems which will bind the child item for that menu item.

C#
private void AddTopMenuItems(DataTable menuData)
{
      DataView view = null;
      try
      {
          view = new DataView(menuData);
          view.RowFilter = "ParentID IS NULL";
          foreach (DataRowView row in view)
          {
               //Adding the menu item
               MenuItem newMenuItem = new MenuItem(row["Text"].ToString(), row["MenuID"].ToString());
               menuBar.Items.Add(newMenuItem);
               //Calling the function to add the child menu items
               AddChildMenuItems(menuData, newMenuItem);
          }
      }
      catch (Exception ex)
      {
          //Show the error massage here
      }
      finally {
            view = null;
      }
}

Adding child menu items 

Here I am using this code to bind the child items in the menu. I'm filtering the menu items here according to the Parent ID passed from the above function.

C#
private void AddChildMenuItems(DataTable menuData, MenuItem parentMenuItem)
{
    DataView view = null;
    try
    {
        view = new DataView(menuData);
        view.RowFilter = "ParentID=" + parentMenuItem.Value;
        foreach (DataRowView row in view)
        {
            MenuItem newMenuItem = new MenuItem(row["Text"].ToString(), row["MenuID"].ToString());
            newMenuItem.NavigateUrl = row["NavigateUrl"].ToString();
            parentMenuItem.ChildItems.Add(newMenuItem);
            // This code is used to recursively add child menu items filtering by ParentID
            AddChildMenuItems(menuData, newMenuItem);
        }
    }
    catch (Exception ex)
    {
        //Show the error massage here
    }
    finally
    {
        view = null;
    }
}

So, this was really basic functions to bind the menu control.

Back-end demonstration 

Now, coming to the main point, the database structure, here I am having five different tables which will have the details about user, groups(roles), it's privileges and menu items.

  1. tblGroupPrivMst: This table contains the information about the groups / roles and the privilege bit applicable for that role.
  2. tblMenuMst: This table contains the menu description, menu id and navigate url fields.
  3. tblUserGrpMap: This  is a mapping table, which will take care about the relation of user to respective group.  
  4. tblDesgMst: This  table contains the designation of the user under the website.
  5. tblUserMst: This  table contains  the details of the user.Image 1

The privilege bit of a role/group will be stored in the table tblGroupPrivMst, and the length of the privilege bit will be equal to total length of the menu items. These privilege bits are nothing but a combination of 0 and 1 digit. Suppose, total number of menu items is 10 then the privilege bit will be 0101011100 and will be arranged in such manners so that the menu items will be consecutive to the bit position. Here 0 at first position indicates that the item at first position will not be applicable for that role. 

Generating Menu

You would love to write the simple SQL Stored Procedures to generate menu. The SP which I am using to generate menu items according to the user privilege is as follows:

SQL
CREATE PROCEDURE [dbo].[spMenuItem]
    @UserID [varchar](50)
WITH EXECUTE AS CALLER
AS
BEGIN
    --DECLARE @GroupCode VARCHAR(5)
    --SET @GroupCode=(SELECT DISTINCT GroupCode FROM tblUserGrpMap WHERE UserID=@UserID)
    CREATE TABLE #TMP(MenuID INT, Text VARCHAR(50), Description VARCHAR(50), ParentID INT, NavigateUrl VARCHAR(100))
    DECLARE @VAL VARCHAR(MAX), @Pos INT, @len INT
    SET @VAL=(SELECT REPLACE(REPLACE(CONVERT(VARCHAR(30), SUM(CAST(PrivilegeID AS 
        NUMERIC(30, 0)))), '2', '1'), '3', '1') FROM tblGroupPrivMst WHERE GroupCode 
        in (SELECT GroupCode FROM tblUserGrpMap WHERE UserID=@UserID))
    SET @Pos=1
    SET @len=LEN(@VAL)
    WHILE(@len!=0)
    BEGIN
        DECLARE @Value CHAR(1)
        SET @Value=SUBSTRING(@VAL, @Pos, 1)
        IF @Value=1
        BEGIN
            --PRINT @Value
            INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID=@Pos
        END
        SET @Pos=@Pos+1
        SET @len=@len-1
    END
    --For first Node (Inserting The Parent Node)
    INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT 
      ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
    --For second Node (Inserting The Parent Node)
    INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT 
      ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
    --For third Node (Inserting The Parent Node)
    INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT 
      ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
    SELECT * FROM #TMP ORDER BY MenuID ASC
    DROP TABLE #TMP
END

This SP will generate the menu items till three levels. If you are having more levels the you need to repeat this insert command:

INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP)) 

The output    

After executing your program you'll get the menu something like this:

Image 2

I know, I am quite boring at the design. 

You can update the privilege of a user using Sql Update Command or you can also create a screen for updating user privileges.  In later part I'll try to cover all the things like, Assigning menu privilege to the user, Mapping a user with multiple roles..

End-point

I hope this article will help you.  

Thanks for spending your precious time to read this article. Any suggestion and guidance will be appreciated.

License

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


Written By
Software Developer (Senior)
Malaysia Malaysia
I've been working with various Microsoft Technologies. I have earned my Microsoft Certified Technology Specialist (MCTS) certification. I'm a highly motivated self-starter with an attitude for learning new skills and utilizing that in my work.


--Amit Kumar
You can reach me at:
Facebook | Linkedin | Twitter | Google+

Comments and Discussions

 
Questionfailed with mysql database Pin
Member 120962759-Jun-16 19:26
Member 120962759-Jun-16 19:26 
QuestionHello! Pin
hannan44030-May-16 1:32
hannan44030-May-16 1:32 
QuestionProject fails to load in Visual Studio 2013 and 2015 Pin
Member 119164252-Sep-15 11:32
Member 119164252-Sep-15 11:32 
QuestionReally nice article Pin
Saikumar Guptha31-Jul-14 22:22
professionalSaikumar Guptha31-Jul-14 22:22 
AnswerRe: Really nice article Pin
_Amy31-Jul-14 22:26
professional_Amy31-Jul-14 22:26 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun3-Jul-14 1:51
Humayun Kabir Mamun3-Jul-14 1:51 
GeneralRe: My vote of 5 Pin
_Amy31-Jul-14 22:26
professional_Amy31-Jul-14 22:26 
QuestionIts Urgent For me please help me Pin
Member 98223495-Mar-14 20:29
Member 98223495-Mar-14 20:29 
QuestionUser base Privileges Pin
Syed Mubashir Hussain Pirzada20-Feb-14 0:02
Syed Mubashir Hussain Pirzada20-Feb-14 0:02 
Questionhow to show subsubmenu relationship in this? Pin
Torakami10-Dec-13 18:49
Torakami10-Dec-13 18:49 
GeneralMy vote of 5 Pin
thardes216-Sep-13 23:49
thardes216-Sep-13 23:49 
GeneralNot Get ChildMenu (From third Level) Pin
nik kamble8-Apr-13 19:09
nik kamble8-Apr-13 19:09 
GeneralRe: This code work fine but up to three level Submenu Pin
_Amy8-Apr-13 19:16
professional_Amy8-Apr-13 19:16 
GeneralRe: This code work fine but up to three level Submenu Pin
nik kamble8-Apr-13 19:20
nik kamble8-Apr-13 19:20 
_Amy i can't get you.
can you explain this.
GeneralRe: This code work fine but up to three level Submenu Pin
nik kamble8-Apr-13 19:29
nik kamble8-Apr-13 19:29 
GeneralRe: This code work fine but up to three level Submenu Pin
_Amy8-Apr-13 19:34
professional_Amy8-Apr-13 19:34 
Questionbut how to get menuitem id on click of menu Pin
Ostwal Aarti13-Mar-13 0:55
Ostwal Aarti13-Mar-13 0:55 
AnswerRe: but how to get menuitem id on click of menu Pin
_Amy13-Mar-13 1:15
professional_Amy13-Mar-13 1:15 
GeneralRe: but how to get menuitem id on click of menu Pin
nik kamble8-Apr-13 19:13
nik kamble8-Apr-13 19:13 
QuestionAm Not Getting Child Menus Pin
HiReka29-Jan-13 18:48
HiReka29-Jan-13 18:48 
Questiongenerating menu based on user privilige Pin
sraws5-Nov-12 1:35
sraws5-Nov-12 1:35 
AnswerRe: generating menu based on user privilige Pin
_Amy5-Nov-12 17:16
professional_Amy5-Nov-12 17:16 
General+5 Pin
Raje_4-Oct-12 22:38
Raje_4-Oct-12 22:38 
GeneralRe: +5 Pin
_Amy4-Oct-12 23:09
professional_Amy4-Oct-12 23:09 

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.