Here is what we want to achieve
1. When the page initially loads, retrieve all the employee rows from the database and display them in a table on the web page
2. When "Add New Employee" button is clicked, jQuery modal popup should be displayed
3. After entering the employee details, and when "Create" button is clicked, the data should be saved to the database and the popup should close automatically
4. The newly added employee should be displayed in the table on the web page
5. If the cancel button ic clicked the dialog should be closed without adding the employee to the database
Step 1 : Create SQL Server table and insert test employee data
Create table tblEmployees
(
ID int primary key identity,
FirstName nvarchar(50),
LastName nvarchar(50),
Email nvarchar(50)
)
Go
Insert into tblEmployees values ('Mark', 'Hastings', 'MarkH@test.com')
Insert into tblEmployees values ('Steve', 'Wicht', 'steve.wicht@test.com')
Go
Step 2 : Create stored procedures to insert and retrieve employees
Create procedure spInsertEmployee
@FirstName nvarchar(50),
@LastName nvarchar(50),
@Email nvarchar(50)
as
Begin
Insert into tblEmployees
values (@FirstName, @LastName, @Email)
End
Create procedure spGetEmployees
as
Begin
Select * from tblEmployees
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 FirstName { get; set; }
public string LastName { get; set; }
public string Email { get; set; }
}
}
Step 6 : Add a new WebService (ASMX). Name it EmployeeService.asmx. Copy and paste the following code.
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.Script.Serialization;
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 void SaveEmployee(Employee employee)
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spInsertEmployee", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramFirstName = new SqlParameter()
{
ParameterName = "@FirstName",
Value = employee.FirstName
};
cmd.Parameters.Add(paramFirstName);
SqlParameter paramLastName = new SqlParameter()
{
ParameterName = "@LastName",
Value = employee.LastName
};
cmd.Parameters.Add(paramLastName);
SqlParameter paramEmail = new SqlParameter()
{
ParameterName = "@Email",
Value = employee.Email
};
cmd.Parameters.Add(paramEmail);
con.Open();
cmd.ExecuteNonQuery();
}
}
[WebMethod]
public void GetEmployees()
{
string cs = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
List<Employee> listEmployee = new List<Employee>();
using (SqlConnection con = new SqlConnection(cs))
{
SqlCommand cmd = new SqlCommand("spGetEmployees", con);
cmd.CommandType = CommandType.StoredProcedure;
con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Employee employee = new Employee();
employee.FirstName = rdr["FirstName"].ToString();
employee.LastName = rdr["LastName"].ToString();
employee.Email = rdr["Email"].ToString();
listEmployee.Add(employee);
}
}
JavaScriptSerializer js = new JavaScriptSerializer();
Context.Response.Write(js.Serialize(listEmployee));
}
}
}
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 () {
var dialogDiv = $('#dialog');
dialogDiv.dialog({
autoOpen: false,
modal: true,
buttons: {
'Create': CreateEmployee,
'Cancel': function () {
dialogDiv.dialog('close');
clearInputFields();
}
}
});
function CreateEmployee() {
var emp = {};
emp.FirstName = $('#txtFirstName').val();
emp.LastName = $('#txtLastName').val();
emp.Email = $('#txtEmail').val();
$.ajax({
url: 'EmployeeService.asmx/SaveEmployee',
method: 'post',
data: '{ employee:' + JSON.stringify(emp) + '}',
contentType: 'application/json; charset=utf-8',
dataType: 'json',
success: function () {
loadEmployees();
dialogDiv.dialog('close');
clearInputFields();
}
});
}
function loadEmployees() {
var tboby = $('#employees tbody');
tboby.empty();
$.ajax({
url: 'EmployeeService.asmx/GetEmployees',
method: 'post',
dataType: 'json',
success: function (data) {
$(data).each(function () {
var tr = $('<tr></tr>')
tr.append('<td>' + this.FirstName + '</td>')
tr.append('<td>' + this.LastName + '</td>')
tr.append('<td>' + this.Email + '</td>')
tboby.append(tr);
})
}
});
}
function clearInputFields() {
$('#dialog input[type="text"]').val('');
}
$('#btnAddEmployee').click(function () {
dialogDiv.dialog("open");
});
loadEmployees();
});
</script>
</head>
<body style="font-family: Arial">
<form id="form1" runat="server">
<div id="dialog">
<table>
<tr>
<td>First Name</td>
</tr>
<tr>
<td><input type="text" id="txtFirstName" /></td>
</tr>
<tr>
<td>Last Name</td>
</tr>
<tr>
<td><input type="text" id="txtLastName" /></td>
</tr>
<tr>
<td>Email</td>
</tr>
<tr>
<td><input type="text" id="txtEmail" /></td>
</tr>
</table>
</div>
<table id="employees" style="border-collapse: collapse" border="1">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<br />
<input type="button" id="btnAddEmployee" value="Add New Employee" />
</form>
</body>
</html>
Comments
Post a Comment