In this post I am explaining how to create menu dynamically from database with role wise permission.
We will create three tables for this first will be to manage Roles, second will be to manage Menu Items and third for role wise permission.
Following is the SQL script to create tables.
CREATE TABLE [dbo].[Role]
(
[RoleID] [int] IDENTITY(1,1) NOT NULL,
[Role] [varchar](100) NULL,
[Description] [varchar](8000) NULL,
[CreatedBy] [varchar](100),
[CreatedDate] [datetime] default getdate(),
[ModifiedBy] [varchar](100),
[ModifiedDate] [datetime] default getdate(),
[IsActive] [bit] default 1
)
CREATE TABLE [dbo].[Menus]
(
[MenuItemID] [int] IDENTITY(1,1) NOT NULL,
[MenuItem] [varchar](50) NULL,
[DisplayOrder] [int] NULL,
[ParentID] [int] NULL,
[Status] [bit] NULL,
[PageURL] [varchar](255) NULL
)
CREATE TABLE [dbo].[RolePrivileges]
(
[PrivilegeID] [int] IDENTITY(1,1) NOT NULL,
[RoleID] [int] NULL,
[MenuItemID] [int] NULL,
[View] [bit] NULL,
[Add] [bit] NULL,
[Edit] [bit] NULL,
[Delete] [bit] NULL,
[Status] [bit] NULL
)
Then Create a Stored Procedure named SPMenuList to get Menu Items Role wise
CREATE PROCEDURE [dbo].[SPMenuList]
-- Add the parameters for the stored procedure here
@RoleId int = 0
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select r.PrivilegeID,m.MenuItemID,r.RoleID,m.MenuItem,m.DisplayOrder,m.ParentID,m.PageURL,r.[Add],
r.edit,r.[delete],r.[view] from RolePrivileges as r
join Menus as m on r.MenuItemID=m.MenuItemID
where r.RoleID=@RoleID and r.Status = 1 and m.Status = 1
END
I am using LINQ and EDMX to create the menu.
try
{
string strPath = "~" + Request.Url.AbsolutePath;
List<string> strPages = new List<string>();
strPages.Add("~/login.aspx");
strPages.Add("~/notauthorized.aspx");
strPages.Add("~/register.aspx");
int RoleID = Convert.ToInt32(Session["RoleID"].ToString());
List<SPMenuList_Result> MenuList =
db.SPMenuList(RoleID).OrderBy(m=>m.DisplayOrder).ToList(); // Get all menu for the role
#region Authorization
List<string> strAuthorizedPages = new List<string>();
strAuthorizedPages = MenuList.Select(p => p.PageURL).ToList(); // Get authorized pages list
if (!strAuthorizedPages.Contains(strPath) && !strPages.Contains(strPath.ToLower()))
{
Response.Redirect("~/NotAuthorized.aspx");
}
#endregion
if (!strPages.Contains(strPath.ToLower()))
{
#region Bind Menu
List<SPMenuList_Result> ParentMenu = MenuList.Where(m => m.ParentID == 0 &&
m.DisplayOrder != 0).OrderBy(m=>m.DisplayOrder).ToList();
foreach (var Item in ParentMenu)
{
MenuItem Menu = new MenuItem(Item.MenuItem, "", "", Item.PageURL);
foreach (var item in MenuList)
{
if (Item.MenuItemID == item.ParentID)
{
MenuItem menu = new MenuItem(item.MenuItem, item.ParentID.ToString(), "",
item.PageURL);
Menu.ChildItems.Add(menu);
try
{
if (item.PageURL.ToLower() == strPath.ToLower())
{
Menu.Selected = true;
}
}
catch (NullReferenceException) { }
}
}
NavigationMenu.Items.Add(Menu);
}
#endregion
}
}
Add the following code to your CSS file if you want to show the selected menu.
.selected
{
background-color:#103D5F;
}
Put Comment if you have any query...
No comments:
Post a Comment