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/AutocompleteAutocomplete 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.
your site is very good . inside da news is very informative and interesting to read.
You’re an excessively skilled blogger. I’ve joined your rss feed and look forward to in the hunt for extra
nice content and i want more informaton