Adding Employee and Retrieve it and show in table using Ajax in ASP.NET MVC
Application Name : SampleMVC
Database : NorthwindDb
Class Library : SampleMVC.DA
Class Library : SampleMVC.Models
Step 1 : Adding SQLHelper.cs Class in Class Library SampleMVC.DA
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 (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;
}
}
}
}
Step 3 : Build And Add Class Libraries in SampleMVC
Step 4 : Adding EmployeeController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using SampleMvc.Models;
using SampleMvc.DA;
using System.Data;
using System.Web.Script.Serialization;
namespace SampleMvc.Controllers
{
public class EmployeeController : 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.GetAllEmployees();
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);
}
}
}
Application Name : SampleMVC
Database : NorthwindDb
Class Library : SampleMVC.DA
Class Library : SampleMVC.Models
Step 1 : Adding SQLHelper.cs Class in Class Library SampleMVC.DA
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 (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;
}
}
}
}
Step 2 : Adding Class EmployeeDA.cs in Class Library SampleMVC.DA and inheriting it with SQLHelper.cs and call required function of SQLHelper Class.
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;
}
}
}
Step 3 : Build And Add Class Libraries in SampleMVC
Step 4 : Adding EmployeeController.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using SampleMvc.Models;
using SampleMvc.DA;
using System.Data;
using System.Web.Script.Serialization;
namespace SampleMvc.Controllers
{
public class EmployeeController : 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.GetAllEmployees();
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);
}
}
}
Step 5 : Adding AddEmployee view
@{
Layout = null;
}
<script src="~/Scripts/jquery-3.2.1.js"></script>
<script>
$(document).ready(function () {
getAllEmployees();
$('#btnAddEmployee').click(function () {
var employee = {};
employee.LastName = $('#txtLastName').val();
employee.FirstName = $('#txtFirstName').val();
employee.Title = $('#txtTitle').val();
employee.BirthDate = $('#txtDob').val();
$.ajax({
url: 'Employee/AddEmployee',
method: 'post',
data: '{emp: ' + JSON.stringify(employee) + '}',
contentType: "application/json; charset=utf-8",
success: function () {
getAllEmployees();
},
error: function (err) {
alert(err);
}
});
});
function getAllEmployees() {
$.ajax({
url: 'Employee/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.LastName + '</td><td>'
+ emp.FirstName + '</td><td>' + emp.Title
+ '</td><td>' + emp.BirthDate + '</td></tr>');
});
},
error: function (err) {
alert(err);
}
});
}
});
</script>
<div style="padding: 30px;">
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 />
<br />
<input type="button" value="Save" id="btnAddEmployee" />
</div>
@*Table to display data *@
<table id="tblEmployee" border="1" style="border-collapse:collapse">
<thead>
<tr>
<th>Last Name</th>
<th>First Name</th>
<th>Title</th>
<th>DOB</th>
</tr>
</thead>
<tbody></tbody>
</table>
No comments:
Post a Comment