Step 1 : Create SQL Server table and insert employee data
Create table tblEmployee
(
Id int primary key identity,
Name nvarchar(50),
Gender nvarchar(10),
Salary int
)
GO
Insert into tblEmployee values ('Mark', 'Male', 50000)
Insert into tblEmployee values ('Sara', 'Female', 60000)
Insert into tblEmployee values ('John', 'Male', 45000)
Insert into tblEmployee values ('Pam', 'Female', 45000)
GO
Step 2 : Create a stored procedure to retrieve employee data by ID
Create procedure spGetEmployeeById
@Id int
as
Begin
Select ID, Name, Gender, Salary
from tblEmployee
where ID = @Id
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 class file to the project. Name it Employee.cs. Copy and paste the following code.
namespace Demo
{
public class Employee
{
public int ID { get; set; }
public string Name { get; set; }
public string Gender { get; set; }
public int Salary { get; set; }
}
}
Step 6 : Add a new WebForm. Name it WebForm.aspx. Copy and paste the following code in the code-behind page.
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace Demo
{
public partial class WebForm1 : System.Web.UI.Page
{
[System.Web.Services.WebMethod]
public static Employee GetEmployeeById(int employeeId)
{
Employee employee = new Employee();
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetEmployeeById", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter()
{
ParameterName = "@Id",
Value = employeeId
});
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
employee.ID = Convert.ToInt32(rdr["Id"]);
employee.Name = rdr["Name"].ToString();
employee.Gender = rdr["Gender"].ToString();
employee.Salary = Convert.ToInt32(rdr["Salary"]);
}
}
return employee;
}
}
}
Step 7 : Copy and paste the following HTML and jQuery code in WebForm1.aspx
<%@ 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 type="text/javascript">
$(document).ready(function () {
$('#btnGetEmployee').click(function () {
var empId = $('#txtId').val();
$.ajax({
url: 'WebForm1.aspx/GetEmployeeById',
method: 'post',
contentType: "application/json",
data: '{employeeId:' + empId + '}',
dataType: "json",
success: function (data) {
$('#txtName').val(data.d.Name);
$('#txtGender').val(data.d.Gender);
$('#txtSalary').val(data.d.Salary);
},
error: function (err) {
alert(err);
}
});
});
});
</script>
</head>
<body style="font-family: Arial">
<form id="form1" runat="server">
ID :
<input id="txtId" type="text" style="width: 86px" />
<input type="button" id="btnGetEmployee" value="Get Employee" />
<br /><br />
<table border="1" style="border-collapse: collapse">
<tr>
<td>Name</td>
<td><input id="txtName" type="text" /></td>
</tr>
<tr>
<td>Gender</td>
<td><input id="txtGender" type="text" /></td>
</tr>
<tr>
<td>Salary</td>
<td><input id="txtSalary" type="text" /></td>
</tr>
</table>
</form>
</body>
</html>
Comments
Post a Comment