Hi iammann,
Here i have created sample using jQuery without entity framework.
For this sample i have used NorthWind database which you can get by clicking on the link below.
Download Northwind Database
Or you can create the table like below.
CREATE TABLE Employees
(
EmployeeID INT IDENTITY(1,1) NOT NULL,
LastName NVARCHAR(20) NOT NULL,
FirstName NVARCHAR(10) NOT NULL,
City NVARCHAR(15) NULL,
ReportsTo INT NULL
)
Now follow the below steps.
Step 1: As usual create a MVC project.
Step 2: Create Model Class by right clicking on model folder. Here i have created with the name Employee.
public class Employee
{
public int EmployeeID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public string City { get; set; }
public int? ReportsTo { get; set; }
}
Step 3 : Create Controller. Here i have created with the name Home.
public class HomeController : Controller
{
string str = ConfigurationManager.ConnectionStrings[1].ConnectionString;
public List<Employee> GetEmployees()
{
List<Employee> employees = new List<Employee>();
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = str;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "SELECT TOP 10 EmployeeID,LastName,FirstName,City,ReportsTo FROM Employees ORDER BY EmployeeID DESC";
con.Open();
using (SqlDataReader sdr = cmd.ExecuteReader())
{
if (sdr.HasRows)
{
while (sdr.Read())
{
Employee emp = new Employee()
{
EmployeeID = Convert.ToInt32(sdr["EmployeeId"]),
FirstName = sdr["FirstName"].ToString(),
LastName = sdr["LastName"].ToString(),
City = sdr["City"].ToString(),
ReportsTo = Convert.ToInt32(sdr["ReportsTo"] == DBNull.Value ? null : sdr["ReportsTo"])
};
employees.Add(emp);
}
}
}
con.Close();
}
}
return employees;
}
public void Add(Employee employee)
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = str;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "INSERT INTO Employees(LastName,FirstName,City,ReportsTo) VALUES(@LastName,@FirstName,@City,@ReportsTo)";
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.Parameters.AddWithValue("@City", employee.City);
cmd.Parameters.AddWithValue("@ReportsTo", employee.ReportsTo);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
public void Update(Employee employee)
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = str;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "UPDATE Employees SET LastName = @LastName,FirstName = @FirstName,City = @City WHERE EmployeeID = @EmployeeID";
cmd.Parameters.AddWithValue("@EmployeeID", employee.EmployeeID);
cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
cmd.Parameters.AddWithValue("@LastName", employee.LastName);
cmd.Parameters.AddWithValue("@City", employee.City);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
public void Delete(int id)
{
using (SqlConnection con = new SqlConnection())
{
con.ConnectionString = str;
using (SqlCommand cmd = new SqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "DELETE FROM Employees WHERE EmployeeID = @EmployeeID";
cmd.Parameters.AddWithValue("@EmployeeID", id);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
}
}
}
public ActionResult Index()
{
return View();
}
public JsonResult GetAllEmployees()
{
return Json(GetEmployees().ToList(), JsonRequestBehavior.AllowGet);
}
public ActionResult AddEmployee()
{
return View();
}
[HttpPost]
public JsonResult AddEmployee(Employee emp)
{
try
{
Add(emp);
return Json("Records added Successfully.");
}
catch
{
return Json("Records not added,");
}
}
public ActionResult UpdateEmployee(int? id)
{
return View(GetEmployees().Find(e => e.EmployeeID == id));
}
[HttpPost]
public JsonResult UpdateEmployee(Employee emp)
{
Update(emp);
return Json("Records updated successfully.", JsonRequestBehavior.AllowGet);
}
[HttpPost]
public JsonResult DeleteEmployee(int id)
{
Delete(id);
return Json("Records deleted successfully.", JsonRequestBehavior.AllowGet);
}
[HttpGet]
public PartialViewResult EmployeeDetails()
{
return PartialView("_EmployeeDetails");
}
}
Step 4: Create Views.
To view the employee details let us create the partial view named _EmployeeDetails inside Home folder.
_EmployeeDetails.cshtml
<script type="text/javascript">
$(document).ready(function () {
var tr;
$.getJSON("/Home/GetAllEmployees", function (json) {
$.each(json, function (i, emp) {
var empid = emp.EmployeeID;
tr = $('<tr/>');
tr.append("<td class='FirstName'>" + emp.FirstName + "</td>");
tr.append("<td class='LastName'>" + emp.LastName + "</td>");
tr.append("<td class='City'>" + emp.City + "</td>");
tr.append("<td class='ReportsTo'>" + emp.ReportsTo + "</td>");
tr.append("<td>" + "<a Onclick='return false;' class='DeleteCss' href=/Home/DeleteEmployee/" + empid + ">Delete</a>" + " | " + "<a class='EditCss' href=/Home/UpdateEmployee/" + empid + ">Edit</a>" + "</td>");
$('#tblEmployee').append(tr);
});
});
$('#tblEmployee').on('click', 'td a.DeleteCss', function () {
var deleteUrl = $(this).attr("href");
if (confirm("Are you sure wants to delete ?.")) {
$.ajax({
url: deleteUrl, dataType: "json", type: "POST", contentType: "application/json",
error: function (err) { alert('Unable to delete record.'); },
success: function (response) { $('#employeeList').load("/Home/EmployeeDetails"); }
});
}
});
});
</script>
<table id="tblEmployee" class="table table-bordered table-hover">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>City</th>
<th>Report To</th>
<th>Action</th>
</tr>
</thead>
<tbody></tbody>
</table>
To create the View for add and update, right click on view folder and then click Add view. Now specify the view name as I have AddEmployee/UpdateEmployee or as you wish, select appropriate template name(For AddEmployee select Create, for UpdateEmployee select Edit) and select model class(Employee (CrudWithADONetAjaxjQuery.Models)) and click on Add button.
AddEmployee.cshtml
@model CrudWithADONetAjaxjQuery.Models.Employee
@{
ViewBag.Title = "Add Employee";
}
<h2>Add Employee</h2>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
<script>
$(document).ready(function () {
$("#btnAdd").click(function () {
var employee =
{
FirstName: $("#FirstName").val(),
LastName: $("#LastName").val(),
City: $("#City").val(),
ReportsTo: $("#ReportsTo").val()
};
$.ajax({
type: "POST", URL: "/Home/AddEmployee", dataType: "json", contentType: "application/json",
data: JSON.stringify({ emp: employee }),
success: function (response) { $('#employeeList').load("/Home/EmployeeDetails"); },
error: function (response) { alert(response.responseText); }
});
});
});
</script>
<div class="form-horizontal">
<h4></h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.ReportsTo, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.ReportsTo, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.ReportsTo, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" id="btnAdd" value="Add" class="btn btn-default" />
</div>
</div>
<div class="form-group" id="employeeList">
<div class="col-md-12">
@Html.Partial("_EmployeeDetails")
</div>
</div>
</div>
UpdateEmployee.cshtml
@model CrudWithADONetAjaxjQuery.Models.Employee
@{
ViewBag.Title = "Update Employee";
}
<h2>Update Employee</h2>
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
<script>
$(document).ready(function () {
$("#btnUpdate").click(function () {
var employee =
{
EmployeeID: $("#hfEmployeeID").val(),
FirstName: $("#FirstName").val(),
LastName: $("#LastName").val(),
City: $("#City").val()
};
$.ajax({
type: "POST", URL: "/Home/UpdateEmployee", dataType: "json", contentType: "application/json",
data: JSON.stringify({ emp: employee }),
success: function (response) { window.location.href = "/Home/AddEmployee"; },
error: function (response) { alert(response.responseText); }
});
});
});
</script>
<div class="form-horizontal">
<h4></h4>
<hr />
@Html.ValidationSummary(true, "", new { @class = "text-danger" })
@Html.HiddenFor(model => model.EmployeeID, new { @id = "hfEmployeeID" })
<div class="form-group">
@Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
@Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })
<div class="col-md-10">
@Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })
@Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })
</div>
</div>
<div class="form-group">
<div class="col-md-offset-2 col-md-10">
<input type="submit" id="btnUpdate" value="Update" class="btn btn-default" />
</div>
</div>
</div>
Step 9: Now Run the Application.
