Using jQuery DataTables Grid With ASP.NET CORE MVC

In this article, we are going to learn how to use Client side Grid (DataTables Grid) with ASP.NET Core MVC in a step by step way. A few months back, while interviewing for my company, I asked most of the developers what kind of Grids they were using in their current project. Most people answered: “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 at 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, I am going to use jQuery DataTables Grid.



Let’s start with database part first.

Database Part

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



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

Creating ASP.NET Core MVC Web Application

Open New Visual Studio 2017 IDE.



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



After choosing 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 as “ExampleGrid”.



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



After clicking on 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 package for Entity framework core from NuGet

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

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

 Microsoft.EntityFrameworkCore.SqlServer



Adding Connection string and Setting up DbContext

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



After adding a connection string, the next step is to add a class which will inherit DbContext class. Before doing this, let's start with creating a folder for models and 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, and inside that choose New Folder.

Add - New Folder.



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

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



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

After inheriting with DbContext, next we are creating a constructor which 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 DatabaseContext class, DbContext instance will be injected there.



Adding Model CustomerTB



After adding CustomerTB Model, in our next step, we are going to add DbSet of all models in DatabaseContext class.

Adding DbSet for CustomerTB Model in DatabaseContext class

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



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



After we have clicked on Add button, it has created DemoGridController in Controller folder, as shown in the below view.



After adding DemoGridController next we are going to download DataTables Scripts and add it to 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 Action Method now let add View with name “ShowGrid”.

Adding ShowGrid View in DemoGrid Folder



Adding DataTables Grid to ShowGrid View

In first step we are going to add Script and Css reference.

<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 reference next we are going to add Datatables Markup.

Adding DataTables Markup

It is simple Html Table in that we are going to add columns headers (“<th>”) will all columns names 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 datatables function to Create datatables.

Adding DataTables Function to create DataTables

Basic syntax

 

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

 

DataTables Options

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

• Processing - Enable or disable 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 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, potentiality 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 initialisation properties.
• Columns - Set column specific initialisation properties.

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

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

Next options after “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

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

We are going to call LoadData Action Method which is under DemoGrid Controller which I will explain in upcoming steps.

E.g.



columnDefs Option

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

E.g.



columns Option

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

E.g.



Render buttons in Columns

At last, we need to render button in the grid for editing data and deleting data.



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

Complete 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 completing with initialization of DataTables grid next we are going to create LoadData Action Method.

Adding LoadData Action Method to DemoGrid Controller

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

We are doing paging and filtering of data on the server side; that 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 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. And 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();
                // Skiping 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 URL - http://localhost:#####/demogrid/showgrid .

“#####” is localhost port number.



Real time Debugging Snapshot

In this section you can seen what values are populated when post method is called.

Search with DataTables grid

In this section we have implemented search for only Name column; if you want to add other column just use or condition (“||”) with it.



Adding more columns to search



Search demo



Edit and Delete with Confirmation in DataTables grid

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

Below is syntax for rendering Edit button

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

Below is syntax for rendering 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 jQuery DataTables Grid with ASP.NET CORE MVC. I hope you enjoyed the article.