In this article, we will discuss how to integrate the Datatable plugin in ASP.NET MVC 5 with server-side pagination and ordering, sorting and searching.
Prerequisites:
HTML
Javascript
jQuery
AJAX
Bootstrap
CSS
ASP.NET MVC 5
C# Programming
C# LINQ
Now Lets Begin:
- Create a new project and add the following JS and CSS in it.
- bootstrap.min.css
- dataTables.bootstrap.min.css
- jquery-3.3.1.min.js
- jquery.dataTables.min.js
- dataTables.bootstrap.min.js
Now First we will make the database. We are going to use the Code First Approach. If you are not familiar with it then you can see it in our article.
Add the connection string in Web.Config file in root folder.
<connectionStrings>
<add name="StringDBContext" connectionString="Server=DESKTOP-CGB025P;Initial Catalog=DemoDB;Persist Security Info=False;User ID=sa;Password=vision;MultipleActiveResultSets=True;Encrypt=False;TrustServerCertificate=False;Connection Timeout=30;" providerName="System.Data.SqlClient" />
</connectionStrings>
Now Go to Models -> Create Context.cs file
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
namespace DatatablePlugin.Models
{
public class Context : DbContext
{
public Context() : base("StringDBContext")
{
}
public DbSet<DemoTable> DemoTables { get; set; }
}
}
Now in the Models folder only create DemoTable.cs file
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;
namespace DatatablePlugin.Models
{
public class DemoTable
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public string Quantity { get; set; }
public string Price { get; set; }
public string Status { get; set; }
}
}
Now open the _Layout.cshtml file from View -> Shared -> _Layout.cshtml
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>@ViewBag.Title - Server Side Datatable Demo</title>
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<link href="~/Content/dataTables.bootstrap.min.css" rel="stylesheet" />
<script src="~/Scripts/jquery-3.3.1.min.js"></script>
<script src="~/Scripts/jquery.dataTables.min.js"></script>
<script src="~/Scripts/dataTables.bootstrap.min.js"></script>
</head>
<body>
<div class="container body-content" style="margin-top:5%">
@RenderBody()
<hr />
<footer>
<p>© @DateTime.Now.Year - Server Side Datatable Demo</p>
</footer>
</div>
@RenderSection("scripts", required: false)
</body>
</html>
The JS and CSS files should be added to the Content and Scripts folder before continuing.
Now we will add the View for the data table. So go to View -> Home -> Index.cshtml
@{
ViewBag.Title = "Home Page";
}
<table id="demodata" class="table table-bordered">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Quantity</th>
<th>Price</th>
<th>Status</th>
</tr>
</thead>
<tbody></tbody>
</table>
<script src="~/Scripts/Index.js"></script>
Now make the Index.js file in the Scripts folder and add the code in it.
DemoDatatable();
function DemoDatatable() {
$("#demodata").DataTable({
"processing": true,
"serverSide": true,
"filter": true,
"orderMulti": false,
"destroy": true,
"ordering": true,
"ajax": {
"url": '/Home/GetTableData',
"type": "POST",
"datatype": "json"
},
"columns": [
{ "data": "Id", "name": "Id", "autoWidth": true }
, { "data": "Name", "Name": "Name", "autoWidth": true }
, { "data": "Quantity", "name": "Quantity", "autoWidth": true }
, { "data": "Price", "name": "Price", "autoWidth": true }
, { "data": "Status", "name": "Status", "autoWidth": true }
]
});
}
Now finally the main login for server-side pagination and ordering begins.
Now go-to HomeController.
using DatatablePlugin.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
namespace DatatablePlugin.Controllers
{
public class HomeController : Controller
{
private readonly Context _context = new Context();
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult GetTableData()
{
JsonResult result = new JsonResult();
try
{
string search = Request.Form.GetValues("search[value]")[0];
string draw = Request.Form.GetValues("draw")[0];
string order = Request.Form.GetValues("order[0][column]")[0];
string orderDir = Request.Form.GetValues("order[0][dir]")[0];
int startRec = Convert.ToInt32(Request.Form.GetValues("start")[0]);
int pageSize = Convert.ToInt32(Request.Form.GetValues("length")[0]);
List<DemoTable> data = _context.DemoTables.ToList();
int totalRecords = data.Count;
if (!string.IsNullOrEmpty(search) &&
!string.IsNullOrWhiteSpace(search))
{
data = data.Where(p => p.Id.ToString().ToLower().Contains(search.ToLower()) ||
p.Name.ToString().Contains(search.ToLower()) ||
p.Quantity.ToString().Contains(search.ToLower()) ||
p.Price.ToString().Contains(search.ToLower()) ||
p.Status.ToString().Contains(search.ToLower())
).ToList();
}
data = SortTableData(order, orderDir, data);
int recFilter = data.Count;
data = data.Skip(startRec).Take(pageSize).ToList();
var modifiedData = data.Select(d =>
new
{
d.Id,
d.Name,
d.Quantity,
d.Price,
d.Status
}
);
result = this.Json(new
{
draw = Convert.ToInt32(draw),
recordsTotal = totalRecords,
recordsFiltered = recFilter,
data = modifiedData
}, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
Console.Write(ex);
}
return result;
}
private List<DemoTable> SortTableData(string order, string orderDir, List<DemoTable> data)
{
List<DemoTable> lst = new List<DemoTable>();
try
{
switch (order)
{
case "0":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList()
: data.OrderBy(p => p.Id).ToList();
break;
case "1":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Name).ToList()
: data.OrderBy(p => p.Name).ToList();
break;
case "2":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Quantity).ToList()
: data.OrderBy(p => p.Quantity).ToList();
break;
case "3":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Price).ToList()
: data.OrderBy(p => p.Price).ToList();
break;
case "4":
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Status).ToList()
: data.OrderBy(p => p.Status).ToList();
break;
default:
lst = orderDir.Equals("DESC", StringComparison.CurrentCultureIgnoreCase) ? data.OrderByDescending(p => p.Id).ToList()
: data.OrderBy(p => p.Id).ToList();
break;
}
}
catch (Exception ex)
{
Console.Write(ex);
}
return lst;
}
}
}
Finally Done with the Login for server-side data table pagination and all the kinds of stuff.
Output:
You can download this demo from here.
