Introduction: In this article I am going to explain how to populate jquery UI auto complete in asp.net text box to auto suggest city names fetched from database on starting typing in textbox using web service as shown in image below:
This article also serves the following:
- How to implement jQuery AutoComplete in Content Page with Master Page in ASP.Net
- Jquery UI autocomplete not working inside update panel
Description: Have you ever noticed how the related suggestions highlights as you start typing in the Google search box? This is called AutoComplete. We can also implement this functionality in our asp.net web applications using jquery UI AutoComplete
The concept is simple. When you start typing in the TextBox, It fetches the matching strings from the database and display while typing e.g. when you type a single character 'm' in the TextBox then all the city names starting with 'm' will be fetched from the database and displayed as a suggestion in textbox.
There are two methods to implement jquery UI Autocomplete:
- By defining web method in web service to fetch data from database based on search term passed.
- By defining static web method on page to fetch data from database based on search term passed.
Implementation: Let’s create a demo page to demonstrate autocomplete feature using jquery.
- First of all create a table using following script
CREATE TABLE tbCityMaster
(
CityId INT NOT NULL PRIMARY KEY IDENTITY(1,1),
CityName VARCHAR(100)
)
- Now insert some dummy data into this tables using following script
GO
INSERT INTO tbCityMaster (CityName)
VALUES
('Chandigarh'),
('Agra'),
('Bharatnagar'),
('Pathankot'),
('Amritsar'),
('Bhiwani'),
('Delhi'),
('Panchkula'),
('Gurgaon'),
('Baroda'),
('Palampur'),
('Bhuj')
('Bathinda'),
('Patiala'),
('Panipat');
- Now create a stored procedure to fetch top 15 city names based on the text we enter in autocomplete textbox.
GO
CREATE PROCEDURE spGetCityNames
(
@SearchText VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP 15 CityName FROM tbCityMaster WHERE CityName LIKE @SearchText+'%'
END
- Now create connection string in web.config as:
<connectionStrings>
<add name="conStr" connectionString="Data Source=LALIT;Initial Catalog=MyDataBase;Integrated Security=True"/>
</connectionStrings>
Note: Replace the Data Source and the Initial Catalog as per your application.
- Now add a web service in the project: for this go to Website menu -> Add new item -> select Web Service and name Cities.asmx
A web service will be added in your project and a new folder App_Code will also be created in your project containing Cities.cs file.
Below is the code for the web service which will handle the jQuery Autocomplete calls and will return the matching records from the tbCityMaster table.
Asp.Net C# Code
In Cities.cs file create a web method to fetch city names from sql server database as:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Script.Services;
using System.Web.Services;
/// <summary>
/// Summary description for WebService
/// </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 Cities : System.Web.Services.WebService
{
[WebMethod]
[ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public string[] FetchCityNames(string SearchText)
{
List<string> CityList = new List<string>();
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
using (SqlCommand cmd = new SqlCommand("spGetCityNames", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SearchText", SearchText);
cmd.Connection = con;
con.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
CityList.Add(dr["CityName"].ToString());
}
}
con.Close();
}
return CityList.ToArray();
}
}
}
HTML Source Code
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title></title>
<script src="http://code.jquery.com/jquery-1.11.3.js" type="text/javascript"></script>
<script src="http://code.jquery.com/ui/1.11.3/jquery-ui.js" type="text/javascript"></script>
<link rel="stylesheet" href="//code.jquery.com/ui/1.11.4/themes/smoothness/jquery-ui.css" />
<script type="text/javascript">
$(function () {
CityAutoComplete();
});
function pageLoad() {
initPageRequestManager();
}
function initPageRequestManager() {
var prmInstance = Sys.WebForms.PageRequestManager.getInstance();
prmInstance.add_endRequest(function () {
//re-bind jquery events
CityAutoComplete();
});
}
function CityAutoComplete() {
$("#<%=txtSearchCity.ClientID %>").autocomplete({
autoFocus: true,
source: function (request, response) {
var inputdata = "{'SearchText': '" + request.term + "'}";
$.ajax({
url: '<%=ResolveUrl("~/Cities.asmx/FetchCityNames") %>',
data: inputdata,
dataType: "json",
type: "POST",
contentType: "application/json; charset=utf-8",
success: function (data) {
response($.map(data.d, function (item) {
return {
label: item
}
}))
},
error: function (response) {
alert(response.responseText);
},
failure: function (response) {
alert(response.responseText);
}
});
},
minLength: 1,
});
}
</script>
<style type="text/css">
#dvSearch {
background: #e1e1e1; /* Fallback color for non-css3 browsers */
width: 400px;
/* Gradients */
background: -webkit-gradient( linear,left top, left bottom, color-stop(0, rgb(243,243,243)), color-stop(1,rgb(225,225,225)));
background: -moz-linear-gradient( center top, rgb(243,243,243) 0%, rgb(225,225,225) 100%);
/* Rounded Corners */
border-radius: 17px;
-webkit-border-radius: 17px;
-moz-border-radius: 17px;
/* Shadows */
box-shadow: 1px 1px 2px rgba(0,0,0,.3), 0 0 2px rgba(0,0,0,.3);
-webkit-box-shadow: 1px 1px 2px rgba(0,0,0,.3), 0 0 2px rgba(0,0,0,.3);
-moz-box-shadow: 1px 1px 2px rgba(0,0,0,.3), 0 0 2px rgba(0,0,0,.3);
}
/*** TEXT BOX ***/
.searchbox {
background: #fafafa; /* Fallback color for non-css3 browsers */
/* Gradients */
background: -webkit-gradient( linear, left bottom, left top, color-stop(0, rgb(250,250,250)), color-stop(1,rgb(230,230,230)));
background: -moz-linear-gradient( center top, rgb(250,250,250) 0%, rgb(230,230,230) 100%);
border: 0;
border-bottom: 1px solid #fff;
border-right: 1px solid rgba(255,255,255,.8);
font-size: 16px;
margin: 4px;
padding: 5px;
width: 380px;
/* Rounded Corners */
border-radius: 17px;
-webkit-border-radius: 17px;
-moz-border-radius: 17px;
/* Shadows */
box-shadow: -1px -1px 2px rgba(0,0,0,.3), 0 0 1px rgba(0,0,0,.2);
-webkit-box-shadow: -1px -1px 2px rgba(0,0,0,.3), 0 0 1px rgba(0,0,0,.2);
-moz-box-shadow: -1px -1px 2px rgba(0,0,0,.3), 0 0 1px rgba(0,0,0,.2);
}
/*** USER IS FOCUSED ON TEXT BOX ***/
.searchbox:focus {
outline: none;
background: #fff; /* Fallback color for non-css3 browsers */
/* Gradients */
background: -webkit-gradient( linear, left bottom, left top, color-stop(0, rgb(255,255,255)), color-stop(1,rgb(235,235,235)));
background: -moz-linear-gradient( center top, rgb(255,255,255) 0%, rgb(235,235,235) 100%);
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<fieldset style="width: 410px; height: 200px;">
<legend>jQuery UI Autocomplete example in asp.net</legend>
<div id="dvSearch">
<asp:TextBox ID="txtSearchCity" CssClass="searchbox" placeholder="Search by city name" runat="server"></asp:TextBox>
</div>
</fieldset>
</ContentTemplate>
</asp:UpdatePanel>
</div>
</form>
</body>
</html>
.png)
0 comments:
Post a Comment
Note: only a member of this blog may post a comment.