Here I am explaining, how to use AJAX AutoCompleteExtender Control directly with ASP.Net Web Page without using any web service.
Database
For this tutorial I am making use of NorthWind database that you can download using the link given below.
Download Northwind Database
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 = .\SQLExpress;
Initial Catalog = Northwind; Integrated Security = true"/>
</connectionStrings>
AJAX Control Toolkit
Download the AJAX Control Toolkit DLL using the link below and add reference to your project.
Download AJAX Control Tookit
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> SearchCustomers(string prefixText, int count)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager
.ConnectionStrings["constr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select ContactName from Customers where " +
"ContactName like @SearchText + '%'";
cmd.Parameters.AddWithValue("@SearchText", prefixText);
cmd.Connection = conn;
conn.Open();
List<string> customers = new List<string>();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
customers.Add(sdr["ContactName"].ToString());
}
}
conn.Close();
return customers;
}
}
}
VB.Net
<System.Web.Script.Services.ScriptMethod(), _
System.Web.Services.WebMethod()> _
Public Shared Function SearchCustomers(ByVal prefixText As String, ByVal count As Integer) As List(Of String)
Dim conn As SqlConnection = New SqlConnection
conn.ConnectionString = ConfigurationManager _
.ConnectionStrings("constr").ConnectionString
Dim cmd As SqlCommand = New SqlCommand
cmd.CommandText = "select ContactName from Customers where" & _
" ContactName like @SearchText + '%'"
cmd.Parameters.AddWithValue("@SearchText", prefixText)
cmd.Connection = conn
conn.Open()
Dim customers As List(Of String) = New List(Of String)
Dim sdr As SqlDataReader = cmd.ExecuteReader
While sdr.Read
customers.Add(sdr("ContactName").ToString)
End While
conn.Close()
Return customers
End Function
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.
That’s it in this article. You can download the source code in C# and VB.Net using the link below
AutoCompleteExtenderWithoutWebService.zip