Generating menu from database according to user privilege

• Download source code - 153.8 KB

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).  

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;
}
Colourised in 16ms

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.

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;
      }
}
Colourised in 17ms

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.

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;
    }
}
Colourised in 19ms

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.

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:

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
Colourised in 19ms

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)) 
Colourised in 1ms

The output    

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

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.