What we want to achieve?
1.) We want to display list of employee in table as shown figure.
2) When we click Add Employee button, modal popup show open to add employee.
3) When we click Edit button, modal popup show open to edit employee
Project Solution is name as SampleMvc.
In SQLHELPER Class : Inherited by data access class.
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Configuration;
namespace SampleMvc.DA
{
/// <summary>
/// Summary description for Class_SQLHelper
/// </summary>
public class SQLHelper
{
private SqlCommand cmd = new SqlCommand();
private SqlConnection conn;
private bool bool_Status;
private string str_Error;
public SQLHelper()
{
conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ToString());
}
protected void OpenConnection()
{
try
{
if (conn.State != ConnectionState.Open)
conn.Open();
}
catch (Exception ex)
{
OperationStatus = false;
ErrorMessage = "Error : " + ex.Message;
}
}
protected void CloseConnection()
{
try
{
if (conn.State != ConnectionState.Closed)
conn.Close();
}
catch (Exception ex)
{
OperationStatus = false;
ErrorMessage = "Error : " + ex.Message;
}
}
public SqlConnection Connection
{
get { return conn; }
}
public SqlCommand Command
{
get { return cmd; }
set { cmd = value; }
}
public bool OperationStatus
{
get { return bool_Status; }
set { bool_Status = value; }
}
public string ErrorMessage
{
get { return str_Error; }
set { str_Error = value; }
}
/// <summary>
/// Constructs a SqlCommand with the given parameters. This method is normally called
/// from the other methods and not called directly. But here it is if you need access
/// to it.
/// </summary>
/// <param name="qry">SQL query or stored procedure name</param>
/// <param name="type">Type of SQL command</param>
/// <param name="args">Query arguments. Arguments should be in pairs where one is the
/// name of the parameter and the second is the value. The very last argument can
/// optionally be a SqlParameter object for specifying a custom argument type</param>
/// <returns></returns>
public SqlCommand CreateCommand(string qry, CommandType type, params object[] args)
{
SqlCommand cmd = new SqlCommand(qry, conn);
OpenConnection();
// Set command type
cmd.CommandType = type;
// Construct SQL parameters
for (int i = 0; i < args.Length; i++)
{
if (args[i] is string && i < (args.Length - 1))
{
SqlParameter parm = new SqlParameter();
parm.ParameterName = (string)args[i];
parm.Value = args[++i];
cmd.Parameters.Add(parm);
}
else if (args[i] is SqlParameter)
{
cmd.Parameters.Add((SqlParameter)args[i]);
}
else throw new ArgumentException("Invalid number or type of arguments supplied");
}
return cmd;
}
/// <summary>
/// Executes a stored procedure that returns no results
/// </summary>ExecNonQueryProc
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>The number of rows affected</returns>
public int ExecNonQueryProc(string proc, params object[] args)
{
int result = 0;
//using (conn)
//{
// OpenConnection();
using (SqlCommand cmd = CreateCommand(proc, CommandType.StoredProcedure, args))
{
result= cmd.ExecuteNonQuery();
CloseConnection();
return result;
}
//}
}
/// <summary>
/// Executes a query that returns a single value
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Value of first column and first row of the results</returns>
public object ExecScalarProc(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))
{
return cmd.ExecuteScalar();
}
}
/// <summary>
/// Executes a stored procedure and returns the results as a SqlDataReader
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a SqlDataReader</returns>
public SqlDataReader ExecDataReaderProc(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))
{
return cmd.ExecuteReader();
}
}
/// <summary>
/// Executes a stored procedure and returns the results as a Data Set
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a DataSet</returns>
public DataSet ExecDataSetProc(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapt.Fill(ds);
return ds;
}
}
/// <summary>
/// Executes a stored procedure and returns the results as a Data Set
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <param name="args">Any number of parameter name/value pairs and/or SQLParameter arguments</param>
/// <returns>Results as a DataTable</returns>
public DataTable ExecDataTableProc(string qry, params object[] args)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure, args))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapt.Fill(dt);
return dt;
}
}
/// <summary>
/// Executes a stored procedure and returns the results as a Data Set
/// </summary>
/// <param name="proc">Name of stored proceduret</param>
/// <returns>Results as a DataTable</returns>
public DataTable ExecDataTableProc(string qry)
{
using (SqlCommand cmd = CreateCommand(qry, CommandType.StoredProcedure))
{
SqlDataAdapter adapt = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapt.Fill(dt);
return dt;
}
}
}
}
In Modal Class Library :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace SampleMvc.Models
{
public class Employee
{
public int EmployeeID { get; set; }
public string LastName { get; set; }
public string FirstName { get; set; }
public string Title { get; set; }
public DateTime BirthDate { get; set; }
}
}
In Data Access Class Library :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SampleMvc.Models;
using System.Data.SqlClient;
using System.Data;
namespace SampleMvc.DA
{
public class EmployeeDA : SQLHelper
{
public void AddEmployee(Employee emp)
{
try
{
int i = ExecNonQueryProc("Usp_AddEmployee",
new SqlParameter("@LastName", emp.LastName),
new SqlParameter("@FirstName", emp.FirstName),
new SqlParameter("@Title", emp.Title),
new SqlParameter("@Birthdate", emp.BirthDate));
}
catch (Exception ex)
{
ErrorMessage = "Error : " + ex.Message;
OperationStatus = false;
}
finally
{
CloseConnection();
}
}
public DataTable GetAllEmployees()
{
DataTable dt = new DataTable();
dt = ExecDataTableProc("Usp_GetAllEmployees");
return dt;
}
public DataTable GetAllEmps()
{
DataTable dt = new DataTable();
dt = ExecDataTableProc("Usp_GetAllEmps");
return dt;
}
public DataTable GetAllEmpById(Employee emp)
{
DataTable dt = new DataTable();
dt = ExecDataTableProc("Usp_GetAllEmpById", new SqlParameter("@EmployeeId", emp.EmployeeID));
return dt;
}
public DataTable UpdateEmployeeById(Employee emp)
{
DataTable dt = new DataTable();
dt = ExecDataTableProc("Usp_UploadEmployeeById",
new SqlParameter("@EmployeeId", emp.EmployeeID),
new SqlParameter("@LastName", emp.LastName),
new SqlParameter("@FirstName", emp.FirstName),
new SqlParameter("@Title", emp.Title),
new SqlParameter("@Birthdate", emp.BirthDate));
return dt;
}
public DataTable DeleteEmployeeById(Employee emp)
{
DataTable dt = new DataTable();
dt = ExecDataTableProc("Usp_DeleteEmployeeById",
new SqlParameter("@EmployeeId", emp.EmployeeID));
return dt;
}
}
}
In Controller Class :
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Web.Mvc;
using SampleMvc.DA;
using SampleMvc.Models;
using System.Web.Script.Serialization;
namespace SampleMvc.Controllers
{
public class EmpController : Controller
{
public ActionResult AddEmployee()
{
return View();
}
[HttpPost]
public ActionResult AddEmployee(Employee emp)
{
EmployeeDA employeeDA = new EmployeeDA();
employeeDA.AddEmployee(emp);
return View();
}
public ActionResult GetAllEmployees()
{
DataTable dt = new DataTable();
EmployeeDA employeeDA = new EmployeeDA();
dt = employeeDA.GetAllEmps();
String emp = ConvertDataTableTojSonString(dt);
return Json(emp);
}
public ActionResult GetEmployeeById(Employee emp)
{
DataTable dt = new DataTable();
EmployeeDA employeeDA = new EmployeeDA();
dt = employeeDA.GetAllEmpById(emp);
String _emp = ConvertDataTableTojSonString(dt);
return Json(_emp);
}
public ActionResult UpdateEmployeeById()
{
return View();
}
[HttpPost]
public ActionResult UpdateEmployeeById(Employee emp)
{
DataTable dt = new DataTable();
EmployeeDA employeeDA = new EmployeeDA();
dt = employeeDA.UpdateEmployeeById(emp);
String _emp = ConvertDataTableTojSonString(dt);
return Json(_emp);
}
public ActionResult DeleteEmployeeById()
{
return View();
}
[HttpPost]
public ActionResult DeleteEmployeeById(Employee emp)
{
DataTable dt = new DataTable();
EmployeeDA employeeDA = new EmployeeDA();
dt = employeeDA.DeleteEmployeeById(emp);
String _emp = ConvertDataTableTojSonString(dt);
return Json(_emp);
}
public String ConvertDataTableTojSonString(DataTable dataTable)
{
System.Web.Script.Serialization.JavaScriptSerializer serializer =
new System.Web.Script.Serialization.JavaScriptSerializer();
List<Dictionary<String, Object>> tableRows = new List<Dictionary<String, Object>>();
Dictionary<String, Object> row;
foreach (DataRow dr in dataTable.Rows)
{
row = new Dictionary<String, Object>();
foreach (DataColumn col in dataTable.Columns)
{
row.Add(col.ColumnName, dr[col]);
}
tableRows.Add(row);
}
return serializer.Serialize(tableRows);
}
}
}
In View AddEmployee :
@{
Layout = null;
}
<script src="~/Scripts/jquery-3.2.1.js"></script>
<script src="~/Scripts/jquery-ui.js"></script>
<link href="~/Contents/CSS/jquery-ui.css" rel="stylesheet" />
<script type="text/javascript">
$(document).ready(function () {
var dialogDiv = $('#dialog');
//Create dialog
$("#dialog .ui-dialog-titlebar").css("background-color", "red");
dialogDiv.dialog({
autoOpen: false,
modal: true,
title: 'Add Employee',
buttons: {
'Create': CreateEmployee,
'Cancel': function () {
dialogDiv.dialog('close');
clearInputFields();
}
}
});
// Functon to create employee
function CreateEmployee() {
var employee = {};
employee.LastName = $('#txtLastName').val();
employee.FirstName = $('#txtFirstName').val();
employee.Title = $('#txtTitle').val();
employee.BirthDate = $('#txtDob').val();
$.ajax({
url: 'Emp/AddEmployee',
method: 'post',
data: '{emp: ' + JSON.stringify(employee) + '}',
contentType: "application/json; charset=utf-8",
success: function () {
getAllEmployees();
dialogDiv.dialog('close');
clearInputFields();
},
error: function (err) {
alert(err);
}
});
}
// Functon to update employee by employee id
function UpdateEmployee() {
var employee = {};
employee.EmployeeID = $('#txtEmpId').val();
employee.LastName = $('#txtLastName').val();
employee.FirstName = $('#txtFirstName').val();
employee.Title = $('#txtTitle').val();
employee.BirthDate = $('#txtDob').val();
$.ajax({
url: 'Emp/UpdateEmployeeById',
method: 'post',
data: '{emp: ' + JSON.stringify(employee) + '}',
contentType: "application/json; charset=utf-8",
success: function () {
getAllEmployees();
dialogDiv.dialog('close');
clearInputFields();
},
error: function (err) {
alert(err);
}
});
}
// Functon to get all employees
function getAllEmployees() {
var tboby = $('#tblEmployee tbody');
tboby.empty();
$.ajax({
url: 'Emp/GetAllEmployees',
dataType: "json",
method: 'post',
success: function (data) {
var employeeTable = $('#tblEmployee tbody');
employeeTable.empty();
$(JSON.parse(data)).each(function (index, emp) {
employeeTable.append('<tr><td>'
+ emp.EmployeeID + '</td><td>'
+ emp.LastName + '</td><td>'
+ emp.FirstName + '</td><td>'
+ emp.Title + '</td><td>'
+ emp.BirthDate + '</td><td>'
+ '<input class="btnEdit" type="button" value="Edit" Id="' + emp.EmployeeID + '" /> '
+ '<input class="btnDelete" type="button" value="Delete" Id="btndelete_' + emp.EmployeeID + '" /> '
+ '<input class="btnView" type="button" value="View"/> </td></tr>');
// Find button click by dynamic Id to retrive data to update.
$("#" + emp.EmployeeID).click(function () {
//alert($(this).attr("Id"));
getEmployeeById($(this).attr("Id"));
});
// Find button click by dynamic Id to delete data.
$("#btndelete_" + emp.EmployeeID).click(function () {
//alert($(this).attr("Id"));
deleteEmployeeById($(this).attr("Id").replace("btndelete_", ""));
});
});
},
error: function (err) {
alert(err);
}
});
}
// Functon to clear all inputs
function clearInputFields() {
$('#dialog input[type="text"]').val('');
}
// Functon to get employee details by employee id
function getEmployeeById(eId) {
var employee = {};
employee.EmployeeId = eId;
$.ajax({
url: 'Emp/GetEmployeeById',
method: 'post',
data: '{emp: ' + JSON.stringify(employee) + '}',
contentType: "application/json; charset=utf-8",
success: function (data) {
$(JSON.parse(data)).each(function (index, emp) {
$('#txtEmpId').val(emp.EmployeeID);
$('#txtLastName').val(emp.LastName);
$('#txtFirstName').val(emp.FirstName);
$('#txtTitle').val(emp.Title);
$('#txtDob').val(emp.BirthDate);
});
// Creating dialog to edit employee
dialogDiv.dialog({
autoOpen: false,
modal: true,
title: 'Edit Employee',
buttons: {
'Update': UpdateEmployee,
'Cancel': function () {
dialogDiv.dialog('close');
clearInputFields();
}
}
});
dialogDiv.dialog('open');
},
error: function (err) {
alert(err);
}
});
}
// Function to delete employee by Id.
function deleteEmployeeById(eId) {
var employee = {};
employee.EmployeeId = eId;
if (confirm('Are you sure you want to continue?')) {
$.ajax({
url: 'Emp/DeleteEmployeeById',
method: 'post',
data: '{emp: ' + JSON.stringify(employee) + '}',
contentType: "application/json; charset=utf-8",
success: function (data) {
getAllEmployees();
dialogDiv.dialog('close');
},
error: function (err) {
alert(err);
}
});
}
}
// Functon to create dialog to add employee on Add Employee button click.
$('#btnAddEmployee').click(function () {
//Creating dialog to add employee
dialogDiv.dialog({
autoOpen: false,
modal: true,
title: 'Add Employee',
buttons: {
'Create': CreateEmployee,
'Cancel': function () {
dialogDiv.dialog('close');
clearInputFields();
}
}
});
dialogDiv.dialog("open");
});
// Calling function to get all employees on page load.
getAllEmployees();
});
</script>
<table id="tblEmployee" border="1" style="border-collapse: collapse">
<thead>
<tr>
<th>Employee ID</th>
<th>Last Name</th>
<th>First Name</th>
<th>Title</th>
<th>DOB</th>
<th>Action</th>
@* style="display:none;"*@
</tr>
</thead>
<tbody></tbody>
</table>
<br />
<br />
<input type="button" value="Add Employee" id="btnAddEmployee" />
@*Just need to set id of div to dialog in order to use jquery modal.*@
<div id="dialog">
<div class="container">
<input type="hidden" id="txtEmpId" />
Last Name<br />
<input type="text" id="txtLastName" /><br />
First Name<br />
<input type="text" id="txtFirstName" /><br />
Title
<br />
<input type="text" id="txtTitle" /><br />
DOB<br />
<input type="text" id="txtDob" />
<br />
</div>
<div class="msgConfirm" style="display:none;">
Do you want to delete this employee.?
</div>
</div>
