Datatables in ASP.NET Core Server-Side

Datatables In ASP.NET Core Server-Side

In this article, we will learn how to use Datatables in ASP.NET Core with Server-Side Processing. We will also be building a simple real-world implementation to help understand Datatables.net to it’s fullest. You can find the source code of the entire implementation here. Let’s begin. By the way, this is my first test to integrate new functionalities in my AdminLTE project.

What is Datatables.net?

Datatables.net 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 Datatable.net are

  • searching
  • sorting
  • pagination
  • JSON formatted source data
  • blazing-fast load times
  • server-side processing
  • client-side processing
  • and more.

So, it a highly flexible and powerful plugin.

Example of DataTables - Datatables in ASP.NET Core Server-Side
Example of Datatables

Server Side Processing vs Client Side Processing

First, for the purpose of integration Datatables in ASP.NET Core Server-Side, this is one of the most important concept. Let’s say we have an API endpoint that returns a list of Customers. Datatables.net works by calling this API endpoint using AJAX calls. Now, when the Datatables.net 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.

Client-side processing

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 Datatables.net 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?

Server-side processing

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 Datatables.net is loading just ~50 records instead of 10,000+ records), reducing the CPU and bandwidth usage. So, with every page, the Datatables.net would request for the next set of ~50 records. Get the advantage? So, this approach is also known as Server-Side Processing in Datatables.net.

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 Datatables in ASP.NET Core Server-Side processing.

Why do you need Datatables in ASP.NET Core?

So, a quick answer to this would be that ASP.NET Core does not ship with any default HTML Tables along with the tons of features that Datatables.net 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 Datatables.net 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.

What we’ll build?

In this implementation, we will be using an ASP.NET Core 5 Web Application Project Template. We will use Datatables.net library to list all the Customer records from the Customer API (we will fill this source with some random data).

Then, this Datatables.net 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.

Getting started with Datatables in ASP.NET Core

So, let’s get started by creating a new ASP.NET Core Empty web application in Visual Studio 2019. If you have to download Visual Studio, this is the official website.

Create a new project with Visual Studio 2019 - Datatables in ASP.NET Core Server-Side
Create a new project with Visual Studio 2019

Then, we have to choose the name of the project, its location and if you want to place solution and project in the sam directory.

Configure your new project - Datatables in ASP.NET Core Server-Side
Configure your new project

Finally, we have to choose the Target Framework. In this project I’m using .NET 5.0 but .NET Core 3.1 is perfectly fine.

Additional Information - Datatables in ASP.NET Core Server-Side
Additional Information

Generation the 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

using DatatablesWithASPNetCore.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

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

Next, let’s configure our Startup.cs 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.

Database for Datatables in ASP.NET Core Server-Side
Database for Datatables in ASP.NET Core Server-Side

You can see that our table is properly created. But we are missing something, aren’t we? The data 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

So, 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.

Makaroo for generating sample data
Makaroo for generating sample data

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! Run this script against your local database.

Installing the required Client-Side libraries

Let’s install the latest Datatables.net 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.

Client-Side Library on Visual Studio 2019 for your project
Client-Side Library on Visual Studio 2019 for your project

And simply search for Datatables.net. It automatically fills the latest version available. At the time of writing, 1.10.24 is the latest version Datatables.net. Then, add also datatables.net-bs4 for an optimize version of the library with Bootstrap.

Add Client-Side Library in Visual Studio 2019
Add Client-Side Library in Visual Studio 2019

This will install Datatables.NET CSS and JS files to the wwwroot/lib/datatables.net 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. Datatables.net need jQuery files to function properly.

Building the HTML table

In order to implement Datatables.net, 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.

@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 5 – Here we are importing the stylesheet of Datatable.net (Bootstrap4) that we had installed earlier.
    Line 9-20 – 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 18 – 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 Datatables.net would not work. In the scripts section, we are loading the necessary js files.

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.

Datatables.net is not initialized - Datatables in ASP.NET Core Server-Side
Datatables.net is not initialized

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

Using Datatables.net

Since Datatables.net 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 Datatables.net. 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.net. Run the application and check.

Datatables.net first run in ASP.NET Core application - Datatables in ASP.NET Core Server-Side
Datatables.net first run in ASP.NET Core application

You can see that the Datatables.net is now up and running. In the Datatable() method, we can pass quite a lot of parameters to configure the DataTables.net. 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. Using these properties , let’s build our Datatable.net 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.

  • 1 – Runs the Function when the Document is ready.
  • 3 – Enables the Processing Indicator
  • – Enables Server-Side Processing
  • 5 – Displays the Search Box
  • 6-10 – AJAX POST call to api/customer. (We will add this endpoint in some time)
  • 11-15 – Remember, the first column is Id? Let’s hide it from the Datatable.net and not include it in the search.
  • 16-26 – Here is the Array of all the Supported Columns.
  • 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.

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?

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

ajax error Datatable in ASP.NET Core - ServerSide Processing
Ajax error from Datatables.net

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 DatatablesWithASPNetCore.Data;
using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic.Core;
using System.Threading.Tasks;

namespace DatatablesWithASPNetCore.Controllers.Apis
{
    [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;
            }
        }
    }
}
  • 23 – The Number of times the API is called for the current Datatable.net.
  • 24 – The count of records to skip. This will be used while Paging in Entity Framework Core
  • 25 – Essentially the page size. You can see the Top Dropdown in the Datatables.net that says, ‘Showing n entries’. n is the page size.
  • 26 – The Column that is set for sorting
  • 27 – Ascending / Descending
  • 28 – The Value from the Search Box
  • 32 – Gets an IQueryable of the DataSource
  • 33-36 – Sorting
  • 37-43 – Searching. Here we will search through each column.
  • 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?
  • 45 – Performs paging using Entity Framework Core
  • 46-47 – Sets the data in the required format and returns it.

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

Final screenshot - Datatables in ASP.NET Core Server-Side
Final screenshot – Datatables.net in ASP.NET Core Server-Side

Conclusion

In conclusion, in this post I explained how to ingrate Datatables in ASP.NET Core Server-Side. I hope it is clear but if you have any question, please use our forum.

The source code of this project is on GitHub.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.