In one of the previous article, I have mentioned Creating Gridview in ASP.Net Core MVC but in this article, I have mentioned how to use jQuery datatable server side processing with searching, sorting and paging in ASP.NET Core MVC.
Server side processing of jQuery datatable can be useful when you have lots of rows in database table (Example more than 10000 rows) and want to fetch only few rows at a time, since we cannot fetch all 10000 rows in single call, then using server side approach we only show certain number (10/15) rows at a time to user, which improves page load time, redeuces the CPU and bandwidth usage.
Step 1: Create a new ASP.NET Core MVC (Model View Controller) project in Visual Studio, I am using VS 2019 with .NET 5 Version, also we will be using AdventureWorks2012 database, which you can download and install locally.
Step 2: Install Nuget packages for 'Microsoft.AspNetCore.Mvc.NewtonsoftJson' and 'Microsoft.EntityFrameworkCore.SqlServer'
You can open Nuget Package manager by navigating to "Tools" -> "Nuget Package Manager" -> "Manage Nuget package for solution" -> Search for 'Microsoft.AspNetCore.Mvc.NewtonsoftJson' and Install 'Microsoft.AspNetCore.Mvc.NewtonsoftJson Version 5' if you are using .NET Core 5 (depening on your .NET core version, I am installing Version 5 since I am using .NET 5)
Similarly, you need to install 'Microsoft.EntityFrameworkCore.SqlServer' package.
Step 3: Once you have installed above NuGet Packages, you can add database connection string appsettings.json file.
"ConnectionStrings": {
"MyConnection": "Data Source=VKS-PC-NEW\\SQLEXPRESS;Initial Catalog=AdventureWorks2012;Integrated security=true"
}Now, navigate to Startup.cs -> ConfigureServices method to add below C# Code to use Database
string conStr = this.Configuration.GetConnectionString("MyConnection");
services.AddDbContext<DBcontext>(options => options.UseSqlServer(conStr));So it would look like below:
public void ConfigureServices(IServiceCollection services)
{
services.AddControllersWithViews();
string conStr = this.Configuration.GetConnectionString("MyConnection");
services.AddDbContext<DBcontext>(options => options.UseSqlServer(conStr));
}Step 4: Add DBcontext .cs file, in your project
using Microsoft.EntityFrameworkCore;
using System.ComponentModel.DataAnnotations.Schema;
namespace jQueryDatatableASPNetCore
{
public class DBcontext : DbContext
{
public DBcontext(DbContextOptions<DBcontext> options) : base(options)
{
}
public DbSet<Person> Person { get; set; }
}
[Table("Person", Schema = "Person")] // to refer table Person.Person in AdventureWorks2012 DB
[Keyless] //as not loading PrimaryKey for this table
public class Person
{
public string FirstName { get; set; }
public string LastName { get; set; }
}
}In the above code, we are creating a Dbcontext class, which will help us connect to database.
We are also creating "Person" class (which in generally should be seperate file, but we are using it in same file for example purpose).
Since, we will be using AdventureWorks2012 database table Person.Person in this example, we have mentioned it using
[Table("Person", Schema = "Person")]code as DataAnnotations above Person Class.
Step 5: Now, we will need to create C# code in Controller to fetch data from the database using DbContext class and pass it as JSON to jquery datatable.
We will also be filtering, sorting and searching data based on the Request Content of jQuery datatable.
using Microsoft.AspNetCore.Mvc;
using System;
using System.Linq;
namespace jQueryDatatableASPNetCore.Controllers
{
public class HomeController : Controller
{
private DBcontext Context { get; }
public HomeController(DBcontext _context)
{
this.Context = _context;
}
public IActionResult Index()
{
return View();
}
[HttpPost]
public IActionResult Person()
{
try
{
var draw = Request.Form["draw"].FirstOrDefault(); // get total page size
var start = Request.Form["start"].FirstOrDefault(); // get starte length size from request.
var length = Request.Form["length"].FirstOrDefault();
var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();
var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();
var searchValue = Request.Form["search[value]"].FirstOrDefault(); // check if there is any search characters passed
int pageSize = length != null ? Convert.ToInt32(length) : 0;
int skip = start != null ? Convert.ToInt32(start) : 0;
int recordsTotal = 0;
var personData = (from tempPerson in this.Context.Person select tempPerson); // get data from database
//check for sorting column number and direction
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))
{
//get sorting column
Func<Person, string> orderingFunction = (c => sortColumn == "First Name" ? c.FirstName : sortColumn == "Last Name" ? c.LastName : c.FirstName);
//check sort order
if(sortColumnDirection == "desc")
{
personData = personData.OrderByDescending(orderingFunction).AsQueryable();
}
else
{
personData = personData.OrderBy(orderingFunction).AsQueryable();
}
}
// if there is any search value, filter results
if (!string.IsNullOrEmpty(searchValue))
{
personData = personData.Where(m => m.FirstName.ToLower().Contains(searchValue.ToLower())
|| m.LastName.ToLower().Contains(searchValue.ToLower()));
}
// get total records acount
recordsTotal = personData.Count();
//get page data
var data = personData.Skip(skip).Take(pageSize).ToList();
var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
return Ok(jsonData);
}
catch (Exception ex)
{
throw;
}
}
}
}
I have commented out some important lines of code, we are using Controller Constructor to initialize DBContext
Once, we have data we are using Person() method to fetch and return results.
Step 6: This is the final step, to create HTML table and call jQuery datatable to load data from server side, so navigate to Views -> Home -> Index.cshtml
@{
ViewData["Title"] = "Home Page";
}
<link href="https://cdn.datatables.net/1.10.20/css/jquery.dataTables.css" rel="stylesheet" type="text/css" />
<div style="width: 500px">
<table id="tblEmp" cellpadding="0" cellspacing="0" border="1" style="border-collapse:collapse">
<thead>
<tr>
<th>First Name</th>
<th>Last Name</th>
</tr>
</thead>
<tbody></tbody>
</table>
</div>
@section Scripts{
<script type="text/javascript" src="https://cdn.datatables.net/1.10.20/js/jquery.dataTables.min.js"></script>
<script type="text/javascript">
$(document).ready(function () {
$("#tblEmp").DataTable({
"processing": true,
"serverSide": true, // enabling server side
"filter": true, //set true for enabling searching
"ajax": {
"url": "/home/person",// ajax url to load content
"type": "POST", // type of method to call
"datatype": "json" // return datatype
},
"columns": [
{ "data": "firstName", "name": "First Name", "autoWidth": true }, // columns name and related settings
{ "data": "lastName", "name": "Last Name", "autoWidth": true },
]
});
});
</script>
}
As you will see in the above code, we are loading jQuery datatable using CDN links.
Since we are using MVC built-in template, we haven't loaded jQuery explicitly, since it is already included in bundle.
We have also created, a basic HTML table with 2 columns and have called jQuery datatable on page load with AJAX to get data from controller.
Once you will build and run the project in the browser, you will see output like below:
That's it, hope it helps.
You may also like to read:
jQuery Datatable server-side processing in ASP.NET MVC
jQuery Datatable Server side processing in ASP.NET Web API
Large file upload with Progress bar in ASP.NET Core (using Tus)
Buy us a coffee
Become a Patron