JQuery Datatable in ASP.NET Core - Server-Side Processing

In this article, we will learn how to use JQuery Datatable in ASP.NET Core with Server Side Processing. We will also be building a simple real-world implementation to help understand JQuery Datatable to it’s fullest. You can find the source code of the entire implementation here. Let’s begin.

What is JQuery Datatable?

JQuery Datatable is one of the most popular jQuery plugins that help create HTML tables and add a whole lot of interactive events to them. The Core Features that come along with JQuery Datatable are searching, sorting, pagination, JSON formatted source data, blazing-fast load times, server-side processing, client-side processing, and more. That said, it a highly flexible and powerful plugin.

Server Side Processing vs Client Side Processing

In context with our tutorial, this is one of the most important concept in JQuery Datatable.

Let’s say we have an API endpoint that returns a list of Customers. JQuery Datatable works by calling this API endpoint using AJAX calls. Now, when the Datatable calls the API, the API can return the N number of items, right? The N factor is quite crucial when it comes to the performance of your application.

If N was, let’s say 100-1000, it can be easily handled by the Browser. In this case, what happens, the API sends back all the Customer records (~1000 Records) in JSON format directly to the JQuery Datatable which then takes it as a data source and ultimately renders the HTML Table. This is known as client-side processing.

But what happens when the N factor is like 10,000 or more?

In cases where the number of records present in the Customer SQL Table is way above 10,000 or more, it is not ideal to send all the data back to the browser, right? What would the browser do with 10,000 records at a single time? That’s a wastage of resources and bandwidth. What can be a better approach? Apply Paging on the Server Side, right?

What it exactly means is, You do not have to send 10,000 records in one go from the API to the Browser. Instead, send the 10,000 records in chunks, in small paged units that may contain like 50 records a page. With this approach, you are drastically improving the load time (as the JQuery Datatable is loading just ~50 records instead of 10,000+ records), reducing the CPU and bandwidth usage. So, with every page, the JQuery Datatable would request for the next set of ~50 records. Get the advantage? So, this approach is also known as Server-Side Processing in JQuery Datatable.

Now, there is nothing against Client-Side Processing. You would still want to use Client-Side Processing if you think that your data record count does not go above 5,000 or 10,000. If you have less than 5,000 records, Client-Side Processing gets the job done efficiently. But when the Records count cannot be anticipated or has the potential to reach thousands or even Millions, it is very much advisable to use Server Side Processing.

In this article we will be implementing Server Side Processing in our ASP.NET Core Application.

Why do you need JQuery Datatable in ASP.NET Core?

A quick answer to this would be, ‘ASP.NET Core does not ship with any default HTML Tables along with the tons of features that jQuery Datatables can provide’. Since this is a client-side library, various tech-stack developers use this Powerful Open Source Plugin to handle everything related to viewing / manipulating data on HTML Tables.

As the JQuery Datatable runs at the client browser, you really don’t have to code extra to enable searching, sorting, paging which would otherwise take up hours of development time. WIth JQuery Datatable, life is easier for a developer :D

What we’ll Build?

In this implementation, We will be using an ASP.NET Core 3.1 Web Application Project Template. We will use JQuery Datatable Library to list all the Customer Records from the Customer API (We will fill this source with some random data).

This JQuery Datatable will have the Server Side Processing Property enabled and perform AJAX calls to receive page data from our API. We will be using Entity Framework Core - Code First Approach as our Data Access Layer. As a bonus, let’s also render buttons within the HTML Table.

Here is small demo of what we will be building.

Getting Started with JQuery Datatable in ASP.NET Core

Let’s get started by creating a new ASP.NET Core 3.1 Web Application Razor Pages Project. I use Visual 2019 Community Edition as my default IDE.

Generating the Model And Database

As we discussed earlier, we basically need an API endpoint that can return a list of Customers. Let’s start by creating a Customer Model at Models/Customer.cs

public class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Contact { get; set; }
    public string Email { get; set; }
    public DateTime DateOfBirth { get; set; }
}

Next, we will need to setup Entity Framework Core. First Install these required packages.

Install-Package Microsoft.EntityFrameworkCore
Install-Package Microsoft.EntityFrameworkCore.Tools
Install-Package Microsoft.EntityFrameworkCore.SqlServer
Install-Package Microsoft.EntityFrameworkCore.Design
Install-Package Microsoft.VisualStudio.Web.CodeGeneration.Design

Once that is done, let’s add in our ApplicationDbContext Class at Data/ApplicationDbContext.cs

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }
    public DbSet<Customer> Customers { get; set; }
}

Next, let’s configure our Startup class to support Entity Framework Core. Navigate to Startup.cs/ConfigureServices Method and add in the following.

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
        options.UseSqlServer(
            Configuration.GetConnectionString("DefaultConnection"),
            b => b.MigrationsAssembly(typeof(ApplicationDbContext).Assembly.FullName)));
    services.AddControllers();
    services.AddRazorPages();
}

Then, in the Configure Method, let’s the map the Controllers endpoint too. We are doing this because we had the Razor Page Template while creating the Project. But our requirement is that we will need both Razor Page as well as an API Controller within the same application. Thus we map the controllers too.

app.UseEndpoints(endpoints =>
{
    endpoints.MapControllers();
    endpoints.MapRazorPages();
});

Finally, let’s add the Connection String for DefaultConnection in the appsettings.json. PS, use your connection string here.

"ConnectionStrings": {
  "DefaultConnection": "Server=(localdb)/mssqllocaldb;Database=jqueryDb;Trusted_Connection=True;MultipleActiveResultSets=true"
}

With everything set, all we have to do is to add the Migrations and apply them to our database. Open up your package manager console and use the following commands

add-migration Initial
update-database

After you are done with this, you will be getting a Done Message on the console. Let’s check our Database now.

You can see that our table is properly created. But we are missing something, aren’t we? The Data :D For this implementation let’s add like 1000 Random Customer Records to this table. I will show a simple way to generate Sample Data that can help you during development.

Let’s fill the Database with some Sample Data

Sample Data is quite important in the development phases of any Project. I use Mockaroo to Generate Sample Data for Testing Purposes. It’s quite easy with their UI. Just fill in the columns that you need, give the corresponding table name, and the number of rows needed. Finally, click the Download Data button which gives you a download in the format you have chosen.

I generated over 1000 Sample Customer Records this way.

Select the SQL Format. Click on the Download Data Button. This generates a SQL File with 1000 Insert Statements. Just execute this script file against your database and that’s it!

PS, For keeping things simple for you, I am adding the script File in the Project’s Directory on Github as well. You can find it under SolutionsItems/SampleCustomers.sql

Here are the newly inserted 1000 Customers.

For now, we will talk about JQuery Datatable and come back to building an API Endpoint that returns data to the DataTable.

Installing the Required Client-Side Libraries

Let’s install the latest JQuery Datatable Library to our ASP.NET Core Application. We will do with this via libman. This is a client side library manager for ASP.NET Core.

And simply search for datatables. It automatically fills the latest version available. At the time of writing, 1.10.21 is the latest version datatables.

This will install Datatable CSS and JS files to the wwwroot/lib/datatables folder.

Make Sure that you Jquery available as well in your ASP.NET Core Project. By default, Jquery files are available in new ASP.NET Core Projects. jQuery Datatables need jQuery files to function properly.

Building the HTML Table

In order to implement JQuery Datatable, we need to first define the structure of our HTML Table in a Razor Page. In the Pages Folder, Open up the Index.cshtml. We will add the table here.

@page
@model IndexModel
@{
    ViewData["Title"] = "Home page";
}
<link href="~/lib/datatables/css/dataTables.bootstrap4.min.css" rel="stylesheet" />
<div class="container">
    <br />
    <div style="width:90%; margin:0 auto;">
        <table id="customerDatatable" class="table table-striped table-bordered dt-responsive nowrap" width="100%" cellspacing="0">
            <thead>
                <tr>
                    <th>ID</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Contact</th>
                    <th>Email</th>
                    <th>Date Of Birth</th>
                    <th>Actions</th>
                </tr>
            </thead>
        </table>
    </div>
</div>
@section Scripts
{
    <script src="~/lib/datatables/js/jquery.dataTables.min.js"></script>
    <script src="~/lib/datatables/js/dataTables.bootstrap4.min.js"></script>
}

Line 6 - Here we are importing the stylesheet of Datatable (Bootstrap4) that we had installed earlier.
Line 10- 21 - A Simple Table Tag for Customers with necessary Columns and Classes. Note that our columns are similar to the Customer Model that we added.
Line 19 - Under this column, we will try to render buttons later in the tutorial. Let’s say, a Delete Button that deletes the corresponding Customer.

Line 25 - If you check the _Layout.cshtml page in the Pages/Shared Folder, the “Scripts” section is rendered after the jquery libraries are loaded. In this way, you can use the Scripts section in the child pages like this, to load custom libraries after jQuery libraries are loaded. Note that it is important to load the jQuery library first. Else our Datatable would not work. In the scripts section, we are loading the necessary js files.

Ps, Do not forget to add an ID to your HTML Table. In our case, we added the ID as customerDatatable. Using this ID, the JS can recognize your table.

Let’s run the Application now.

You can see that our column are appearing, but we are still not able to see the actual Datatable. Why? It’s because we have not initialized it. In the next section, we will learn all about JQuery Datatable Options.

Using JQuery Datatable

Since JQuery Datatable is a client-side library, we need Scripts to configure it. You could write these scripts under the script tag with the Scripts Section in the Index.cshtml. However, it’s always better to have a separate script file for each entity. Here, we will treat the customerDatatable as one entity.

In the wwwroot/js folder, create a new file, customerDatatable.js. Here we will add everything related to the Customer Datatable.

Once the customerDatatable.js is created, let’s add it to the Scripts section of Index.cshtml file. Make sure to add this script file as the last one. Now you Scripts section would look like this.

@section Scripts
{
    <script src="~/lib/datatables/js/jquery.dataTables.min.js"></script>
    <script src="~/lib/datatables/js/dataTables.bootstrap4.min.js"></script>
    <script src="~/js/customerDatatable.js"></script>
}

The Basic Syntax

The first thing we will have to do is to Initialize the actual Datatable. For this, let’s add the most basic syntax in the customerDatatable.js

$(document).ready(function () {
    $('#customerDatatable').dataTable({
    });
});

What happens here is quite self-explanatory. As soon as the Document (HTML) loads up, using the ID of the Customer datatable, that is customerDatatable, we invoke the DataTable() Method. This is how to initialize the datatable. Run the application and check.

You can see that the Datatable is now up and running. In the Datatable() method, we can pass quite a lot of parameters to configure the DataTable. Let’s see each of them.

Understanding the Options / Parameters

• processing - A boolean property that when enables shows the processing indicator when the table is being processed.
• serverSide - This property enables server-side processing.
• filter - enables/disables the search bar.
• ajax - used to fetch the data from external sources, in our case, API
• columnDefs - Here we can define the properties of each column like visibility, isSearchable, and so on.
• columns - Column Initialization

Let’s assume that we have our API Endpoint running at /api/customer. (We will build this endpoint in a later section)

Using these properties , let’s build our Datatable Configuration Script.

$(document).ready(function () {
    $("#customerDatatable").DataTable({
        "processing": true,
        "serverSide": true,
        "filter": true,
        "ajax": {
            "url": "/api/customer",
            "type": "POST",
            "datatype": "json"
        },
        "columnDefs": [{
            "targets": [0],
            "visible": false,
            "searchable": false
        }],
        "columns": [
            { "data": "id", "name": "Id", "autoWidth": true },
            { "data": "firstName", "name": "First Name", "autoWidth": true },
            { "data": "lastName", "name": "Last Name", "autoWidth": true },
            { "data": "contact", "name": "Country", "autoWidth": true },
            { "data": "email", "name": "Email", "autoWidth": true },
            { "data": "dateOfBirth", "name": "Date Of Birth", "autoWidth": true },
            {
                "render": function (data,row) { return "<a href='#' class='btn btn-danger' onclick=DeleteCustomer('" + row.id+ "'); >Delete</a>";   }
            },
        ]
    });
});

Let’s understand each and every line of this script.

Line 1 - Runs the Function when the Document is ready.
Line 3 - Enables the Processing Indicator
Line 4 - Enables Server-Side Processing
Line 5 - Displays the Search Box

Line 6-10 - AJAX POST call to api/customer. (We will add this endpoint in some time)
Line 11-15 - Remember, the first column is Id? Let’s hide it from the Datatable and not include it in the search.
Line 16-26 - Here is the Array of all the Supported Columns.

It is important to use camelCasing while defining the names of the variables. firstName will work. But FirstName won’t. Quite weird, but that’s how js works. Make sure you follow camelCasing standard while working with js scripts.

Also, note that the list of records that are returned from the API will be named as ‘data’. That is why we are using this name for defining the column values. It is essentially data.id, data.firstName, and so on. Get it?

Line 14- Using the Row’s Id, we are rendering a button that on click triggers the DeleteCustomer method with Id as the parameter. I will leave the implementation of this method to you guys.

That’s basically everything you would have to do in the JS end. Build and run the application.

As expected, you will get an alert that says something like an AJAX error. You guessed it. It’s because we don’t have an api/customer POST method. Let’s build it now!

Creating an API Endpoint

Create a new Empty API Controller under the Controllers folder. You may have to create a new Controllers folder as well. Name the new controller as CustomerController.

Before continuing, we will need to install a specific package that is responsible for Sorting the data.

Install-Package System.Linq.Dynamic.Core

Here is what you will need in the Customer Controller.

using Datatables.ServerSide.Data;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Linq;
using System.Linq.Dynamic.Core;
namespace Datatables.ServerSide.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class CustomerController : ControllerBase
    {
        private readonly ApplicationDbContext context;
        public CustomerController(ApplicationDbContext context)
        {
            this.context = context;
        }
        [HttpPost]
        public IActionResult GetCustomers()
        {
            try
            {
                var draw = Request.Form["draw"].FirstOrDefault();
                var start = Request.Form["start"].FirstOrDefault();
                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();
                int pageSize = length != null ? Convert.ToInt32(length) : 0;
                int skip = start != null ? Convert.ToInt32(start) : 0;
                int recordsTotal = 0;
                var customerData = (from tempcustomer in context.Customers select tempcustomer);
                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))
                {
                    customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);
                }
                if (!string.IsNullOrEmpty(searchValue))
                {
                    customerData = customerData.Where(m => m.FirstName.Contains(searchValue)
                                                || m.LastName.Contains(searchValue)
                                                || m.Contact.Contains(searchValue)
                                                || m.Email.Contains(searchValue) );
                }
                recordsTotal = customerData.Count();
                var data = customerData.Skip(skip).Take(pageSize).ToList();
                var jsonData = new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data };
                return Ok(jsonData);

            }
            catch (Exception ex)
            {
                throw;
            }
        }
    }
}

Line 23- The Number of times the API is called for the current datatable.
Line 24 - The count of records to skip. This will be used while Paging in EFCore
Line 25 - Essentially the page size. You can see the Top Dropdown in the Jquery Datatable that says, ‘Showing n entries’. n is the page size.

Line 26 - The Column that is set for sorting
Line 27 - Ascending / Descending
Line 28 - The Value from the Search Box
Line 32 - Gets an IQueryable of the DataSource
Line 33-36 - Sorting
Line 37-43 - Searching. Here we will search through each column.

Line 44 - Gets the total count of the Records. According to me, this is the most expensive query in this entire controller code. You could probably avoid this by other means like storing the total records somewhere in another table, maybe?
Line 45 - Performs paging using EFCore
Line 46-47 - Sets the data in the required format and returns it.

That’s it! Let’s build and run our application.

Great, we have everything implemented now! Searching, Paging, Server Side Processing, Sorting, and a beautiful UI. This will be probably the most important aspect while developing ASP.NET Core Applications of any size and complexity. That’s it for this tutorial. :D

Summary

In this article, we covered everything you want to know while working with JQuery Datatable in ASP.NET Core Server Side Processing. We went through the syntaxes and files involved in this integration and ultimately built a clean datatable that implements paging, sorting, searching, and server-side processing. You can find the source code of the entire implementation here.

I hope you got a good knowledge of JQuery Datatable in ASP.NET Core from this article. If you have any comments or suggestions, please leave them behind in the comments section below. Do not forget to share this article within your developer community. Thanks and Happy Coding!