JQUERY UI AUTOCOMPLETE TEXTBOX WITH DATABASE IN ASP.NET C#,VB WITH EXAMPLE

Description: Have you ever noticed how the related suggestions highlights as you start typing in the Google search box? This is called AutoComplete. We can also implement this functionality in our asp.net web applications using jquery UI AutoComplete

The concept is simple. When you start typing in the TextBox, It fetches the matching strings from the database and display while typing e.g. when you type a single character 'm' in the TextBox then all the city names starting with 'm' will be fetched from the database  and displayed as a suggestion in textbox.

There are two methods to implement jquery UI Autocomplete:

1. By defining web method in web service to fetch data from database based on search term passed. 
2. By defining static web method on page to fetch data from database based on search term passed. Read the article

 In this article I am going to implement the first method.

Implementation: Let’s create a demo page to demonstrate autocomplete feature using jquery.

• First of all create a table using following script

CREATE TABLE tbCityMaster

(

                CityId   INT NOT NULL PRIMARY KEY IDENTITY(1,1),

                CityName           VARCHAR(100)

) 

• Now insert some dummy data into this tables using following script

 GO

INSERT INTO tbCityMaster (CityName)

VALUES

('Chandigarh'),

('Agra'),

('Bharatnagar'),

('Pathankot'),

('Amritsar'),

('Bhiwani'),

('Delhi'),

('Panchkula'),

('Gurgaon'),

('Baroda'),

('Palampur'),

('Bhuj')

('Bathinda'),

('Patiala'),

('Panipat');

• Now create a stored procedure to fetch top 15 city names based on the text we enter in autocomplete textbox.

GO

CREATE PROCEDURE spGetCityNames

(

                @SearchText VARCHAR(50)

)

AS

BEGIN

                SET NOCOUNT ON;

                SELECT TOP 15 CityName FROM tbCityMaster WHERE CityName LIKE @SearchText+'%'

END

• Now create connection string in web.config as:

<connectionStrings>

    <add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=MyDataBase;Integrated Security=True"/>

  </connectionStrings>

Note: Replace the Data Source and the Initial Catalog as per your application.

• Now add a web service in the project: for this go to Website menu -> Add new item -> select Web Service and name Cities.asmx

A web service will be added in your project and a new folder App_Code will also be created in your project containing Cities.cs file.

Below is the code for the web service which will handle the jQuery Autocomplete calls and will return the matching records from the tbCityMaster  table.

Asp.Net C# Code

In Cities.cs file create a web method to fetch city names from sql server database as:

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.Services;

using System.Web.Services;

/// <summary>

/// Summary description for WebService

/// </summary>

[WebService(Namespace = "http://tempuri.org/")]

[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]

// To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.

[System.Web.Script.Services.ScriptService]

public class Cities : System.Web.Services.WebService

{

    [WebMethod]

    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]

    public string[] FetchCityNames(string SearchText)

    {

        List<string> CityList = new List<string>();

        using (SqlConnection con = new SqlConnection())

        {

            con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;

            using (SqlCommand cmd = new SqlCommand("spGetCityNames", con))

            {

                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.AddWithValue("@SearchText", SearchText);

                cmd.Connection = con;

                con.Open();

                using (SqlDataReader dr = cmd.ExecuteReader())

                {

                    while (dr.Read())

                    {

                        CityList.Add(dr["CityName"].ToString());

                    }

                }

                con.Close();

            }

            return CityList.ToArray();

        }

    }  

}