Part 49 - Pagination using Skip and Take method | JQuery DataTable Server side
In this video you will be able to know how to implement pagination in server side processing in JQuery Datatables.
If you are new to DataTables then please download latest version of Jquery DataTable. Click here to download the latest version of Jquery Datatable and watch my previous tutorial to get step by step DataTable plugin installation guide. you can visit here: Integrate JQuery DataTable plugin into Asp.net MVC
The expected output will be as what displayed in following image.
# View Page (Index.cshtml)
Right click on your controller' s Index method and add a view. After adding view, replace content with below code.
@model MVCTutorial.Models.EmployeeViewModel
@{
ViewBag.Title = "Index";
// Layout = null;
}
<div class="panel panel-body" style="min-height:256px">
<div class="col-md-3">
@{ Html.RenderAction("SideMenu", "Test");}
</div>
<div class="col-md-9">
<div class="well">
<a href="#" class="btn btn-primary" onclick="AddEditEmployee(0)">New</a>
</div>
<table class="display" id="MyDataTable">
<thead>
<tr>
<th>
EmaployeeName
</th>
<th>
Department
</th>
<th>
Address
</th>
<th>
EmployeeId
</th>
</tr>
</thead>
<tbody></tbody>
</table>
<div class="modal fade" id="myModal1">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<a href="#" class="close" data-dismiss="modal">×</a>
<h3 class="modal-title">AddEdit Employee</h3>
</div>
<div class="modal-body" id="myModalBodyDiv1">
</div>
</div>
</div>
</div>
<input type="hidden" id="hiddenEmployeeId" />
</div>
</div>
<script>
$(document).ready(function () {
// $("#MyDataTable").DataTable();
BindDataTable();
})
var BindDataTable = function (response) {
$("#MyDataTable").DataTable({
"bServerSide": true,
"sAjaxSource": "/Test/GetEmployeeRecord",
"fnServerData": function (sSource,aoData,fnCallback) {
$.ajax({
type: "Get",
data:aoData,
url: sSource,
success:fnCallback
})
},
"aoColumns": [
{ "mData": "Name" },
{ "mData": "DepartmentName" },
{ "mData": "Address" },
{
"mData": "EmployeeId",
"render": function (EmployeeId, type, full, meta) {
debugger
return '<a href="#" onclick="AddEditEmployee(' + EmployeeId + ')"><i class="glyphicon glyphicon-pencil"></i></a>'
}
},
]
});
}
var AddEditEmployee = function (employeeId) {
var url = "/Test/AddEditEmployee?EmployeeId=" + employeeId;
$("#myModalBodyDiv1").load(url, function () {
$("#myModal1").modal("show");
})
}
</script>
# DataTablesParam Class.
Create a class named DataTableParams and copy below code into this. This class will be used for receiving dataTables default parameters. You can remove extra properties which are not useful to you. Also, please don't forget to copy EmployeeViewModel from Part 20 of this tutorial series.
public class DataTablesParam
{
public int iDisplayStart { get; set; }
public int iDisplayLength { get; set; }
public int iColumns { get; set; }
public string sSearch { get; set; }
public bool bEscapeRegex { get; set; }
public int iSortingCols { get; set; }
public int sEcho { get; set; }
public List<string> sColumnNames { get; set; }
public List<bool> bSortable { get; set; }
public List<bool> bSearchable { get; set; }
public List<string> sSearchValues { get; set; }
public List<int> iSortCol { get; set; }
public List<string> sSortDir { get; set; }
public List<bool> bEscapeRegexColumns { get; set; }
public DataTablesParam()
{
sColumnNames = new List<string>();
bSortable = new List<bool>();
bSearchable = new List<bool>();
sSearchValues = new List<string>();
iSortCol = new List<int>();
sSortDir = new List<string>();
bEscapeRegexColumns = new List<bool>();
}
public DataTablesParam(int iColumns)
{
this.iColumns = iColumns;
sColumnNames = new List<string>(iColumns);
bSortable = new List<bool>(iColumns);
bSearchable = new List<bool>(iColumns);
sSearchValues = new List<string>(iColumns);
iSortCol = new List<int>(iColumns);
sSortDir = new List<string>(iColumns);
bEscapeRegexColumns = new List<bool>(iColumns);
}
}
# Controller Code (TestController.cs)
Create a Test controller and copy below code into this.
using MVCTutorial.Models;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Net.Mail;
using System.Text;
using System.Threading;
using System.Web;
using System.Web.Mvc;
namespace MVCTutorial.Controllers
{
public class TestController : Controller
{
public ActionResult Index()
{
MVCTutorialEntities db = new MVCTutorialEntities();
List<Department> list = db.Departments.ToList();
ViewBag.DepartmentList = new SelectList(list, "DepartmentId", "DepartmentName");
List<EmployeeViewModel> listEmp = db.Employees.Where(x => x.IsDeleted == false).Select(x => new EmployeeViewModel { Name = x.Name, DepartmentName = x.Department.DepartmentName, Address = x.Address, EmployeeId = x.EmployeeId }).ToList();
ViewBag.EmployeeList = listEmp;
return View();
}
public ActionResult SideMenu()
{
return PartialView("SideMenu");
}
public JsonResult GetEmployeeRecord(DataTablesParam param)
{
MVCTutorialEntities db = new MVCTutorialEntities();
List<EmployeeViewModel> List = new List<EmployeeViewModel>();
int pageNo = 1;
if (param.iDisplayStart >= param.iDisplayLength) {
pageNo = (param.iDisplayStart / param.iDisplayLength) + 1;
}
int totalCount = 0;
if (param.sSearch != null)
{
totalCount = db.Employees.Where(x => x.Name.Contains(param.sSearch) || x.Department.DepartmentName.Contains(param.sSearch) || x.Address.Contains(param.sSearch)).Count();
List = db.Employees
.Where(x => x.Name.Contains(param.sSearch) || x.Department.DepartmentName.Contains(param.sSearch) || x.Address.Contains(param.sSearch))
.OrderBy(x => x.EmployeeId)
.Skip((pageNo - 1) * param.iDisplayLength)
.Take(param.iDisplayLength)
.Select(x => new EmployeeViewModel
{
Name = x.Name,
EmployeeId = x.EmployeeId,
DepartmentId = x.DepartmentId,
DepartmentName = x.Department.DepartmentName,
Address = x.Address,
IsDeleted = x.IsDeleted
}).ToList();
}
else
{
totalCount = db.Employees.Count();
List = db.Employees.OrderBy(x => x.EmployeeId).Skip((pageNo - 1) * param.iDisplayLength).Take(param.iDisplayLength).Select(x => new EmployeeViewModel
{
Name = x.Name,
EmployeeId = x.EmployeeId,
DepartmentId = x.DepartmentId,
DepartmentName = x.Department.DepartmentName,
Address = x.Address,
IsDeleted = x.IsDeleted
}).ToList();
}
return Json(new
{
aaData = List,
sEcho = param.sEcho,
iTotalDisplayRecords = totalCount,
iTotalRecords = totalCount
}
, JsonRequestBehavior.AllowGet);
}
[HttpPost]
public ActionResult Index(EmployeeViewModel model)
{
try
{
MVCTutorialEntities db = new MVCTutorialEntities();
List<Department> list = db.Departments.ToList();
ViewBag.DepartmentList = new SelectList(list, "DepartmentId", "DepartmentName");
if (model.EmployeeId > 0)
{
//update
Employee emp = db.Employees.SingleOrDefault(x => x.EmployeeId == model.EmployeeId && x.IsDeleted == false);
emp.DepartmentId = model.DepartmentId;
emp.Name = model.Name;
emp.Address = model.Address;
db.SaveChanges();
}
else
{
//Insert
Employee emp = new Employee();
emp.Address = model.Address;
emp.Name = model.Name;
emp.DepartmentId = model.DepartmentId;
emp.IsDeleted = false;
db.Employees.Add(emp);
db.SaveChanges();
}
return View(model);
}
catch (Exception ex)
{
throw ex;
}
}
public ActionResult AddEditEmployee(int EmployeeId)
{
MVCTutorialEntities db = new MVCTutorialEntities();
List<Department> list = db.Departments.ToList();
ViewBag.DepartmentList = new SelectList(list, "DepartmentId", "DepartmentName");
EmployeeViewModel model = new EmployeeViewModel();
if (EmployeeId > 0)
{
Employee emp = db.Employees.SingleOrDefault(x => x.EmployeeId == EmployeeId && x.IsDeleted == false);
model.EmployeeId = emp.EmployeeId;
model.DepartmentId = emp.DepartmentId;
model.Name = emp.Name;
model.Address = emp.Address;
}
return PartialView("Partial2", model);
}
}
}
0 Response to "Part 49 - Pagination using Skip and Take method | JQuery DataTable Server side"
Post a Comment