Using jQuery Autocomplete in ASP.NET

In this article I will show how to use jQuery Autcomplete in ASP.NET application. Autocomplete is one of the most needed feature while making applications where user demands the auto predicting feature when he types something in the input textbox. So what is Autocomplete ?
From http://en.wikipedia.org/wiki/Autocomplete
Autocomplete is a feature provided by many web browsers, e-mail programs, search engine interfaces, source code editors, database query tools, word processors, and command line interpreters. Autocomplete involves the program predicting a word or phrase that the user wants to type in without the user actually typing it in completely. This feature is effective when it is easy to predict the word being typed based on those already typed, such as when there are a limited number of possible or commonly used words (as is the case with e-mail programs, web browsers, or command line interpreters), or when editing text written in a highly-structured, easy-to-predict language  

In common scenarios user types a keyword in the search textbox and after that we have to hit the the database and bring the records matching that keyword and show these records as the autocomplete. Let's start with database side first. Create a sample table say "Users" and create a stored procedure say "Search_Users" for searching the records based on the searched keyword. Next we are going to insert random 500 records in this table. Below is the script for all of them.

GO
CREATE TABLE [Users]
    (
      [ID] INT IDENTITY ,
      [Name] NVARCHAR(100)
    )

GO

--Let's create a search sp for names

CREATE PROC [Search_Users] @Name NVARCHAR(100)
AS 
    BEGIN
        SELECT  [ID],[Name]
        FROM    [Users]
        WHERE   [Name] LIKE ( @Name + '%' )
    END

GO

    
--Lets Populate it with 500 random names
INSERT INTO [Users]
SELECT dbo.[GenerateRandomName](10)
GO 500

Above you must be wondering what is "GenerateRandomName" function ? I have used that user defined function so as to create a random name and by "GO 500" I have made the insert script to run 500 times. Below is the script of function. Note in this function I have used "RandNumberView" View. Because I need to use the random number if I had used the rand() simply in the function I would have received this error "Invalid use of side-effecting or time-dependent operator in 'rand' within a function"

CREATE FUNCTION [GenerateRandomName] ( @LENGTH INT )
RETURNS NVARCHAR(255)
AS 
    BEGIN
  
	--DECLARE VARIABLES
        DECLARE @RandomNumber NVARCHAR(255)
        DECLARE @I SMALLINT
        DECLARE @RandNumber FLOAT
        DECLARE @Position TINYINT
        DECLARE @ExtractedCharacter VARCHAR(1)
        DECLARE @ValidCharacters VARCHAR(255)
        DECLARE @VCLength INT
    
    --SET VARIABLES VALUE
        SET @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'    
        SET @VCLength = LEN(@ValidCharacters)
        SET @ExtractedCharacter = ''
        SET @RandNumber = 0
        SET @Position = 0
        SET @RandomNumber = ''


        SET @I = 1

        WHILE @I < ( @Length + 1 ) 
            BEGIN

                SET @RandNumber = ( SELECT  RandNumber
                                    FROM    [RandNumberView]
                                  )
                SET @Position = CONVERT(TINYINT, ( ( @VCLength - 1 )
                                                   * @RandNumber + 1 ))
                SELECT  @ExtractedCharacter = SUBSTRING(@ValidCharacters,
                                                        @Position, 1)
                SET @I = @I + 1

                SET @RandomNumber = @RandomNumber + @ExtractedCharacter

            END

	
        RETURN @RandomNumber

  
    END
GO

CREATE VIEW [RandNumberView]
AS  

SELECT  RAND() AS [RandNumber]

Now let's come to the server side code which will act as the middle man between the aspx page and the database. I have created ashx handler for it and lets keep it's name as "Autocomplete.ashx" . Below is the code for it:
Autcomomplete.ashx

<%@ WebHandler Language="C#" Class="AutoComplete" %>
 
using System;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;

public class AutoComplete : IHttpHandler
{

    public void ProcessRequest(HttpContext context)
    {
        string searchText = context.Request.QueryString["term"];
        Collection<AutoCompleteDTO> collection;
        string connectionString =
            "Data Source=YourServerName; Initial Catalog=YourDatabase; User ID=YourUserName; Password=YourPassword";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand())
            {
                command.Connection = connection;
                command.CommandText = "Search_Users";
                command.CommandType = CommandType.StoredProcedure;

                SqlParameter paramName = new SqlParameter("@Name", SqlDbType.NVarChar);
                paramName.Size = 100;
                paramName.Value = searchText;
                command.Parameters.Add(paramName);
                collection = new Collection<AutoCompleteDTO>();
                connection.Open();
                using (SqlDataReader dataReader = command.ExecuteReader())
                {
                    AutoCompleteDTO dto;
                    if (dataReader != null)
                        while (dataReader.Read())
                        {
                            dto = new AutoCompleteDTO();
                            dto.value = dto.label = (string)dataReader["Name"];
                            dto.id = Convert.ToString(dataReader["ID"]);
                            collection.Add(dto);
                        }
                }
            }
        }
        JavaScriptSerializer serializer = new JavaScriptSerializer();

        string jsonString=serializer.Serialize(collection);

        context.Response.Write(jsonString);
    }

    public bool IsReusable
    {
        get
        {
            return false;
        }
    }
}

Above you must be seeing I have used AutoCompleteDTO class. Now why I have used it ? Actually I have to return a JSON string to the client side with following properties "id", "label" and "value". I grasped the data from db and transferred that collection to collection of AutCompleteDTO. Next target is to convert this collection to JSON string. That I have done with :

        JavaScriptSerializer serializer = new JavaScriptSerializer();
        string jsonString=serializer.Serialize(collection);
        context.Response.Write(jsonString);

Code for AutCompleteDTO class:

public class AutoCompleteDTO
{
    public string id { get; set; }
    public string label { get; set; }
    public string value { get; set; }
}

Next come to ASPX code. Let's say the target page is "Sample.aspx" 

Sample.aspx:

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Web.Services" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/themes/base/jquery-ui.css"
        type="text/css" media="all" />
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.min.js"
        type="text/javascript"></script>
    <script type="text/javascript">

        $(function () {
            $('#<%= txtName.ClientID %>').autocomplete({
                source: "AutoComplete.ashx"
            });
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
    </form>
</body>
</html>

Above I have used following external references of scripts:
 

    <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/themes/base/jquery-ui.css"
        type="text/css" media="all" />
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.min.js"
        type="text/javascript"></script>

You can download them and store them at your end for your own end. The code which leads to autocomplete behavior is :

            $('#<%= txtName.ClientID %>').autocomplete({
                source: "AutoComplete.ashx"
            });

Below is the screenshot of my screen after typing "s" in the searchtextbox

Demo:

                                                                            

Update:
How to add multiple words feature for autocomplete.

For this I have just used the logic provided at http://jqueryui.com/demos/autocomplete/#multiple-remote i.e:

            $("#<%= txtMultipleName.ClientID %>").autocomplete({
                source: function (request, response) {
                    $.getJSON("AutoComplete.ashx", {
                        term: extractLast(request.term)
                    }, response);
                },
                search: function () {
                    // custom minLength
                    var term = extractLast(this.value);
                    if (term.length < 1) {
                        return false;
                    }
                },
                focus: function () {
                    // prevent value inserted on focus
                    return false;
                },
                select: function (event, ui) {
                    var terms = split(this.value);
                    // remove the current input
                    terms.pop();
                    // add the selected item
                    terms.push(ui.item.value);
                    // add placeholder to get the comma-and-space at the end
                    terms.push("");
                    this.value = terms.join(", ");
                    return false;
                }
            });
            function split(val) {
                return val.split(/,\s*/);
            }
            function extractLast(term) {
                return split(term).pop();
            }

Sample code of Source page:

<%@ Page Language="C#" %>

<%@ Import Namespace="System.Web.Services" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <link rel="stylesheet" href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/themes/base/jquery-ui.css"
        type="text/css" media="all" />
    <script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.3/jquery.min.js"></script>
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.5/jquery-ui.min.js"
        type="text/javascript"></script>
    <script type="text/javascript">

        $(function () {
            $("#<%= txtMultipleName.ClientID %>").autocomplete({
                source: function (request, response) {
                    $.getJSON("AutoComplete.ashx", {
                        term: extractLast(request.term)
                    }, response);
                },
                search: function () {
                    // custom minLength
                    var term = extractLast(this.value);
                    if (term.length < 1) {
                        return false;
                    }
                },
                focus: function () {
                    // prevent value inserted on focus
                    return false;
                },
                select: function (event, ui) {
                    var terms = split(this.value);
                    // remove the current input
                    terms.pop();
                    // add the selected item
                    terms.push(ui.item.value);
                    // add placeholder to get the comma-and-space at the end
                    terms.push("");
                    this.value = terms.join(", ");
                    return false;
                }
            });
            function split(val) {
                return val.split(/,\s*/);
            }
            function extractLast(term) {
                return split(term).pop();
            }
        });
    </script>
</head>
<body>
    <form id="form1" runat="server">
    Multiple word:
    <asp:TextBox ID="txtMultipleName" runat="server" Width="400px"></asp:TextBox>
    </form>
</body>
</html>

Same "Autcomplete.ashx" handler will be used for this purpose.
Demo for multiple words:
                                                                              

Do let me know your feedback, comments.