In this article we will be using AJAX Auto Complete Extender to have a drop down like feel when we type in the textbox
Introduction
In real world application development we are given a task to develop an auto complete feature just as Google has when we type something in Google Search text Box, the matching criteria is shown in a drop down look.In order to achieve this we will be using AJAX Auto Complete Extender.
Objective
- Defining Web Method in WebService for Getting data from Database.
- Understanding AJAX Auto Complete extender.
Using the code
- Create an Empty Website in VS 2010.Add a Webform
- Now drag and drop 1 label and 1 Textbox.
- Now Right Click on Web Site -> Add New Item -> Select Web Service -> Name it as AutoComplete.
- Once you create the webservice an AutoComplete.cs will be automatically added to App_Code folder.
Explanation of Web Method for Getting data from database.
- First we will uncomment the line [System.Web.Script.Services.ScriptService] since we are using AJAX component.
- Then we create a Web-method and define a function which has a return type a string array the function will take a string variable as an argument.
- Now in the method We will define a list which is type of string.
- Then we write our SQL Query to get data from database and dill the data from data apdapter to dataaset.
- Now we declare a string array named as countryName and get the Count of the records that are fetched from the Query.
- We then run a foreach loop to the data rows in the dataset and then we set the value of the country name to the column of the table i.e. Countries.
- Finally we return the countryname variable as our function has a return type of string array.
Screen :- Adding of Web Service
Understanding the AJAX Auto Complete Extender.
- TargetControlID :- It will hold the ID of the textbox that will be used for auto-complete
- MinimumPrefixLength :- The count of characters required to enter by the user to fetch data from the database matching the Character.
- ServicePath :- The path of the webservice where it is located.
- ServiceMethod :- The name of the webmethod that we have used to get data from database in the web service.(Note :- Name of the web method should be case sensitive).
Lets Start Implementation
// HTML Mark UP
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AutoCompleteWithoutWebService.aspx.cs"
Inherits="AutoCompleteWithoutWebService" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>
<!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 runat="server">
<title></title>
<style type="text/css">
.style1
{
font-family: "Cooper Black";
font-size: x-large;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<asp:ToolkitScriptManager ID="Toolkitscriptmanager1" runat="server">
<Services>
<asp:ServiceReference Path="AutoComplete.asmx" />
</Services>
</asp:ToolkitScriptManager>
<div class="style1" style="text-align: center">
AJAX AutoComplete Sample using With Web Service<br />
<br />
</div>
<p style="text-align: center">
<asp:Label ID="Label1" runat="server" CssClass="style1" Text="Type Name of Countries"></asp:Label>
<asp:TextBox ID="xtxtCountries" runat="server" Height="26px" Width="199px"></asp:TextBox>
<asp:AutoCompleteExtender ID="xtxtCountries_AutoCompleteExtender" runat="server"
MinimumPrefixLength="1" Enabled="True" CompletionSetCount="3" EnableCaching="true" ServicePath="AutoComplete.asmx"
ServiceMethod="GetCountries" TargetControlID="xtxtCountries">
</asp:AutoCompleteExtender>
</p>
</form>
</body>
</html>
//AutoComplete.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;
/// <summary>
/// Summary description for AutoComplete
/// </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 AutoComplete : System.Web.Services.WebService {
public AutoComplete () {
//Uncomment the following line if using designed components
//InitializeComponent();
}
[WebMethod]
public string[] GetCountries(string prefixText)
{
List<string> fetchCountries = new List<string>();
SqlConnection sqlconn = new SqlConnection("Data Source=.;Initial Catalog=DNF;Persist Security Info=True;User ID=sa;Password=sqluser");
sqlconn.Open();
SqlCommand cmdselect = new SqlCommand("select Countries from Countries where Countries like '" + prefixText + "%' ", sqlconn);
cmdselect.CommandType = CommandType.Text;
cmdselect.Parameters.AddWithValue("@CountryPrefix", prefixText);
SqlDataAdapter selectda = new SqlDataAdapter(cmdselect);
DataSet ds = new DataSet();
selectda.Fill(ds);
string[] countryName = new string[ds.Tables[0].Rows.Count];
int i = 0;
try
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
countryName.SetValue(dr["Countries"].ToString(), i);
i++;
}
}
catch { }
finally
{
sqlconn.Close();
}
return countryName;
}
}Note :- In the html mark up, we have added a Tool Script manager, inside the Tool Script manager we will have to add ServiceReference and the path for the webservice so that no issue arises.
Output Screen :-
Conclusion
The following task can also be achieved without Web Service as well that we will see in the next part where we will create a Web Method in the Code behind itself