Place to Tech: Web Services

Hi All,

Today I will demonstrate you on how to populate a text box with suggested values when user types a character in text box using a AJAX Auto Complete Extender. This demonstrate uses web service to get data from the database.
Suppose I have a database called "Demo" and have a table called "tblFlightInformation" to store Flight Schedule details.

Following is the database table script;

USE [Demo]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblFlightInformation](
[FlightId] [int] NOT NULL,
[FlightDestination] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[FlightLeaveTime] [datetime] NULL,
[FlightReturnTime] [datetime] NULL,
CONSTRAINT [PK_tblFlightInformation] PRIMARY KEY CLUSTERED
(
[FlightId] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Lets populate the table with sample data;

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (1, 'India', '2011-11-12 19:30:00', '2011-11-12 23:00:00')

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (2, 'Italy', '2011-11-13 06:00:00', '2011-11-13 12:45:00')

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (3, 'Singapore', '2011-11-13 04:10:00', '2011-11-14 01:15:00')

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (4, 'America', '2011-12-01 09:20:00', '2011-12-06 08:50:00')

INSERT INTO dbo.tblFlightInformation (FlightId, FlightDestination, FlightLeaveTime, FlightReturnTime)
VALUES (5, 'Saudi Arabia', '2011-12-10 23:10:00', '2011-12-13 19:10:00')

Lets create an asp.net web site and add a web service (WebServiceFlightInformation.asmx) and a web method called "LoadDestinationInformation".

[WebMethod]
public List<string> LoadDestinationInformation(string prefixText)
{
using (SqlConnection conn = new SqlConnection())
{
conn.ConnectionString = ConfigurationManager.ConnectionStrings["FlightInformation"].ConnectionString;

using (SqlCommand cmd = new SqlCommand())
{
cmd.CommandText = "select FlightDestination from tblFlightInformation where " +
"FlightDestination like @FlightDestination + '%'";
cmd.Parameters.AddWithValue("@FlightDestination", prefixText);
cmd.Connection = conn;
conn.Open();

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

using (SqlDataReader sdr = cmd.ExecuteReader())
{
while (sdr.Read())
{
FlighDestination.Add(sdr["FlightDestination"].ToString());
}
}

conn.Close();
return FlighDestination;
}
}

After that lets create the user interface by adding a aspx page to our web site. Following is the mark up for the aspx page.

<%@ Page Title="Home Page" Language="C#" AutoEventWireup="true"
CodeFile="Default.aspx.cs" Inherits="_Default" %>

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

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Ajax AutoCompleteExtender without Webservice</title>
</head>
<body>
<form id="form1" runat="server">
<ajax:ToolkitScriptManager ID="ScriptManager1" runat="server" EnablePageMethods = "true"/>
<div>
<asp:TextBox ID="txtCountry" runat="server" AutoPostBack="True"></asp:TextBox>
<ajax:AutoCompleteExtender ID="AutoCompleteExtender1"
runat="server"
TargetControlID="txtCountry"
MinimumPrefixLength="1"
EnableCaching="true"
CompletionSetCount="1"
CompletionInterval="1000"
ServiceMethod="LoadDestinationInformation"
ServicePath="WebServiceFlightInformation.asmx">
</ajax:AutoCompleteExtender>
</div>
</form>
</body>
</html>

That's all and have a look into the below image, that's how it works.....