AJAX AutoCompleteExtender Example in ASP.Net

Here I am explaining, how to use AJAX AutoCompleteExtender Control directly with ASP.Net Web Page without using any web service.

Database

CREATE TABLE tblEmployee(EmployeeID int primary key IDENTITY(1,1),

                                             EmployeeName Varchar(90));

INSERT INTO tblEmployee Values('Ana Trujillo');

INSERT INTO tblEmployee Values('Antonio Moreno');

INSERT INTO tblEmployee Values('Ann Devon');

INSERT INTO tblEmployee Values('Andre Fonseca');

INSERT INTO tblEmployee Values('Annette Roulet');

INSERT INTO tblEmployee Values('Anabela Dominues');

INSERT INTO tblEmployee Values('Brandem');

INSERT INTO tblEmployee Values('Beckhom');

INSERT INTO tblEmployee Values('Treelisis');

INSERT INTO tblEmployee Values('Vaiolik');

Connection String

Once database is downloaded you can attach the same to your SQL Server Instance. I am making use of SQL Server 2005 Express hence below is my Connection string.

<connectionStrings>

      <addname="constr"connectionString="Data Source =REDDY-PC\REDDYSQLEXP;Initial Catalog = Demo; Integrated Security = true"/>

</connectionStrings>

AJAX Control Toolkit

Download the AJAX Control Toolkit DLL using the link below and add reference to your project.

AJAX Installation Steps

 

Once that’s done register it on the ASPX page as given below

<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="cc1" %>

 

HTML Markup

Below is the HTML markup of the ASP.Net Web Page

<asp:ScriptManager ID="ScriptManager1" runat="server"

EnablePageMethods = "true">

</asp:ScriptManager>

 

<asp:TextBox ID="txtContactsSearch" runat="server"></asp:TextBox>

<cc1:AutoCompleteExtender ServiceMethod="SearchCustomers"

    MinimumPrefixLength="2"

    CompletionInterval="100" EnableCaching="false" CompletionSetCount="10"

    TargetControlID="txtContactsSearch"

    ID="AutoCompleteExtender1" runat="server" FirstRowSelected = "false">

</cc1:AutoCompleteExtender>

 

As you can notice above I have placed a ScriptManager, a TextBox that will act as AutoComplete and the AJAX Control Toolkit AutoCompleteExtender. Also I am calling a method SearchCustomers using the ServiceMethod property

 

Server Side Methods

The following method is used to populate the Auto complete list of customers

C#

[System.Web.Script.Services.ScriptMethod()]

[System.Web.Services.WebMethod]

public static List<string> SearchEmployees(string prefixText, int count)

{

    using (SqlConnection conn = new SqlConnection())

    {

        conn.ConnectionString = ConfigurationManager

                .ConnectionStrings["constr"].ConnectionString;

        using (SqlCommand cmd = new SqlCommand())

        {

            cmd.CommandText = "select EmployeeName from tblEmployee where " +

            "EmployeeName like @SearchText + '%'";

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

            cmd.Connection = conn;

            conn.Open();

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

            using (SqlDataReader sdr = cmd.ExecuteReader())

            {

                while (sdr.Read())

                {

                    customers.Add(sdr["EmployeeName"].ToString());

                }

            }

            conn.Close();

            return employees;

        }

    }

}

 

The above method simply searches the customers table and returns the list of customer names that matches the prefix text.

Note: It is very important that you keep the signature of the method same as what given above. The method must have two parameters namely

1. prefixText (string)

2. count (int)

Otherwise the method won’t work with AutoCompleteExtender.

 

The end.