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");
DataTable dtMenuItems = new DataTable();
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dtMenuItems);
cmd.Dispose();
sda.Dispose();
return dtMenuItems;
}
}
catch (Exception ex) {
}
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)
{
MenuItem newMenuItem = new MenuItem(row["Text"].ToString(), row["MenuID"].ToString());
menuBar.Items.Add(newMenuItem);
AddChildMenuItems(menuData, newMenuItem);
}
}
catch (Exception ex)
{
}
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);
AddChildMenuItems(menuData, newMenuItem);
}
}
catch (Exception ex)
{
}
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.
tblGroupPrivMst
: This table contains the information about the groups / roles and the privilege bit applicable for that role. tblMenuMst
: This table contains the menu description, menu id and navigate url fields.tblUserGrpMap
: This is a mapping table, which will take care about the relation of user to respective group. tblDesgMst
: This table contains the designation of the user under the website. 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
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
INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID=@Pos
END
SET @Pos=@Pos+1
SET @len=@len-1
END
INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT
ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
INSERT INTO #TMP SELECT * FROM tblMenuMst WHERE MenuID IN(SELECT DISTINCT
ParentID FROM #TMP WHERE ParentID NOT IN(SELECT MenuID FROM #TMP))
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
ENDColourised 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.