Create the required tables and stored procedures
Table Script -
Create Table DepartmentTable
(
Id int identity primary key,
DepartmentName nvarchar(20)
)
Go
Insert into DepartmentTable values ('Science')
Insert into DepartmentTable values ('Arts')
Insert into DepartmentTable values ('Commerce')
Go
Stored Procedure -
CREATE PROCEDURE USP_GetDepartment
AS
BEGIN
Select Id, DepartmentName from DeparmentTable
END
GO
Step 2-
Create new asp.net web application project. Name it JqueryDemo.
Step 3-
Include a connection string in the web.config file to your database.
<add name="CS"
connectionString="server=.;database=SampleDB;integrated security=SSPI"/>
Step 4-
Add a class file to the project. Name it Department.cs. Copy and paste the following code.
namespace
{
public class Department
{
public int Id { get; set; }
public string DepartmentName { get; set; }
}
}
Step 5-
Add a WebService (ASMX) to the project. Name it WebService.asmx. Copy and paste the following code.
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
using System.Web.Services;
namespace
{
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class WebService: System.Web.Services.WebService
{
[WebMethod]
public void GetDepartment()
{
string cs = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;
List<Department> Department= new List<Department>();
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("USP_GetDepartment", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Department ObjDepartment = new Department();
ObjDepartment.Id = Convert.ToInt32(rdr["Id"]);
ObjDepartment.Name = rdr["DepartmentName"].ToString();
Department.Add(ObjDepartment);
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(Department));
}
Step 6-
Add a WebForm to the ASP.NET project. Copy and paste the following HTML and jQuery code.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
<style type="text/css">
</style>
<script src="https://code.jquery.com/jquery-2.2.4.js"></script>
<script type="text/javascript">
$(document).ready(function () {
var DDLDepartment = $('#DDLDepartment');
$.ajax({
url: 'WebService.asmx/GetDepartment',
method: 'post',
dataType: 'json',
success: function (data) {
DDLDepartment.append($('<option/>', { value: -1, text: 'Select Department' }))
$(data).each(function (index, item) {
DDLDepartment.append($('<option/>', { value: item.Id, text: item.DepartmentName }));
});
},
error: function (err) {
alert(err);
}
});
});
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<table align="center" style="width: 261px">
<tr>
<td>
<asp:Label ID="lblDepartment" runat="server" Text="Department : "></asp:Label></td>
<td>
<asp:DropDownList ID="DDLDepartment" runat="server">
</asp:DropDownList></td>
</tr>
</table>
</div>
</form>
</body>
</html>
