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

jQueryUI Tooltip Widget with ASP.NET Web Service

jQueryUI Tooltip Widget with ASP.NET Web Service

Description: In this article, we will show some tooltips on textboxes and the tooltip text will come through our SQL Database tables for each textbox. We will achieve this using the following agenda.

  • First we will create a table in our database which contains tooltip text to be displayed against each control that we will use in our web page.
  • We will create an auto-implemented class file that have auto implemented properties that corresponds to our database table columns.
  • Now, we will create an ASP.NET web service, that retrieve data from database. This retrieved data will be used to display tooltip for each control in JSON format.
  • At last, we will design our web page body with some sample textboxes with specific ID’s and these ID’s should be same as that of our database table column which contains control name.
  • Now, we use jQuery code to call our web service which contains retreived data and show it as tooltip using tooltip widget property of jQueryUI.

Thinking it to be very difficult, hold on…first read the article then you will come to know, how easy it is.

You can download source code here..

Solution:

Step 1: Create Database with some sample data. You can use your own data or use the below script.

CREATE TABLE dbo.TooltipEmployees(controlName NVARCHAR(100) NULL,
                                  tooltipData NVARCHAR(100) NULL);
 
INSERT INTO dbo.TooltipEmployees(controlName,
                                 tooltipData)
VALUES(N'Name', N'Please enter as in Id proof');
 
INSERT INTO dbo.TooltipEmployees(controlName,
                                 tooltipData)
VALUES(N'Salary', N'Enter as per payslip');
 
INSERT INTO dbo.TooltipEmployees(controlName,
                                 tooltipData)
VALUES(N'EmailId', N'Enter official email');
 
INSERT INTO dbo.TooltipEmployees(controlName,
                                 tooltipData)
VALUES(N'Designation', N'Enter as per payslip');
 
CREATE PROC spGetTooltipData
       @controlName NVARCHAR(100)
AS
BEGIN
SELECT te.*
FROM dbo.TooltipEmployees te
WHERE te.controlName=@controlName;
END;

Our database table will look like this.

Step 2: Create an Empty ASP.NET Web application.

Step 3: Add a class file.

Step 4: Replace with the following code.

namespace Asp.netTooltipUsingJqueryUI
{
    public class Tooltip
    {
        public string controlName { getset; }
        public string tooltipData { getset; }
    }
}

Step 5: Add a new web service file.

Step 6: Replace with the following code.

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
 
namespace Asp.netTooltipUsingJqueryUI
{
 
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    [System.Web.Script.Services.ScriptService]
    public class TooltipDataService : System.Web.Services.WebService
    {
        [WebMethod]
        public void GetTooltipData(string controlName)
        {
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
 
            Tooltip toolTip = new Tooltip();
 
            using (SqlConnection con = new SqlConnection(CS))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("spGetTooltipData", con);
                cmd.CommandType = CommandType.StoredProcedure;
 
                cmd.Parameters.AddWithValue("@controlName", controlName);
 
                SqlDataReader dr = cmd.ExecuteReader();
 
                while(dr.Read())
                {
                    toolTip.controlName = dr["controlName"].ToString();
                    toolTip.tooltipData = dr["tooltipData"].ToString();
                }
 
                JavaScriptSerializer serializer = new JavaScriptSerializer();
                Context.Response.Write(serializer.Serialize(toolTip));
            }
        }
    }
}

Note: Go to Web.config file and add the connection strings to your database.

  <connectionStrings>
    <add connectionString="Data Source=.;Initial Catalog=DemoDB;Integrated Security=True" name="DBCS" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Explanation of Web Service code:

  • I have created a method that needs a parameter. This parameter actually is the control name which will check with the control name in our database table.
  • We have created a strored procedure that returns data based on this parameter. We are just using simple ADO.NET code to retreive data.
  • The retreived data is now assigned to auto-implemented properties of class file.
  • This data is now changed to JSON format using JavaScriptSerializer class.
  • The connection string is used to connect to database. The (.) in data source refers to local connection.

Step 7: Now let’s check our webservice, if it is returning correct data from database or not. Press Ctrl + F5.

Now you can see that our web service is returning correct data in JSON format.

Step 8: Add a new webform to implement our front end code.

Step 9: Go to source mode of webform and add the following code in the body tag.

            <table border="1">
                <tr>
                    <td><b>Name: </b></td>
                    <td>
                        <input type="text" title="" id="Name" class="tooltips" /></td>
                </tr>
 
                <tr>
                    <td><b>Salary: </b></td>
                    <td>
                        <input type="text" title="" id="Salary" class="tooltips" /></td>
                </tr>
 
                <tr>
                    <td><b>Email Id: </b></td>
                    <td>
                        <input type="text" title="" id="EmailId" class="tooltips" /></td>
                </tr>
 
                <tr>
                    <td><b>Designation: </b></td>
                    <td>
                        <input type="text" title="" id="Designation" class="tooltips" /></td>
                </tr>
            </table>

Note: The id of each of the HTML element should be same as that you have stored in your database.

Step 10: To add the jQuery references, visit my article here..and follow step 5 and step 9 to download jQueryUI files and add their references or you can just download the code file attached with this article.

Step 11: Add the following jQuery code to the head section.

    <script type="text/javascript">
        $(document).ready(function () {
 
            $('.tooltips').tooltip({
                content: getTooltipText
            });
 
            function getTooltipText() {
                var rtnValue = '';
                $.ajax({
                    method: "POST",
                    dataType: "json",
                    url: "TooltipDataService.asmx/GetTooltipData",
                    data: { controlName: $(this).attr('id') },
                    async: false,
                    success: function (data) {
                        rtnValue = data.tooltipData;
                    }
                });
                return rtnValue;
            }
        });
    </script>

Explanation of jQuery code:

  • We have created a function to make the AJAX call to web service. We will send the controlName parameter to web service using the id property of our input elements in the body tag.
  • Method is POST as firstly we will call the service, then sends data to it and again retreive database from it.
  • Datatype is JSON as we are returning data from services in JSON format using JavascriptSerializer class.
  • The call to service should be synchronous as if it is asynchronous, then the call to service will be once but we need continuous connection with service.
  • if the ajax call is success, we have returned the data back to control using rtnValue variable.
  • Finally, we are using jQuery class selector, and implemented tooltip widget and the content of that widget comes from the function that made the AJAX call and returned data.

Step 12: Now save all changes and press Ctrl + F5 and you will see this.

Have you ever wondered how easy to do it using jQuery.

Hope you enjoyed this and if you like please visit my blog debugsolutions and subscribe for email alerts and share as much as possible.