Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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
jQuery dynamic menu from database in asp

The menu should look as shown below
jquery menu from database

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>

jQuery tutorial for beginners

9 comments:

  1. I have a question about this tutorial. It’s nice to make a menu dynamic, but how can I create an event for example a “Click” or “Mouse over” to navigate a linked webpage?

    If I get an interview question to explain what’s better, a dynamic menu or static, what is the best answer?

    ReplyDelete
  2. You can do it two ways either by appending the 'a' attribute in the script and adding the attr(href) to the specified url but the issue could be is that you cannot set that different for every child li created so you might need to modify the script so that you can set it different for every child li element.

    Another way which is preferred is that adding the link to the database so create a field in DB, property in class and set that property to the attr(href) in jquery. This approach can be used with this script written here.

    what’s better, a dynamic menu or static?

    I think static is better you do not want to load even the menu for every page from DB unless it is required. Dynamic can be used where we have need for dynamic menu or items are changing regularly. but for that you need to have different database setup and different code. Like if you want to show your latest products then dynamic is useful.

    P.S: It is just my inference but you can use your own imagination too.

    ReplyDelete
  3. Thanks the tutorial is very helpful, please how can i convert the menu to a horizontal view, please i will appreciate your feedback. thank You.

    ReplyDelete
  4. Will these examples work with newer version of jquery? Where can i find the version used by this website?

    ReplyDelete
  5. Dear Sir
    I need help for this part of code
    --( return list.Where(x => x.ParentId == parent).Select(x => new Menu)
    This is my code in VB.NET
    --( Return list.Where(Function(x) x.ParentId = parent)
    I get this error (Nullable object must have a value)
    How use Nothing in this function?

    ReplyDelete
  6. Dear Sir
    I need help for this part of code
    --( return list.Where(x => x.ParentId == parent).Select(x => new Menu)
    This is my code in VB.NET
    --( Return list.Where(Function(x) x.ParentId = parent)
    I get this error (Nullable object must have a value)
    How use Nothing in this function?

    ReplyDelete
  7. I am facing the error

    Nullable object must have a value.

    Public Function GetMenuTree(ByVal list As List(Of Menu), ByVal Parent As Integer?) As List(Of Menu)
    Return list.Where(Function(x) x.ParentId = Parent).[Select](Function(x) New Menu With {

    ReplyDelete
  8. Great Work sir! your tutorial give me helping hand but i have some confusion in JQuery, can you give me the jquery script or can you please tell me how can i implement jQuery in menu form.
    Thanks!!

    ReplyDelete
  9. Hello guys, I have an error in this part > $('#menu').menu();

    error: "Uncaught TypeError: $(...).menu is not a function"

    ReplyDelete

It would be great if you can help share these free resources