Server Side Rendering Of DataTables JS In ASP.NET Core

Introduction

In this article, we are implementing server-side data configuration in data tables js in asp.net core. In this article, we are going to cover the following topics.

1. What is DataTables JS
2. Why is Server-side rendering required
3. Create .Net Core Project With Code First Approach
4. Implement DataTables JS in Asp.net Core
5. Implement Server-Side Rendering
6. Search, Sort, and Pagination on the server side

What are data tables Js?

DataTables Js is a javascript plugin that helps to create a dynamic, responsive, and multi-function table with fewer codes. DataTables js can convert your simple table into a multi-functional table that has searching, sorting, and pagination options.

We can implement DataTables in two ways, first create a simple table with data and then convert it to a data table or fetch data from the server by default using Ajax of DataTables.

Why is server-side rendering required?

DataTables js is also used to generate data tables from existing tables created using HTML. All operations like sorting, searching, and pagination also worked on this table. This is suitable when you have a small amount of data like 500 or 1000. But what happens when you have data in lakhs? You can’t load the whole data all the time when a user interacts with a data table or searches for anything in that table.

In this situation, server-side rendering is used. When we use server-side rendering we pass only the required data to the client-side from the controller. For example, you show only 20 records per page then in server-side rendering, you only have to pass 20 records from the controller. This method can reduce the time of loading and the user doesn’t need to wait for a long time.

Create a .NET core project

Step 1. Create a new project by clicking on File>New>Project

Step 2. Select Asp.Net Core Web App with Model View and Controller and click on the Next button.

Step 3. In the next wizard, you have to enter the following things and then click on the Next button.

• Project Name
• Location of your project (Path where you want to save your project)

Step 4. In this wizard screen, you have to specify the following things and then click on the create button.

• Target Framework: Here I am using the current version installed in my system which is 5.
• Authentication Type: Currently in this project, we are not using authentication so here I select none.
• Configure HTTPS: If you host your API with a secure HTTPS connection then you can check this box. It will add a redirection in your project which helps to redirect the HTTP request to HTTPS automatically.
• Enable Docker: For this project, we are not using Docker so leave unchecked
• Enable Razor Runtime Compilation: If you check this option it will automatically enable auto compile view in your project. This means there is no need to re-run your project after making changes on the view side.

Now our project is created and you can see the project structure in the below image.

Add entity framework and create tables

For using the Entity Framework in our project and creating a table using the code first approach we have to follow the below steps.

Step 1. Right-click on the project name and click on Manage NuGet Packages.

Install the NuGet package

Step 2. Install the Following NuGet Packages as per your project version compatibility.

• Microsoft.EntityFrameworkCore.SqlServer: This package is used to interact with SQL Server from our C# and .Net Core.
• Microsoft.EntityFrameworkCore.Tools: This package contains various commands like Add-Migration, Drop-Database, Get-DbContext, Get-Migration, Remove-Migration, Scaffold-DbContext, Script-Migration, and Update-Database. In this article, we use Add-Migration and Update-Database commands.
• Microsoft.Extensions.Configuration: Using this NuGet package we can read data from our app setting file. We will get our connection string from the app setting file.

Step 3. Now we add a new folder in our solution to contain various database table classes. To add a new folder in our solution right click on the project name click on Add then click on New Folder and gave the name DbModels.

Step 4. In this DbModels folder, we will use our entity classes. Right-click in this folder then Add then Class. Give a suitable name for your class.

Step 5. Add fields as you want to create in your table. Here I create an Employee class with the following fields. Here key attribute defines that use this column as the primary key.

public class Employees {
    [Key]
    public int EmployeeId {
        get;
        set;
    }
    public string EmployeeFirstName {
        get;
        set;
    }
    public string EmployeeLastName {
        get;
        set;
    }
    public decimal Salary {
        get;
        set;
    }
    public string Designation {
        get;
        set;
    }
}

Step 6. Now we create a context class which use as a middleware to SQL Server. Add a new class in your Models folder and add constructor and Employee DbSet as seen below code.

public class EmpContext: DbContext {
    public EmpContext(DbContextOptions options): base(options) {}
    DbSet <Employees> Employees {
        get;
        set;
    }
}

Step 7. Now we have to connect SQL Server with our project, for that, we need a connection string, and for this string, we are going to add the in-app setting file. Add your connection string as shown below.

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft": "Warning",
            "Microsoft.Hosting.Lifetime": "Information"
        }
    },
    "AllowedHosts": "*",
    "ConnectionStrings": {
        "ConStr": "Server=.;Database=DataTableDemo;MultipleActiveResultSets=True;Trusted_Connection=True;"
    }
}

As seen in the above code here I pass. (dot) as a server name because I used my local SQL Server. Then give the database name Tutorial, if this database does not exist then it will generate automatically. Here I have not given any username and password because I use Windows authentication for this. If you want to use another method to log in then pass username and password.

Step 8. Now we have to add Db Context in our startup file for this open startup file and add the following code.

public void ConfigureServices(IServiceCollection services) {
    services.AddControllersWithViews().AddRazorRuntimeCompilation();
    services.AddDbContext < EmpContext > (x => x.UseSqlServer(Configuration.GetConnectionString("ConStr")));
}

In the ConfigureService Method, we add our EmpContext class and pass the connection string in it by getting it from our app setting file using Configure.GetConnectionString() method.

Step 9. Now open Package Manager Console by clicking on Tool Menu then NuGet Package Manager then Package Manager Console.

Step 10. Add the following command.

Add-Migration Init

Here Init is our name of migration, you can give as per your choice. Hit enter.

Step 11. As you can see in your solution new folder named Migration is created and in this project, there are two files. One is EmpContextModelSnapshot and another one is *_Init, here * mean date time stamp.

Step 12. For now, our database and table are not created to make changes on the server side Use the below command.

Update-Database

Now you can see that our SQL Server Employee table is created with the same fields as we added in our model.

Implement data tables JS in ASP.NET core

Add a table in your view. Here I used the already generated controller home and add a simple table in the index view as shown below code.

@{
    ViewData["Title"] = "Home Page";
}
<div class="container">
    <div class="table-responsive">
        <table id="myTable" class="table table-bordered">
            <thead>
                <tr>
                    <th>Employee Id</th>
                    <th>First Name</th>
                    <th>Last Name</th>
                    <th>Salary</th>
                    <th>Designation</th>
                </tr>
            </thead>
        </table>
    </div>
</div>

When you run this code output of this table looks like the below image.

addedTo convert this table into a data table you just have to put some lines of code as given below.

@section Scripts{
    <script src="//cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <script>
        $(document).ready(function () {
            $('#myTable').DataTable()
        }     
        });
    </script> 
}

<link href="//cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css" rel="stylesheet" />

As you see above code, we add the cdn of the data table you can download the required scripts and call them from your local location also. And when the document is ready convert our table to a data table using a method of data table. All this script is in a section called script which is defined in the layout file because we want to execute this scripts after all necessary scripts are loaded.

We also add a style sheet on top of the view. Now you refresh your view you can see the data table as a given snapshot.

Enable server side rendering

Step 1

<script>
       $(document).ready(function () {
           $('#myTable').DataTable(
               {
                   ajax: {
                       url: "Home/GetEmployeeList",
                       type: "POST", 
                   },
                   processing: true,
                   serverSide: true,
                   filter: true,
                   columns: [
                       { data: "employeeId", name: "EmployeeId" },
                       { data: "employeeFirstName", name: "EmployeeFirstName" },
                       { data: "employeeLastName", name: "EmployeeLastName" },
                       { data: "salary", name: "Salary" },
                       { data: "designation", name: "Designation" },
                   ]
               }
           );
       });
</script>

As you see in the above code we add some properties in datatable methods.

• Ajax:This will send the request to the server to the given URL which is here Home/GetEmployeeList
• Processing: A boolean property that when enables shows the processing indicator when the table is being processed.
• Server Side: this property needs to set true when we want to enable server-side rendering
• Filter: enables/disables the search bar. We if don’t want to show the search box we can set this property false.
• Column: Total recordThis takes an array of the object which defines the column name coming from the server. Data shown in a column is based on the given name and data. As you saw in the code the data property contains a column name that starts in lowercase because we return data from the server-side is in JSON format.

Step 2. Now create an action method in the controller side that returns JSON data as per datatable ajax received.

[HttpPost]
public JsonResult GetEmployeeList() {
    int totalRecord = 0;
    int filterRecord = 0;
    var draw = Request.Form["draw"].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 = Convert.ToInt32(Request.Form["length"].FirstOrDefault() ?? "0");
    int skip = Convert.ToInt32(Request.Form["start"].FirstOrDefault() ?? "0");
    var data = _context.Set < Employees > ().AsQueryable();
    //get total count of data in table
    totalRecord = data.Count();
    // search data when search value found
    if (!string.IsNullOrEmpty(searchValue)) {
        data = data.Where(x => x.EmployeeFirstName.ToLower().Contains(searchValue.ToLower()) || x.EmployeeLastName.ToLower().Contains(searchValue.ToLower()) || x.Designation.ToLower().Contains(searchValue.ToLower()) || x.Salary.ToString().ToLower().Contains(searchValue.ToLower()));
    }
    // get total count of records after search 
    filterRecord = data.Count();
    //sort data
    if (!string.IsNullOrEmpty(sortColumn) && !string.IsNullOrEmpty(sortColumnDirection)) data = data.OrderBy(sortColumn + " " + sortColumnDirection);
    //pagination
    var empList = data.Skip(skip).Take(pageSize).ToList();
    var returnObj = new {
        draw = draw, recordsTotal = totalRecord, recordsFiltered = filterRecord, data = empList
    };
  }
}

Explanation

• Here I create an action method that returns JsonResult and the type of this method is Post.
• In this method, we define and initialize some variables let’s look one by one.

Note. All the values which we are getting from Request. The form comes when we enable server Side true in the datatable js side.

• Draw: In this variable, we are going to store draw value coming from the datatable side. The draw parameter is just a sequence counter. It allows responses to come back out of order and DataTables will draw the correct page.
• Sort Column: In this variable, we are storing column name using which we are sorting our data. We don’t get directly column name from datatable. We are getting column index in order[0][column] parameter using we get column name from columns[index][name] parameter.
• Sort Column Direction: In this variable, we directionsDatatablstore sort direction like ASC or DESC. We are getting this direction in order[0][dir] parameter.
• Search Value: In this variable, we are going to store search value that comes from the data table side when a user searches anything in the search box.
• PageSize: In this variable, we are store page size means the number of records we are going to show on a single page. We can get this value from the length parameter of the Request. The start Form
• Skip: In this variable, we store the start index. Start index is defined by PageIndex(On which page you are) * pageSize. We get this value in the start parameter.
• Get data from the employee table and store it in an object which here is data.
• When we are getting search value then filter data from data object as per value.
• For sorting, we need to install one NuGet package System.Linq.Dynamic.Core this package is used for order data as we order in SQL query.
• For pagination skip given records first and then take the given record. storeThis data we store in a new variable which is list type.
• Data object which accepts by data table after server call accepted contains main four properties
  • Draw: return the same draw which we are getting from the client side
  • RecordsTotal: number of records that in that table
  • RecordsFiltered: number of records after filtering/searching
  • Data: The data that we want to display here this data is in the empList object.
• In last return this object in JSON.

When we run this example given variable gets data as shown in the below image.

After successfully returning data to the data table. Our table will look like the below snapshot.

Searching in data table

When we type some value in the given textbox it will come to the controller side in the searchValue variable as shown in a given image.

After data is returned to the client-side table will look like below.

Sorting column

When we click on the column header it will call server and pass that column index and direction. You can see data coming to the controller and then the client side in the given below images.

Pagination

When we change the length from the top drop-down it will change the value of page size and when we click on the page number given in the bottom right side it will change the value of start. You can see differences in datatable in the given below images.

This is just a basic example of server-side rendering. DataTables JS has many more features you can learn from its official website.https://datatables.net/. I hope you find this article helpful. You can find source code of this project on my GitHub.