Using jQuery DataTables Grid With ASP.NET CORE MVC

Introduction

In this article, we are going to learn how to use a Client-side Grid (DataTables Grid) with ASP.NET Core MVC in a step-by-step manner. A few months back, while interviewing for my company, I asked most developers what kind of Grids they were using in their current projects. Most people answered with a “DataTables Grid”. The next question was, why? The answer was: "Because it is easy to use and it is open-source”.

Now, in this ongoing era of web development, most of the work is done on the Client-side and less work is done on the server side.

As you know, there are no inbuilt Grids in ASP.NET Core. You need to either create your own or use third-party Grids available from the NuGet package. The best option is to use the client-side grid. In this article, I am going to use the jQuery DataTables Grid.

Let’s start with the database part first.

Database Part

I have created a database with the name “CustomerDB”. In that, it has a “CustomerTB” table.

Next, we are going to create an ASP.NET Core MVC Web application.

Creating ASP.NET Core MVC Web Application

Open a New Visual Studio 2017 IDE.

After opening IDE, next, we are going to create the ASP.NET Core MVC project. For that, just click on File >> New >> Project.

After choosing a project, a new dialog will pop up with the name “New Project”. In that, we are going to choose Visual C# Project Templates >> Web >> ASP.NET Core Web Application. Then, we are going to name the project “ExampleGrid”.

After naming the project, we are going to click on the OK button to create a project. A new dialog will pop up for choosing templates to create an “ASP.NET Core Web Application”. In that template, we are going to create an MVC application. That's why we are going to choose “Web Application (Model View Controller)”. Next, we will have the option to choose framework 1.) Net core, or, 2.) .NET Framework. Also, there's the ASP.NET Core Version. For that, we are going to choose “.NET Framework” and “ASP.NET Core 1.1” as the ASP.NET Core Version. Click on the OK button to create a project.

After clicking on the OK button, it will start to create a project.

Project structure

After creating the application, next, we have to add the references needed for Entity Framework Core.

Installing the Package for Entity Framework Core from NuGet

To install the package, just right-click on the project (ExampleGrid) and then select the Manage NuGet package. The below dialog of the NuGet Package Manager will pop up.

In the browse tab, type “Microsoft.EntityFrameworkCore.SqlServer” in the search box and just click on the Install button.

Microsoft.EntityFrameworkCore.SqlServer

Adding a connection string and setting up DbContext

After adding a reference, now add a connection string in the appsetting.json file.

After adding a connection string, the next step is to add a class that will inherit the DbContext class. Before doing this, let's start with creating a folder for models. Inside that, we are going to add this class.

For adding a folder, just right-click on the project (ExampleGrid), then choose Add from the menu that pops up. Inside that, choose New Folder.

Add  New Folder.

Now, let’s add a class with the name DatabaseContext in the Models folder.

To add a model, just right-click on the Models folder. Then, select Add >> Class. An "Add New Item" dialog will pop up with the default class selected. Name the class as DatabaseContext and click on the Add button.

After adding a DatabaseContext class, next, we are going to inherit the DbContext class.

After inheriting with DbContext, next, we create a constructor that takes DbContextOptions as an input parameter and also inherits the base class constructor (: base(options)) [DbContext].

Next, we are going to add a new Service in Startup.cs class for injecting dependency.

Now, whenever you use the DatabaseContext class, the DbContext instance will be injected there.

Adding Model CustomerTB

After adding the customer Model, in our next step, we are going to add the best of all models in the DatabaseContext class.

Adding DbSet for CustomerTB Model in DatabaseContext class

Now, let's add DbSet for the CustomerTB Model in the DatabaseContext class, as shown below.

After adding the CustomerTB Model in the DatabaseContext class, in the next step, we are going to create a controller.

After we click on the Add button, it has created DemoGridController in the Controller folder, as shown in the below screenshot.

After adding DemoGridController, next, we are going to download DataTables Scripts and add it to the project.

Getting DataTables Scripts

The following Javascript library files are used in this example.

• http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js
• https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js  
• https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap4.min.js

The following CSS files are used in this example.

Bootstrap v3.3.7

https://getbootstrap.com/docs/3.3/getting-started/

DataTables CSS files

https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css https://cdn.datatables.net/responsive/2.1.1/css/responsive.bootstrap.min.css

Adding ShowGrid Action Method in DemoGrid Controller

After adding the Action Method, now let's add a View with the name “ShowGrid”.

Adding ShowGrid View in DemoGrid Folder

Adding DataTables Grid to ShowGrid View

In the first step, we will add Script and CSS references.

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>  
<link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />  
<link href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css" rel="stylesheet" />
<link href="https://cdn.datatables.net/responsive/2.1.1/css/responsive.bootstrap.min.css" rel="stylesheet" />  
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>  
<script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap4.min.js "></script>  

After adding Script and CSS references, next, we are going to add a Datatables Markup.

Adding DataTables Markup

This is a simple HTML Table where we are going to add column headers, (“<th>”), which will show all columns names that we want to display.

<div class="container">  
    <br />  
    <div style="width:90%; margin:0 auto;">  
        <table id="example" class="table table-striped table-bordered dt-responsive nowrap" width="100%" cellspacing="0">  
            <thead>  
                <tr>  
                    <th>CustomerID</th>  
                    <th>Name</th>  
                    <th>Address</th>  
                    <th>Country</th>  
                    <th>City</th>  
                    <th>Phoneno</th>  
                    <th>Edit</th>  
                    <th>Delete</th>  
                </tr>  
            </thead>  
        </table>  
    </div>  
</div>  

After adding Markup, next, we are going to add a data table function to create data tables.

Adding DataTables Function to Create DataTables

Basic syntax

$('#example').dataTable({
});

DataTables Options

All definitions are taken from the https://datatables.net website.

• Processing: Enables or disables the display of a 'processing' indicator when the table is being processed (e.g. a sort).
• ServerSide: Server-side processing - where filtering, paging, and sorting calculations are all performed by a server.
• Filter: this option is used for enabling and disabling of the search box
• OrderMulti: When ordering is enabled (ordering), by default DataTables allows users to sort multiple columns by shift-clicking upon the header cell for each column. Although this can be quite useful for users, it can also increase the complexity of the order, potentially increasing the processing time of ordering the data. Therefore, this option is provided to allow this shift-click multiple-column ability
• Ajax: Ajax request is made to get data to DataTables.
• ColumnDefs: Set column definition initialization properties.
• Columns: Set column-specific initialization properties.

After completing an understanding of options or properties we are going to set it.

We are going to set the “processing” option to true to display the processing bar, after that, we are going to set the “serverSide” option to true because we are going to do paging and filtering at the serverSide.

The next option after the “serverSide” option is “filter.” We are going to use the search box; that's why we have set this property to true, “orderMulti” is also set to false because we do not want to sort multiple columns at once.

DataTables Options Snapshot

Ajax option

The main option is Ajax, which we are going to use for calling an Action Method for getting data to bind the DataTables Grid. The data is in JSON format. For that, we are going to pass the URL: -"/DemoGrid/LoadData”. This request is a Post request. We are going to set the data type as JSON.

Next, we are going to call the LoadData Action Method, which is under the DemoGrid Controller that I will explain in the upcoming steps.

E.g.

Columns Option

After setting Ajax, we have a “columnDefs” option that I have used for hiding the Primary key of the table (“CustomerID”) and that should also be unsearchable.

E.g.

Columns option

Finally, the second to last option is columns that are used for the initialization of the DataTables grid. Add the property that you need to render on the grid, which must be defined in this column's option.

E.g.

Render Buttons in columns

Finally, we need to render a button in the grid for editing and deleting data.

Finally, when clicking the delete button, we can call a custom function to delete data, as I have created the “DeleteData” function.

Complete the Code Snippet of ShowGrid View.

@{
    Layout = null;
}
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>   
<link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />  
<link href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css" rel="stylesheet" />   
<link href="https://cdn.datatables.net/responsive/2.1.1/css/responsive.bootstrap.min.css" rel="stylesheet" />  
<script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>   
<script src="https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap4.min.js"></script>    
<div class="container">  
    <br />   
    <div style="width:90%; margin:0 auto;">  
        <table id="example" class="table table-striped table-bordered dt-responsive nowrap" width="100%" cellspacing="0">    
            <thead>    
                <tr>    
                    <th>CustomerID</th>    
                    <th>Name</th>    
                    <th>Address</th>    
                    <th>Country</th>    
                    <th>City</th>    
                    <th>Phoneno</th>    
                    <th>Edit</th>    
                    <th>Delete</th>    
                </tr>    
            </thead>    
        </table>   
    </div>     
</div>     
<script>  
    $(document).ready(function() {  
        $("#example").DataTable({  
            "processing": true, // for show progress bar    
            "serverSide": true, // for process server side    
            "filter": true, // this is for disable filter (search box)    
            "orderMulti": false, // for disable multiple column at once    
            "ajax": {  
                "url": "/DemoGrid/LoadData",  
                "type": "POST",  
                "datatype": "json"  
            },  
            "columnDefs": [{  
                "targets": [0],  
                "visible": false,  
                "searchable": false  
            }],  
            "columns": [  
                { "data": "CustomerID", "name": "CustomerID", "autoWidth": true },  
                { "data": "Name", "name": "Name", "autoWidth": true },  
                { "data": "Address", "name": "Address", "autoWidth": true },  
                { "data": "Country", "name": "Country", "autoWidth": true },  
                { "data": "City", "name": "City", "autoWidth": true },  
                { "data": "Phoneno", "name": "Phoneno", "autoWidth": true },  
                {  
                    "render": function(data, type, full, meta) { 
                        return '<a class="btn btn-info" href="/DemoGrid/Edit/' + full.CustomerID + '">Edit</a>'; 
                    }  
                },  
                {  
                    data: null,  
                    render: function(data, type, row) {  
                        return "<a href='#' class='btn btn-danger' onclick=DeleteData('" + row.CustomerID + "');>Delete</a>";  
                    }  
                },  
            ]  
        });  
    });  
    function DeleteData(CustomerID) {  
        if (confirm("Are you sure you want to delete ...?")) {  
            Delete(CustomerID);  
        } else {  
            return false;  
        }  
    }  
    function Delete(CustomerID) {  
        var url = '@Url.Content("~/")' + "DemoGrid/Delete";  
        $.post(url, { ID: CustomerID }, function(data) {  
            if (data) {  
                oTable = $('#example').DataTable();  
                oTable.draw();  
            } else {  
                alert("Something Went Wrong!");  
            }  
        });  
    }  
</script>

After finishing the initialization of the DataTables grid, we are going to create a LoadData Action Method.

Adding LoadData action method to demoGrid controller

Here, we are going to Add an Action Method with the name LoadData. In this action method, we are going to get all Customer records from the database to display. Based on the parameter, we are going to sort data and do paging with data.

We are doing paging and filtering of data on the server side; that is why we are using IQueryable which will execute queries with filters on the server side.

For using OrderBy in the query, we need to install System.Linq.Dynamic package from NuGet packages.

After adding the package, next, we see the complete code snippet and how to get data and do paging and filtering with it.

Complete code snippet of LoadData action method

All processes are step-by-step with comments, so it's easy to understand.

All Request. Form parameters value will get populated when the AJAX post method gets called on load.

public IActionResult LoadData()  
{  
    try  
    {  
        var draw = HttpContext.Request.Form["draw"].FirstOrDefault();  
        // Skip number of Rows count  
        var start = Request.Form["start"].FirstOrDefault();  
        // Paging Length 10, 20  
        var length = Request.Form["length"].FirstOrDefault();  
        // Sort Column Name  
        var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();  
        // Sort Column Direction (asc, desc)  
        var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();  
        // Search Value from (Search box)  
        var searchValue = Request.Form["search[value]"].FirstOrDefault();  
        // Paging Size (10, 20, 50, 100)  
        int pageSize = length != null ? Convert.ToInt32(length) : 0;  
        int skip = start != null ? Convert.ToInt32(start) : 0;  
        int recordsTotal = 0;  
        // getting all Customer data  
        var customerData = (from tempcustomer in _context.CustomerTB  
                            select tempcustomer);  
        // Sorting  
        if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))  
        {  
            customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);  
        }  
        // Search  
        if (!string.IsNullOrEmpty(searchValue))  
        {  
            customerData = customerData.Where(m => m.Name == searchValue);  
        }  
        // total number of rows counts  
        recordsTotal = customerData.Count();  
        // Paging  
        var data = customerData.Skip(skip).Take(pageSize).ToList();  
        // Returning Json Data  
        return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data });  
    }  
    catch (Exception)  
    {  
        throw;  
    }  
}

Complete code Snippet of DemoGridController

In this part, we are using constructor injection to inject DBContext dependencies. By using DBContext, we are getting all customer data from the database.

using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Threading.Tasks;  
using Microsoft.AspNetCore.Mvc;  
using ExampleGrid.Models;  
using System.Linq.Dynamic;  
// For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860  
namespace ExampleGrid.Controllers  
{  
    public class DemoGridController : Controller  
    {  
        private DatabaseContext _context;  
        public DemoGridController(DatabaseContext context)  
        {  
            _context = context;  
        }  
        // GET: /<controller>/  
        public IActionResult ShowGrid()  
        {  
            return View();  
        }  
        public IActionResult LoadData()  
        {  
            try  
            {  
                var draw = HttpContext.Request.Form["draw"].FirstOrDefault();  
                // Skipping number of Rows count  
                var start = Request.Form["start"].FirstOrDefault();  
                // Paging Length 10, 20  
                var length = Request.Form["length"].FirstOrDefault();  
                // Sort Column Name  
                var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();  
                // Sort Column Direction (asc, desc)  
                var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();  
                // Search Value from (Search box)  
                var searchValue = Request.Form["search[value]"].FirstOrDefault();  
                // Paging Size (10, 20, 50, 100)  
                int pageSize = length != null ? Convert.ToInt32(length) : 0;  
                int skip = start != null ? Convert.ToInt32(start) : 0;  
                int recordsTotal = 0;  
                // Getting all Customer data  
                var customerData = (from tempcustomer in _context.CustomerTB  
                                    select tempcustomer);  
                // Sorting  
                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))  
                {  
                    customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);  
                }  
                // Search  
                if (!string.IsNullOrEmpty(searchValue))  
                {  
                    customerData = customerData.Where(m => m.Name == searchValue);  
                }  
                // Total number of rows count  
                recordsTotal = customerData.Count();  
                // Paging  
                var data = customerData.Skip(skip).Take(pageSize).ToList(); 
                // Returning Json Data  
                return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data });  
            }  
            catch (Exception)  
            {  
                throw;  
            }  
        }  
    }  
}  

Save the entire Source code and run the application.

Run Application

To access the application, enter the URL - http://localhost:#####/demogrid/showgrid.

“#####” is a local host port number.

Real-time Debugging Snapshot

In this section, you can see the values that are populated when the post method is called.

Search with DataTables grid

In this section, we have implemented a search for only the Name column. If you want to add another column, just use or condition (“||”) with it.

Adding more columns to search

Search demo

Edit and Delete with confirmation in the DataTables grid

In this section, we are focusing on 2 buttons edit and delete. Edit is used for editing records. In this part, you just need to provide a URL of the edit page with its parameter.

Below is the Syntax for Rendering Edit Button

For the delete button, we can just do the same way as Edit but for that, we need to create another page. However, if we want a line deleted, then we need to use the below syntax for rendering the button.

Below is the syntax for Rendering a Delete Button

<script>
    function DeleteData(CustomerID) {
        if (confirm("Are you sure you want to delete ...?")) {
            Delete(CustomerID);
        } else {
            return false;
        }
    }
    function Delete(CustomerID) {
        var url = '@Url.Content("~/")' + "DemoGrid/Delete";
        $.post(url, { ID: CustomerID }, function (data) {
            if (data) {
                oTable = $('#example').DataTable();
                oTable.draw();
            } else {
                alert("Something Went Wrong!");
            }
        });
    }
</script>

Finally, we have learned how to use the jQuery DataTables Grid with ASP.NET CORE MVC. I hope you enjoyed this article.