Saturday, 14 October 2017

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
RoleMaster and UserMaster Table
MenuMaster
MenuMaster Table
RoleMenuMapping
RoleMenuMapping Table

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

--Role table creation
create table tbl_RoleMaster
(
RoleId int primary key identity,
Name varchar(50),
Active bit,
)
go
insert into tbl_RoleMaster values
('Normal User',1),
('Admin',1),
('HR',1)
--User Table Creation
go
create table tbl_UserMaster
(
Id int primary key identity,
UserId varchar(20), -- actual user id for login 
Name varchar(100),
RoleId int,
Active bit
)
go
insert into tbl_UserMaster values
('10001','Rahul',1,1),
('10002','John',2,1),
('10003','Mike',3,1)
--Menu Table Creation
go
create table tbl_MenuMaster
(
MenuId int primary key identity,
MenuName varchar(50),
MenuUrl varchar(200),
MenuParentId int,
Active bit
)
go
insert into tbl_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 primary key identity,
RoleId int,
MenuId int,
Active bit
)
go
--Normal User
insert into tbl_RoleMenuMapping values(1,1,1)
insert into tbl_RoleMenuMapping values(1,2,1)
insert into tbl_RoleMenuMapping values(1,4,1)
insert into tbl_RoleMenuMapping values(1,6,1)
insert into tbl_RoleMenuMapping values(1,7,1)
--Admin
insert into tbl_RoleMenuMapping values(2,1,1)
insert into tbl_RoleMenuMapping values(2,2,1)
insert into tbl_RoleMenuMapping values(2,4,1)
insert into tbl_RoleMenuMapping values(2,5,1)
insert into tbl_RoleMenuMapping values(2,6,1)
insert into tbl_RoleMenuMapping values(2,7,1)
--Hr
insert into tbl_RoleMenuMapping values(3,1,1)
insert into tbl_RoleMenuMapping values(3,2,1)
insert into tbl_RoleMenuMapping values(3,3,1)
insert into tbl_RoleMenuMapping values(3,4,1)
insert into tbl_RoleMenuMapping values(3,6,1)
insert into tbl_RoleMenuMapping values(3,7,1)

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

create proc usp_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 join tbl_RoleMenuMapping rm on um.RoleID=rm.RoleID                                                              
                inner join tbl_MenuMaster mm on mm.MenuId=rm.MenuID                                                   
                inner join tbl_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 FileNewProject. 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 .

Creating asp.net mvc 5 application

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

Selecting asp.net mvc 5 empty template

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

Asp.net mvc 5 empty folder structure

Adding model

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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace WebApplication1.Models
{
    public class Menu
    {
        public int MID;
        public string MenuName;
        public string MenuURL;
        public int MenuParentID;
    }
}

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.

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using Dapper;
using WebApplication1.Models;
using System.Configuration;
using System.Data;

namespace WebApplication1.Repository
{
    public class MenuData
    {
        public static IList<Menu> GetMenus(string UserId)
        {
            /* using ado.net code */
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString()))
            {
                List<Menu> menuList = new List<Menu>();
                SqlCommand cmd = new SqlCommand("usp_GetMenuData", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@UserId", UserId);
                con.Open();
                SqlDataReader sdr = cmd.ExecuteReader();
                while (sdr.Read())
                {
                    Menu menu = new Menu();
                    menu.MID = Convert.ToInt32(sdr["MID"].ToString());
                    menu.MenuName = sdr["MenuName"].ToString();
                    menu.MenuURL = sdr["MenuURL"].ToString();
                    menu.MenuParentID = Convert.ToInt32(sdr["MenuParentID"].ToString());
                    menuList.Add(menu);
                }
                return menuList;
            }

            /* use can also use dapper orm
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString()))
            {
                try
                {
                    var para = new DynamicParameters();
                    para.Add("@UserId", UserId);
                    var MenuList = con.Query<Menu>("usp_GetMenuData", para, null, true, 0, CommandType.StoredProcedure);
                    return MenuList.ToList();
                }
                catch
                {
                    return null;
                }
            }*/
        }
    }
}

Adding Controller

Next step is to add controller to application. Go to controller folder and add new empty controller named as Account controller. Replace all code with below code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication1.Models;
using WebApplication1.Repository;

namespace WebApplication1.Controllers
{
    public class AccountController : Controller
    {
        public virtual PartialViewResult Menu()
        {
            IEnumerable<Menu> Menu = null;

            if (Session["_Menu"] != null)
            {
                Menu = (IEnumerable<Menu>)Session["_Menu"];
            }
            else
            {
                Menu = MenuData.GetMenus("10002");// pass employee id here
                Session["_Menu"] = Menu;
            }
            return PartialView("_Menu", Menu);
        }
    }
}

Now add one more controller. Go to controller folder and add new empty controller named as Home controller. Following code will be generated for home controller class.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace WebApplication1.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/
        public ActionResult Index()
        {
            return View();
        }
      }
}

Adding View

Now, right click on home controller index action method, add new view and name Index. Select Empty template and uncheck Use layout page and click on Add as shown below.

Addling index view

Now go to Views / Home folder, open Index.cshtml file and replace all code with below code.

Index.cshtml Code:
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    <div style="height: 150px; background-color: #eeeeee;">
       <h2>This is Home index view.</h2>
    </div>
</body>
</html>

Now add Shared folder inside Views folder.
Now right click on shared folder, go to add view and give view name _Layout and select Empty template and uncheck Use layout page and click on Add.
Now add one more view named as _Menu inside Views folder.
Replace all code provided below.

_Layout.cshtml Code:
<!DOCTYPE html>
<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Layout</title>
</head>
<body>
    <div style="height: 100px; background-color: olive; color:black;">
        <h2>This is inside layout page.</h2>
    </div>

    @{Html.RenderAction("Menu", "Account", new { area = "" });}

    <div>
        @RenderBody()
    </div>
</body>
</html>
_Menu.cshtml Code:
@model IEnumerable<WebApplication1.Models.Menu>

<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/4.7.0/css/font-awesome.min.css">

<div class="navigation">
    @foreach (var m in Model)
    {
        if (m.MenuParentID == 0)
        {
            var submenu = Model.Where(n => n.MenuParentID == m.MID).ToList();

            if (submenu != null && submenu.Count > 0)
            {
                <div class="dropdown">
                    <button class="dropbtn">
                        @m.MenuName
                        <i class="fa fa-caret-down"></i>
                    </button>
                    <div class="dropdown-content">
                        @foreach (var e in submenu)
                        {
                            <a href="@e.MenuURL">@e.MenuName </a>
                        }
                    </div>
                </div>
            }
            else
            {
                //if parent menu does not have child menu
                <a href="@m.MenuURL"><span style="font-size: 15px; color: #ffffff">@m.MenuName</span></a>
            }
        }
    }
</div>

<style>
    .navigation {
        overflow: hidden;
        background-color: #333;
        font-family: Arial;
    }

        .navigation a {
            float: left;
            font-size: 16px;
            color: white;
            text-align: center;
            padding: 14px 16px;
            text-decoration: none;
        }

    .dropdown {
        float: left;
        overflow: hidden;
    }

        .dropdown .dropbtn {
            font-size: 16px;
            border: none;
            outline: none;
            color: white;
            padding: 14px 16px;
            background-color: inherit;
        }

        .navigation a:hover, .dropdown:hover .dropbtn {
            background-color: red;
        }

    .dropdown-content {
        display: none;
        position: absolute;
        background-color: #f9f9f9;
        min-width: 160px;
        box-shadow: 0px 8px 16px 0px rgba(0,0,0,0.2);
        z-index: 1;
    }

        .dropdown-content a {
            float: none;
            color: black;
            padding: 12px 16px;
            text-decoration: none;
            display: block;
            text-align: left;
        }

            .dropdown-content a:hover {
                background-color: #ddd;
            }

    .dropdown:hover .dropdown-content {
        display: block;
    }
</style>

Now, right click on Views Folder and add new view and give its name _ViewStart and select Empty template and uncheck Use layout page and click on Add. Replace all code with below code.

_ViewStart.cshtml Code:
@{
    Layout = "~/Views/Shared/_Layout.cshtml";
}

Complete folder structure is shown below.

Asp.net mvc complete folder structure

Write your database connection string in web.config file and run your application and output is shown below.

Final menu output

Now you can change employee id from Account Controller from GetMenus method to get menu data of respected user.

DOWNLOAD SOURCE CODE
Share:

0 comments:

Post a Comment

Email Subscription

Subscribe to our newsletter to get the latest articles directly into your inbox

Blog Archive

BUY FROM AMAZON