Step 1 : SQL Script to create and populate table tblStudents
Create Table tblStudents
(
ID int identity primary key,
Name nvarchar(50),
Gender nvarchar(20),
TotalMarks int
)
GO
Insert into tblStudents values('Mark Hastings','Male',900)
Insert into tblStudents values('Maria Nicholas','Female',760)
Insert into tblStudents values('John Stenson','Male',980)
Insert into tblStudents values('Rob Gerald','Male',990)
Insert into tblStudents values('Ron Simpson','Male',440)
Insert into tblStudents values('Rad Wicht','Male',320)
Insert into tblStudents values('Julian John','Male',983)
Insert into tblStudents values('James Bynes','Male',720)
Insert into tblStudents values('Mary Ward','Female',870)
Insert into tblStudents values('Michael Niron','Male',680)
Step 2 : Create a stored procedure to retrieve employee names
Create proc spGetStudentNames
@term nvarchar(50)
as
Begin
Select Name from tblStudents where Name like @term + '%'
End
Step 3 : Create new asp.net web application project. Name it Demo.
Step 4 : Include a connection string in the web.config file to your database.
<add name="DBCS"
connectionString="server=.;database=SampleDB;integrated security=SSPI"/>
Step 5 : Add a Generic Handler to the project. Name it StudentHandler.ashx. Copy and paste the following code.
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Script.Serialization;
namespace Demo
{
public class StudentHandler : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
string term = context.Request["term"] ?? "";
List<string> listStudentNames = new List<string>();
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using(SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetStudentNames", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter()
{
ParameterName = "@term",
Value = term
});
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while(rdr.Read())
{
listStudentNames.Add(rdr["Name"].ToString());
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
context.Response.Write(js.Serialize(listStudentNames));
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
Step 6 : Download jQuery UI from jqueryui.com. Copy and paste the following files in your project.
a) jquery-ui.js
b) jquery-ui.css
c) images
Step 7 : Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code.
<%@ Page Language="C#" AutoEventWireup="true"
CodeBehind="WebForm1.aspx.cs"
Inherits="Demo.WebForm1" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<script src="jquery-1.11.2.js"></script>
<script src="jquery-ui.js"></script>
<link href="jquery-ui.css" rel="stylesheet" />
<script type="text/javascript">
$(document).ready(function () {
$('#txtName').autocomplete({
source: 'StudentHandler.ashx'
});
});
</script>
</head>
<body style="font-family: Arial">
<form id="form1" runat="server">
Student Name :
<asp:TextBox ID="txtName" runat="server">
</asp:TextBox>
</form>
</body>
</html>
Comments
Post a Comment