jQuery UI Autocomplete Widget Using ASP.Net Generic Handler

Objective: Our main purpose is to suggest data in a TextBox when a user enters only some text into it. This data will be fetched using a table stored in the SQL Database. So, we will do some ADO.NET operations.

Solution: We can do this using a jQuery UI Autocomplete Widget. We will use a generic handler to fetch data from the database. Let's have a look in action.

Step 1

Create an Empty ASP.NET web application in Visual Studio and name it whatever you like. I named it "jQueryAutocompleteWidgetWithASP".

Step 2

We need to download the jQuery UI files from its official website www.jqueryui.com/download. Visit the website and select the Autocomplete checkbox and go to the bottom of the page and download the files. Extract the downloaded file and copy the images folder and the jquery-ui.js and jqueryui.css files to your ASP.NET web application.

Step 3

Download the jQuery file from www.jquery.com and also copy it to your solution.

An alternative to the preceding 2 steps is to just download the files from the NuGet package manager as shown below. I am using this method in my project.

Step 4

Create a database with the following script in the SQL Server.

Code Snippet

CREATE DATABASE SAMPLEDB

USE SAMPLEDB

CREATE TABLE [dbo].[Employee] (
    [Name]        NVARCHAR (50) NULL,
    [Designation] NVARCHAR (50) NULL
);

Code Snippet

INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Mark', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Amalie', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Boston', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Cathie', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Donaldo', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Easter', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Faraday', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Goldo', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Harmoine', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Instern', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Jane', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Kalis', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Lister', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Nicholas', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Peter', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Mark Hastings', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Amalie Loren', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Boston Cristopher', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Cathie Crew', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Donaldo Duck', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Easter Caster', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Faraday Joseph', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Goldo Jene', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Harmoine Creanjure', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Instern Calis', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Jane Austen', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Kalis Cris', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Lister Lane', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Nicholas Nistola', N'Software Developer')
INSERT INTO [dbo].[Employee] ([Name], [Designation]) VALUES (N'Peter Parker', N'Software Developer')

Code Snippet

CREATE procedure GetData
@QueryString nvarchar(50)
As
Begin
    Select Name from Employee where Name like '%' + @QueryString + '%'
End

Step 5

Add a Connection String to the preceding created database using the following code. Put this code in the web.config file of your project.

Code Snippet

<?xml version="1.0"?>
<configuration>
 <system.web>
   <compilation debug="true" targetFramework="4.5" />
   <httpRuntime targetFramework="4.5" />
 </system.web>
 <connectionStrings>
   <add connectionString="Data Source=.;Initial Catalog=SampleDB;Integrated Security=SSPI" providerName="System.Data.SqlClient" name="DB"/>
 </connectionStrings>
</configuration>

Step 6

Add a Generic Handler to your project and name it "employeeHandler".

Step 7

Add the following code to the "employeeHandler.ashx.cs".

Code Snippet

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;

namespace jQueryAutocompleteWidgetWithASP
{
    public class employeeHandler : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            string QueryString = context.Request["term"];
            List<string> listEmployee = new List<string>();
            string CS = ConfigurationManager.ConnectionStrings["DB"].ConnectionString;
            using (SqlConnection con = new SqlConnection(CS))
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("GetData", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter()
                {
                    ParameterName = "@QueryString",
                    Value = QueryString
                });

                SqlDataReader DR = cmd.ExecuteReader();
                while (DR.Read())
                {
                    listEmployee.Add(DR["Name"].ToString());
                }
            }
            JavaScriptSerializer JS = new JavaScriptSerializer();
            context.Response.Write(JS.Serialize(listEmployee));
        }
        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

Step 8

Add a webform to your project and name it "Demo.aspx".

Step 9

Add the following code to the HTML source of the preceding added webform.

Code Snippet

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Demo.aspx.cs" Inherits="jQueryAutocompleteWidgetWithASP.Demo" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <link href="Content/themes/base/all.css" rel="stylesheet" />
    <script src="Scripts/jquery-2.1.4.js"></script>
    <script src="Scripts/jquery-ui-1.11.4.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $('#txtName').autocomplete({
                source: 'employeeHandler.ashx'
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
        Employee Name :
        <asp:TextBox ID="txtName" runat="server">
        </asp:TextBox>

    </form>
</body>
</html>

Step 10

Finally Run the project by pressing the F5 Key. You will see the following result.

The following is the description of the SQL Script:

1. We simply create a table with some sample data.
2. Create a Stored Procedure to retrieve names from the table and we are using "@QueryString" as the parameter to filter the data. Also we used "%" that is a wildcard of SQL. It fetches all the names that meets the conditions.

Description of Web.config: We added a connection string to this file so that it will be easy for us to connect to our database.

The following describes the Generic Handler code:

1. In the beginning we will add the namespaces to obtain classes. We have added four additional namespaces. Three are for ADO.NET connectivity and the last one is for serialization.
2. We use the context parameter of the ProcessRequest function. We defined a key for it as "term" and save it in a string variable. Remember, use the key "term" because our jQuery Widget will recognise this only. This is specified in the API documentation of the widget on api.jqueryui.com.
3. A simple List object is created so that we can store the list of names retrieved from database.
4. A simple ADO.NET code is used to read the data.
5. Finally, the JavaScriptSerializer class object is created and context will write the response to List that we created in Step 3.

The following describes the HTML page with jQuery:

1. Use a TextBox on the page.
2. Include the JavaScript files and CSS files in the page.
3. Add a script tag.
4. Use the jQuery DOM Ready function to invoke an anonymous function and inside it, we are using the autocomplete method of the jQuery Id selector element.
5. The "source" JSON object of the autocomplete method executes the handler that we created and the QueryString is passed to the handler.
6. Using QueryString as the parameter, the Stored Procedure executes and gives us the list of names.

If you like my tutorial, please comment.

Also follow me on Facebook, Twitter and LinkedIn. Also Like my Facebook page, www.fb.com/the technewsinform