Auto Complete Extender Using WCF Services in Asp.Net With Database

Database Setup :

SQL Server Database Script:

CREATE TABLE [dbo].[STUDENT](
	[StudentID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[RollNumber] [decimal](10, 2) NULL,
	[Class] [nvarchar](50) NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[STUDENT] ON
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (1, N'Andy', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (7, N'Anar', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (3, N'Bill', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (8, N'Amit', CAST(100.00 AS Decimal(10, 2)), N'MCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (9, N'Anjali', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (6, N'Chris', CAST(300.00 AS Decimal(10, 2)), N'MBBS')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (10, N'Avinash', CAST(200.00 AS Decimal(10, 2)), N'BCA')
INSERT [dbo].[STUDENT] ([StudentID], [Name], [RollNumber], [Class]) VALUES (11, N'Rohan', CAST(200.00 AS Decimal(10, 2)), N'BCA')
SET IDENTITY_INSERT [dbo].[STUDENT] OFF

Web.Config Configuration :

<system.serviceModel>
		<behaviors>
			<serviceBehaviors>
				<behavior name="ServiceBehavior">
					<serviceMetadata httpGetEnabled="true"/>
					<serviceDebug includeExceptionDetailInFaults="true"/>
				</behavior>
			</serviceBehaviors>
			<endpointBehaviors>
				<behavior name="ServiceAspNetAjaxBehavior">
					<enableWebScript/>
				</behavior>
			</endpointBehaviors>
		</behaviors>
		<serviceHostingEnvironment aspNetCompatibilityEnabled="true"/>
		<services>
			<service behaviorConfiguration="ServiceBehavior" name="Service">
				<endpoint address="" binding="webHttpBinding" contract="IService" behaviorConfiguration="ServiceAspNetAjaxBehavior">
					<identity>
						<dns value="localhost"/>
					</identity>
				</endpoint>
				<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
			</service>
		</services>
	</system.serviceModel>

CSS :

<style>
        .completionList
        {
            padding: 0px;
            margin: 0px;
            color: black;
            overflow: auto;
            text-align: left;
            border: 2px solid lightskyblue;
            list-style-type: none;
            cursor: default;
            height: 150px;
        }
        .item
        {
            height: 17px;
            cursor: pointer;
        }
        
        .itemHighLight
        {
            color: White;
            background-color: lightskyblue;
            cursor: pointer;
        }
    </style>

Register AjaxControlToolKit Assembly to Asp.Net

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

Asp.Net :

    <asp:ScriptManager ID="ScriptManager1" runat="server" EnablePageMethods="true">
    </asp:ScriptManager>
    <asp:TextBox ID="txtStudent" runat="server"></asp:TextBox>
    <cc1:AutoCompleteExtender ServiceMethod="GetStudent" MinimumPrefixLength="1" ServicePath="~/Service.svc"
        CompletionInterval="100" EnableCaching="false" CompletionSetCount="10" TargetControlID="txtStudent"
        ID="AutoCompleteExtender1" runat="server" FirstRowSelected="false" CompletionListCssClass="completionList"
        CompletionListItemCssClass="item" CompletionListHighlightedItemCssClass="itemHighLight">
    </cc1:AutoCompleteExtender>

IService.CS :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.ServiceModel.Web;

[ServiceContract]
public interface IService
{
    [WebInvoke(Method = "POST", ResponseFormat = WebMessageFormat.Json)]
    List<string> GetStudent(string prefixText, int count);
}

Service.CS

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using AjaxControlToolkit;
using System.ServiceModel.Activation;
using System.Web.Script.Serialization;

[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class Service : IService
{
    public List<string> GetStudent(string prefixText, int count)
    {
        List<string> Students = new List<string>();
        using (SqlConnection conn = new SqlConnection())
        {
            conn.ConnectionString = "Data Source=.; uid=sa; pwd=sql;database=WCF;";
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandText = "select Name, StudentID from STUDENT where " + "Name like @prefix + '%'";
                cmd.Parameters.AddWithValue("@prefix", prefixText);
                cmd.Connection = conn;
                conn.Open();
                using (SqlDataReader sdr = cmd.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        string item = AjaxControlToolkit.AutoCompleteExtender.CreateAutoCompleteItem(sdr["Name"].ToString(), sdr["StudentID"].ToString());
                        Students.Add(item);
                    }
                }
                conn.Close();
            }
            return Students;
        }
    }
}