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 WebService (ASMX). Name it EmployeeService.asmx. Copy and paste the following code.
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Services;
namespace Demo
{
[WebService(Namespace = "http://tempuri.org/")]
[WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
[System.ComponentModel.ToolboxItem(false)]
[System.Web.Script.Services.ScriptService]
public class EmployeeService : System.Web.Services.WebService
{
[WebMethod]
public 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;
SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@Id";
parameter.Value = employeeId;
cmd.Parameters.Add(parameter);
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 : Add an HTML page to the ASP.NET project. Copy and paste the following HTML and jQuery code
<html>
<head>
<script src="jquery-1.11.2.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$('#btnGetEmployee').click(function () {
var empId = $('#txtId').val();
$.ajax({
url: 'EmployeeService.asmx/GetEmployeeById',
data: { employeeId: empId },
method: 'post',
dataType: 'xml',
success: function (data) {
var jQueryXml = $(data);
$('#txtName').val(jQueryXml.find('Name').text());
$('#txtGender').val(jQueryXml.find('Gender').text());
$('#txtSalary').val(jQueryXml.find('Salary').text());
},
error: function (err) {
alert(err);
}
});
});
});
</script>
</head>
<body style="font-family:Arial">
ID : <input id="txtId" type="text" style="width:100px" />
<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>
</body>
</html>
In our next video we will discuss how to call an asp.net web service that returns JSON data using jQuery AJAX.
Comments
Post a Comment