Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>
Idioma: En

Bind menu and sub-menu dynamically in mvc from database

In this article, I am going to explain you binding menu and sub-menu dynamically from database in asp.net mvc with razor view engine. I will be using visual studio 2013 edition and sql server 2008. Below is the step by step tutorial.

Creating Database Table

We will be using 4 following tables.
RoleMaster
UserMaster
MenuMaster
RoleMenuMapping

RoleMaster and UserMaster
MenuMaster
RoleMenuMapping

Below is the script to create all the tables and insert some data into it.

--Role table creation
create tabletbl_RoleMaster
(
RoleId int primarykey identity,
Name varchar(50),
Active bit,
)
go
insert intotbl_RoleMaster values
('Normal User',1),
('Admin',1),
('HR',1)
--User Table Creation
go
create tabletbl_UserMaster
(
Id int primarykey identity,
UserId varchar(20), -- actual user id for login 
Name varchar(100),
RoleId int,
Active bit
)
go
insert intotbl_UserMaster values
('10001','Rahul',1,1),
('10002','John',2,1),
('10003','Mike',3,1)
--Menu Table Creation
go
create tabletbl_MenuMaster
(
MenuId int primarykey identity,
MenuName varchar(50),
MenuUrl varchar(200),
MenuParentId int,
Active bit
)
go
insert intotbl_MenuMaster values
('Pay Slip','#',0,1),
('User DashBoard','#',0,1),
('Modify Pay Details','HR/ModifyPayDetails',1,1),
('Download Pay Slip','Home/DownloadPay',1,1),
('User Maintenance','Admin/UserMaintenance',2,1),
('View User Details','Home/User Details',2,1),
('Bank Details','Home/BankDetails',0,1)
--RoleMenuMapping Table Creation
create table  tbl_RoleMenuMapping
(
Id int primarykey identity,
RoleId int,
MenuId int,
Active bit
)
go
--Normal User
insert intotbl_RoleMenuMapping values(1,1,1)
insert intotbl_RoleMenuMapping values(1,2,1)
insert intotbl_RoleMenuMapping values(1,4,1)
insert intotbl_RoleMenuMapping values(1,6,1)
insert intotbl_RoleMenuMapping values(1,7,1)
--Admin
insert intotbl_RoleMenuMapping values(2,1,1)
insert intotbl_RoleMenuMapping values(2,2,1)
insert intotbl_RoleMenuMapping values(2,4,1)
insert intotbl_RoleMenuMapping values(2,5,1)
insert intotbl_RoleMenuMapping values(2,6,1)
insert intotbl_RoleMenuMapping values(2,7,1)
--Hr
insert intotbl_RoleMenuMapping values(3,1,1)
insert intotbl_RoleMenuMapping values(3,2,1)
insert intotbl_RoleMenuMapping values(3,3,1)
insert intotbl_RoleMenuMapping values(3,4,1)
insert intotbl_RoleMenuMapping values(3,6,1)
insert intotbl_RoleMenuMapping values(3,7,1)

Now the next step is to create procedure. Below is the script to create procedure.

create procusp_GetMenuData
@UserId varchar(20)   --user id as input parameter
as
begin                                                                    
                select mm.MenuID MID, mm.MenuName,mm.MenuURL,mm.MenuParentID from
                tbl_UserMaster um                                                       
                inner jointbl_RoleMenuMapping rm on um.RoleID=rm.RoleID                                                              
                inner jointbl_MenuMaster mm on mm.MenuId=rm.MenuID                                                   
                inner jointbl_RoleMaster br on br.RoleId =rm.RoleID                                                 
                where um.UserId = @UserId  and rm.Active=1                -- add more active condition if required                                             
end

Creating ASP.NET MVC Empty Application

Now the next step is to create ASP.NET MVC empty application as shown below.
Go to File → New → Project. A new window will be open as shown below.
Now go to Web and select .NET Framework 4.5 and give project name and click on OK .

Now new window will open as shown below.
Now Select Empty Template, check on MVC checkbox and click on OK.

Now, a new project will be created as shown below.

Adding model

Go to Models folder and add new class file Menu.cs. Now replace all code with below code.

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Web;

namespaceWebApplication1.Models
{
    public class Menu
    {
        public intMID;
        public stringMenuName;
        public stringMenuURL;
        public intMenuParentID;
    }
}

Now add a new folder named Repository to the root directory of the application and add a new class file MenuData.cs into Repository folder and replace all code with below code.

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data.SqlClient;
usingSystem.Linq;
usingSystem.Web;
usingDapper;
usingWebApplication1.Models;
usingSystem.Configuration;
usingSystem.Data;

namespaceWebApplication1.Repository
{
    public class MenuData
    {
        public static IListMenu> GetMenus(string UserId)
        {
            /* using ado.net code */
            using(SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString()))
            {
                ListMenu

Related Articles



This post first appeared on ASPArticles, please read the originial post: here

Share the post

Bind menu and sub-menu dynamically in mvc from database

×

Subscribe to Asparticles

Get updates delivered right to your inbox!

Thank you for your subscription

×