Link for all dot net and sql server video tutorial playlists
Link for slides, code samples and text version of the video
In this video we will discuss how to call server side aspx page method using jQuery AJAX.
We will have a c# function in the code-behind file, the retrieves employee details by ID from the SQL Server database. We want to call this server side c# function using jquery and display the data.
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
$(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);
}
});
});
});
