jQuery dynamic menu from database in asp.net

Suggested Videos
Part 83 - jquery ui progress bar
Part 84 - asp.net multiple file upload with progress bar
Part 85 - jquery menu widget



In this video we will discuss, how to build a jQuery menu using data from a database table.



We will be using the following database table tblMenu for this demo


The menu should look as shown below


Step 1 : Create SQL Server table and insert menu data

Create table tblMenu

(

    Id int primary key identity,

    MenuText nvarchar(30),

    ParentId int foreign key references tblMenu(Id),

    Active bit

)

Go

Insert into tblMenu values('USA', NULL, 1)

Insert into tblMenu values('India', NULL, 1)

Insert into tblMenu values('UK', NULL, 1)

Insert into tblMenu values('Australia', NULL, 1)

Insert into tblMenu values('Virginia', 1, 1)

Insert into tblMenu values('Maryland', 1, 1)

Insert into tblMenu values('AP', 2, 1)

Insert into tblMenu values('MP', 2, 1)

Insert into tblMenu values('Karnataka', 2, 1)

Insert into tblMenu values('Bangalore', 9, 1)

Insert into tblMenu values('Mangalore', 9, 1)

Insert into tblMenu values('Mysore', 9, 0)

Go

Step 2 : Create a stored procedure to retrieve menu data

Create Proc spGetMenuData

as

Begin

    Select * from tblMenu

End

Step 3 : Create new asp.net web application project. Name it Demo. 

Step 4 : Include a connection string in the web.config file to your database.

<add name="DBCS"

      connectionString="server=.;database=SampleDB;integrated security=SSPI"/>

Step 5 : Add a class file to the project. Name it Menu.cs. Copy and paste the following code.

using System.Collections.Generic;

namespace Demo

{

    public class Menu

    {

        public int Id { get; set; }

        public string MenuText { get; set; }

        public int? ParentId { get; set; }

        public bool Active { get; set; }

        public List<Menu> List { get; set; }

    }

}

Step 6 : Add a new GenericHandler. Name it MenuHandler.ashx. Copy and paste the following code.

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.Script.Serialization;

namespace Demo

{

    public class MenuHandler : IHttpHandler

    {

        public void ProcessRequest(HttpContext context)

        {

            string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

            List<Menu> listMenu = new List<Menu>();

            using (SqlConnection con = new SqlConnection(cs))

            {

                SqlCommand cmd = new SqlCommand("spGetMenuData", con);

                cmd.CommandType = CommandType.StoredProcedure;

                con.Open();

                SqlDataReader rdr = cmd.ExecuteReader();

                while (rdr.Read())

                {

                    Menu menu = new Menu();

                    menu.Id = Convert.ToInt32(rdr["Id"]);

                    menu.MenuText = rdr["MenuText"].ToString();

                    menu.ParentId = rdr["ParentId"] != DBNull.Value

                        ? Convert.ToInt32(rdr["ParentId"]) : (int?)null;

                    menu.Active = Convert.ToBoolean(rdr["Active"]);

                    listMenu.Add(menu);

                }

            }

            List<Menu> menuTree = GetMenuTree(listMenu, null);

            JavaScriptSerializer js = new JavaScriptSerializer();

            context.Response.Write(js.Serialize(menuTree));

        }

        public List<Menu> GetMenuTree(List<Menu> list, int? parent)

        {

            return list.Where(x => x.ParentId == parent).Select(x => new Menu

            {

                Id = x.Id,

                MenuText = x.MenuText,

                ParentId = x.ParentId,

                Active = x.Active,

                List = GetMenuTree(list, x.Id)

            }).ToList();

        }

        public bool IsReusable

        {

            get

            {

                return false;

            }

        }

    }

}

Step 7 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code

<%@ Page Language="C#" AutoEventWireup="true"

    CodeBehind="WebForm1.aspx.cs" Inherits="Demo.WebForm1" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <script src="jquery-1.11.2.js"></script>

    <script src="jquery-ui.js"></script>

    <link href="jquery-ui.css" rel="stylesheet" />

    <script type="text/javascript">

        $(document).ready(function () {

            $.ajax({

                url: 'MenuHandler.ashx',

                method: 'get',

                dataType: 'json',

                success: function (data) {

                    buildMenu($('#menu'), data);

                    $('#menu').menu();

                }

            });

            function buildMenu(parent, items) {

                $.each(items, function () {

                    var li = $('<li>' + this.MenuText + '</li>');

                    if (!this.Active) {

                        li.addClass('ui-state-disabled');

                    }

                    li.appendTo(parent);

                    if (this.List && this.List.length > 0) {

                        var ul = $('<ul></ul>');

                        ul.appendTo(li);

                        buildMenu(ul, this.List);

                    }

                });

            }

        });

    </script>

</head>

<body style="font-family: Arial">

    <form id="form1" runat="server">

        <div style="width: 150px">

            <ul id="menu"></ul>

        </div>

    </form>

</body>

</html>