jQueryUI Menu Widget using DB

Description – In this demo, we will create a menu using jQuery UI Menu Widget and all of the values will be populated through a Database table.

Step 1: Open SQL Server Management Studio and Create a Database named DemoDB and use the following script to create the table, insert sample data and also to create the Stored procedure which will be used in Visual Studio to Retrieve Data.

CREATE TABLE [dbo].[jquerymenu] (
    [ID]       INT           IDENTITY (1, 1) NOT NULL,
    [Text]     NVARCHAR (50) NULL,
    [parentId] INT           NULL,
    [isActive] BIT           NULL
);
 
SET IDENTITY_INSERT [dbo].[jquerymenu] ON
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (1, N'USA', NULL, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (2, N'India', NULL, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (3, N'UK', NULL, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (4, N'Australia', NULL, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (5, N'Virginia', 1, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (6, N'Maryland', 1, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (7, N'AP', 2, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (8, N'MP', 2, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (9, N'Karnataka', 2, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (10, N'Bangalore', 9, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (11, N'Mangalore', 9, 1)
INSERT INTO [dbo].[jquerymenu] ([ID], [Text], [parentId], [isActive]) VALUES (12, N'Mysore', 9, 0)
SET IDENTITY_INSERT [dbo].[jquerymenu] OFF
 
Create Proc spGetMenuData
as
Begin
    Select * from jquerymenu
End

Step 2: Open Visual Studio and follow the below mentioned steps.

Create project
Create a new Project
Add Class File
Add a class file
Add Webform
Add a Webform
Add Handler
Add an ASP.NET Generic Handler to Hold the request

Step 3: Add a Connection String for Connection to Database in Web.Config file.

<connectionStrings>
    <add name="DBCS" connectionString="server=.;database=DemoDB;integrated security=SSPI" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Step 4: Create Auto-populated properties in the Class file created in Step 2.

using System.Collections.Generic;
 
namespace jQueryUIMenu
{
    public class Menu
    {
        public int ID { get; set; }
        public string Text { get; set; }
        public int? parentId { get; set; }
        public bool isActive { get; set; }
        public List<Menu> List { get; set; }
    }
}

Step 5: Add the following Code to the Generic Handler which will retrieve data from Database.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Configuration;
 
namespace jQueryUIMenu
{
    public class MenuHandler : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            var cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            var list = new List<Menu>();
 
            using (var con = new SqlConnection(cs))
            {
                var cmd = new SqlCommand("spGetMenuData", con) { CommandType = CommandType.StoredProcedure };
                con.Open();
                var dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    var menu = new Menu
                    {
                        ID = Convert.ToInt32(dr["ID"]),
                        Text = dr["Text"].ToString(),
                        parentId = dr["parentId"] != DBNull.Value ? Convert.ToInt32(dr["parentId"]) : (int?)null,
                        isActive = Convert.ToBoolean(dr["isActive"])
                    };
                    list.Add(menu);
                }
            }
            var mainList = GetMenuTree(list, null);
 
            var js = new JavaScriptSerializer();
            context.Response.Write(js.Serialize(mainList));
 
        }
 
        private List<Menu> GetMenuTree(List<Menu> list, int? parent)
        {
            return list.Where(x => x.parentId == parent).Select(x => new Menu
            {
                ID = x.ID,
                Text = x.Text,
                parentId = x.parentId,
                isActive = x.isActive,
                List = GetMenuTree(list, x.ID)
            }).ToList();
        }
 
        public bool IsReusable => false;
    }
}

Step 6: Add the following code to the Body Tag of Webform1.aspx.

<body>
    <form id="form1" runat="server">
        <div style="width150px">
            <ul id="menu"></ul>
        </div>
    </form>
</body>

Step 7: Add jQuery UI core files as shown below.

Add Nuget
Add jQuery UI Core files from Nuget Package Manager

Step 8: Add the following jQuery Code to the Head Tag of Webform1.aspx.

<head runat="server">
    <title>Dynamic Menu</title>
    <script src="Scripts/jquery-1.6.4-vsdoc.js"></script>
    <script src="Scripts/jquery-1.6.4.js"></script>
    <script src="Scripts/jquery-ui-1.11.4.js"></script>
    <link href="Content/themes/base/all.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.Text + '</li>');
                    if (!this.isActive) {
                        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>

Step 9: Press Ctrl + F5 together to View the result in Browser. You will see a menu having values populated from Database.

Final

Hope you enjoyed the Example. Please share it if you think that I can be useful to others. Also comment in case of any Queries.

Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s