Introduction
In the previous article of this series "Implementing jQuery Datatables in ASP.NET MVC application", we have seen followings.
Where we have used Datatables plugin to basically display data with various features.
Because a lot of requests from readers to me to write a tutorial for implement CRUD operation in Datatables, today I am going to show you how we can implement CRUD operation in Datatables.
- Implement jQuery Datatable in ASP.NET MVC application
- jQuery Datatable server side pagination and sorting in ASP.NET MVC
- Implement custom multicolumn server-side filtering in jQuery dataTables
- Full CRUD operation using datatables in ASP.NET MVC
Where we have used Datatables plugin to basically display data with various features.
Because a lot of requests from readers to me to write a tutorial for implement CRUD operation in Datatables, today I am going to show you how we can implement CRUD operation in Datatables.
We know that Datatable is free but it's editor, which is used for CRUD operations are paid. Hence in this article, we will implement CRUD operation in Datatable using jquery modal popup.
Ok, Let's start implementing CRUD operation in jQuery Datatable.
Follow the following steps in order to implement "CRUD operation using Datatables in ASP.NET MVC".
Here In this article, I have used Visual Studio 2015
Step - 1: Create New Project.
Go to File > New > Project > ASP.NET Web Application (under web) > Enter enter application name > select your project location > and then click on add button > It will brings up a new dialog window for select template > here I will select Empty template > checked MVC checkbox from Add folder and core referances for: > and then click on ok button.
Step-2: Add a Database.
Now I will create a database for our application. As this is a tutorial project, I will add a database in our applications here in the app_data folder.
Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.
Go to Solution Explorer > Right Click on App_Data folder > Add > New item > Select SQL Server Database Under Data > Enter Database name > Add.
Step-3: Create a table in our database.
In this example, I have added the table for store Employee information for implementing CRUD operation in Datatable.
double click on the database under app_data folder for open the database in server explorer > expand the database and Right click on Tables node > click on Add New Table > here we will write schema of the table for the table we want to create > now click on Update button for create the table and then again click on Update Database button.
Step-4: Add Entity Data Model.
Go to Solution Explorer > Right Click on Project name form Solution Explorer > Add > New item > Select ADO.net Entity Data Model under data > Enter model name > Add.
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.
As I have already shown you how we can implement server-side paging, sorting, filtering functionality in Datatables, here we will only focus on CRUD functionality. So here we will show data in Datatables without implementing server-side paging, sorting, filtering functionality for the sake of simplicity.
In this page, first of all, we need to add required the jQuery & CSS libraries. So we will add those required libraries in our application from NuGet package manager.
For add libraries from NuGet package manager
Go to solution explorer > Right Click on your project name > Manage NuGet Packages... > Search & select the resources you want to Add in your project > click Install button.
We will add following libraries from NuGet Package Manager
A popup window will come (Entity Data Model Wizard) > Select Generate from database > Next >
Chose your data connection > select your database > next > Select tables > enter Model Namespace > Finish.
As I have already shown you how we can implement server-side paging, sorting, filtering functionality in Datatables, here we will only focus on CRUD functionality. So here we will show data in Datatables without implementing server-side paging, sorting, filtering functionality for the sake of simplicity.
Step-5: Create a Controller.
Go to Solution Explorer > Right Click on Controllers folder form Solution Explorer > Add > Controller > Enter Controller name > Select Templete "empty MVC Controller"> Add.
Here I have created a controller "HomeController"
Now we will add a view for Index action (which is already added in the home controller) where we will show data in Datatables.
Step-6: Add view for Index action.
Right Click on Action Method (here right click on Index action) > Add View... > Enter View Name > Select "Empty" under Template dropdown > > Add.In this page, first of all, we need to add required the jQuery & CSS libraries. So we will add those required libraries in our application from NuGet package manager.
For add libraries from NuGet package manager
Go to solution explorer > Right Click on your project name > Manage NuGet Packages... > Search & select the resources you want to Add in your project > click Install button.
We will add following libraries from NuGet Package Manager
- jQuery Library (I have downloaded jQuery 3.1.1)
- jQuery.UI.Combined 1.12.1
- jQuery.Validation 1.16.0
- Microsoft.jQuery.Unobtrusive.Validation 3.2.3
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.13/css/jquery.dataTables.min.css" />
<link href="~/Content/themes/base/jquery-ui.min.css" rel="stylesheet" />
<style>
span.field-validation-error {
color: red;
}
</style>
</head>
<body>
<div style="width:90%; margin:0 auto" class="tablecontainer">
<a class="popup btn btn-primary" href="/home/save/0" style="margin-bottom:20px; margin-top:20px;">Add New Employee</a>
<table id="myDatatable">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
<th>Email ID</th>
<th>City</th>
<th>Country</th>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
</table>
</div>
<script src="~/Scripts/jquery-3.1.1.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>
<script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>
<script src="~/Scripts/jquery-ui-1.12.1.min.js"></script>
<script>
$(document).ready(function () {
var oTable = $('#myDatatable').DataTable({
"ajax": {
"url" : '/home/GetEmployees',
"type" : "get",
"datatype" : "json"
},
"columns": [
{ "data": "FirstName", "autoWidth": true },
{ "data" : "LastName", "autoWidth" : true},
{ "data": "EmailID", "autoWidth": true },
{ "data": "City", "autoWidth": true },
{ "data": "Country", "autoWidth": true },
{
"data": "EmployeeID", "width": "50px", "render": function (data) {
return '<a class="popup" href="/home/save/'+data+'">Edit</a>';
}
},
{
"data": "EmployeeID", "width": "50px", "render": function (data) {
return '<a class="popup" href="/home/delete/' + data + '">Delete</a>';
}
}
]
})
$('.tablecontainer').on('click', 'a.popup', function (e) {
e.preventDefault();
OpenPopup($(this).attr('href'));
})
function OpenPopup(pageUrl) {
var $pageContent = $('<div/>');
$pageContent.load(pageUrl, function () {
$('#popupForm', $pageContent).removeData('validator');
$('#popupForm', $pageContent).removeData('unobtrusiveValidation');
$.validator.unobtrusive.parse('form');
});
$dialog = $('<div class="popupWindow" style="overflow:auto"></div>')
.html($pageContent)
.dialog({
draggable : false,
autoOpen : false,
resizable : false,
model : true,
title:'Popup Dialog',
height : 550,
width : 600,
close: function () {
$dialog.dialog('destroy').remove();
}
})
$('.popupWindow').on('submit', '#popupForm', function (e) {
var url = $('#popupForm')[0].action;
$.ajax({
type : "POST",
url : url,
data: $('#popupForm').serialize(),
success: function (data) {
if (data.status) {
$dialog.dialog('close');
oTable.ajax.reload();
}
}
})
e.preventDefault();
})
$dialog.dialog('open');
}
})
</script>
</body>
</html>
Step-7: Add a new method in HomeController.
Here I have added a method GetEmployees for fetch employees data from the database.
public ActionResult GetEmployees()
{
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
var employees = dc.Employees.OrderBy(a => a.FirstName).ToList();
return Json(new { data = employees }, JsonRequestBehavior.AllowGet);
}
}
Now for doing CRUD operation, we will add some MVC action in our Home Controller.
Step-8: Add a new MVC Action Save (GET) in HomeController.
Here we will add a new MVC Action (GET Method) for getting the form for add/update employee information.
[HttpGet]
public ActionResult Save(int id)
{
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
var v = dc.Employees.Where(a => a.EmployeeID == id).FirstOrDefault();
return View(v);
}
}
Step-9: Add a partial view for that Save MVC action.
Here we will add a partial view for the save action as we will open the form in jQuery popup dialog window.
@model DatatableCRUD.Models.Employee
<h2>Save</h2>
@using (Html.BeginForm("save","home", FormMethod.Post, new { id= "popupForm" }))
{
if (Model != null && Model.EmployeeID > 0)
{
@Html.HiddenFor(a=>a.EmployeeID)
}
<div class="form-group">
<label>First Name</label>
@Html.TextBoxFor(a=>a.FirstName,new { @class = "form-control" })
@Html.ValidationMessageFor(a=>a.FirstName)
</div>
<div class="form-group">
<label>Last Name</label>
@Html.TextBoxFor(a => a.LastName, new { @class = "form-control" })
@Html.ValidationMessageFor(a => a.LastName)
</div>
<div class="form-group">
<label>Email</label>
@Html.TextBoxFor(a => a.EmailID, new { @class = "form-control" })
@Html.ValidationMessageFor(a => a.EmailID)
</div>
<div class="form-group">
<label>City</label>
@Html.TextBoxFor(a => a.City, new { @class = "form-control" })
@Html.ValidationMessageFor(a => a.City)
</div>
<div class="form-group">
<label>Country</label>
@Html.TextBoxFor(a => a.Country, new { @class = "form-control" })
@Html.ValidationMessageFor(a => a.Country)
</div>
<div>
<input type="submit" value="Save" />
</div>
}
Right click inside the save action > Add View.. > Enter view name > Select Empty (without model) from template dropdown > Check the Checkbox "Create as a partial view".
Step-10: Add an another MVC Action Save (POST) in HomeController.
Here we will add a another MVC Action (POST Method) for saving data to the database.
[HttpPost]
public ActionResult Save(Employee emp)
{
bool status = false;
if (ModelState.IsValid)
{
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
if (emp.EmployeeID > 0)
{
//Edit
var v = dc.Employees.Where(a => a.EmployeeID == emp.EmployeeID).FirstOrDefault();
if (v != null)
{
v.FirstName = emp.FirstName;
v.LastName = emp.LastName;
v.EmailID = emp.EmailID;
v.City = emp.City;
v.Country = emp.Country;
}
}
else
{
//Save
dc.Employees.Add(emp);
}
dc.SaveChanges();
status = true;
}
}
return new JsonResult { Data = new { status = status} };
}
Step-11: Add an another MVC Action Delete (GET) in HomeController.
Here we will add an another MVC Action (GET Method) for getting the confirm dialog window before deleting the selected employee.
[HttpGet]
public ActionResult Delete(int id)
{
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
var v = dc.Employees.Where(a => a.EmployeeID == id).FirstOrDefault();
if (v != null)
{
return View(v);
}
else
{
return HttpNotFound();
}
}
}
Step-12: Add a partial view for that Delete MVC action.
Right click inside the save action > Add View.. > Enter view name > Select Empty (without model) from template dropdown > Check the Checkbox "Create as a partial view".
@model DatatableCRUD.Models.Employee
<h2>Delete Employee</h2>
@using (Html.BeginForm("delete","home", FormMethod.Post,new { id= "popupForm" }))
{
@Html.HiddenFor(a=>a.EmployeeID)
<div class="form-group">
<label>First Name</label>
<p>@Model.FirstName</p>
</div>
<div class="form-group">
<label>Last Name</label>
<p>@Model.LastName</p>
</div>
<div class="form-group">
<label>Email</label>
<p>@Model.EmailID</p>
</div>
<div class="form-group">
<label>City</label>
<p>@Model.City</p>
</div>
<div class="form-group">
<label>Country</label>
<p>@Model.Country</p>
</div>
<div>
<input type="submit" value="Delete" />
</div>
}
Step-13: Add an another MVC Action Delete (POST) in HomeController.
Here in this MVC Action, we will write code for delete selected employee data from our database.
[HttpPost]
[ActionName("Delete")]
public ActionResult DeleteEmployee(int id)
{
bool status = false;
using (MyDatabaseEntities dc = new MyDatabaseEntities())
{
var v = dc.Employees.Where(a => a.EmployeeID == id).FirstOrDefault();
if (v != null)
{
dc.Employees.Remove(v);
dc.SaveChanges();
status = true;
}
}
return new JsonResult { Data = new { status = status} };
}
