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 project. 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 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 article, I am going to use the jQuery DataTables Grid.
 
ASP.NET CORE
 
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.
 
ASP.NET CORE
 
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.
 
ASP.NET CORE
 
After opening IDE, next, we are going to create the ASP.NET Core MVC project. For that, just click on File >> New >> Project.
 
ASP.NET CORE
 
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”.
 
ASP.NET CORE
 
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 in order 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.
 
ASP.NET CORE
 
After clicking on the OK button, it will start to create a project.
 
Project Structure
 
ASP.NET CORE
 
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 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
 
ASP.NET CORE
 
Adding a Connection String and Setting Up DbContext
 
After adding a reference, now add a connection string in the appsetting.json file.
 
ASP.NET CORE
 
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.
 
ASP.NET CORE
 
Now, let’s add a class with the name DatabaseContext in the Models folder.
 
For adding 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.
 
ASP.NET CORE
 
After adding a DatabaseContext class, next, we are going to inherit the 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].
 
ASP.NET CORE
 
Next, we are going to add a new Service in Startup.cs class for injecting dependency.
 
Now, whenever you use DatabaseContext class, the DbContext instance will be injected there.
 
ASP.NET CORE
 
Adding Model CustomerTB
 
ASP.NET CORE
 
After adding CustomerTB Model, in our next step, we are going to add DbSet of all models in the DatabaseContext class.
 
Adding DbSet for CustomerTB Model in DatabaseContext class
 
Now, let's add DbSet for CustomerTB Model in DatabaseContext class, as shown below.
 
ASP.NET CORE
 
After adding CustomerTB Model in DatabaseContext class next step we are going to create a controller.
 
ASP.NET CORE
 
After we click on the Add button, it has created DemoGridController in the Controller folder, as shown in the below screenshot.
 
ASP.NET CORE
 
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
 
ASP.NET CORE
 
After adding the Action Method, now let's add a View with the name “ShowGrid”.
 
Adding ShowGrid View in DemoGrid Folder
 
ASP.NET CORE
 
Adding DataTables Grid to ShowGrid View
 
In the first step, we are going to add Script and Css references.
  1. <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>  
  2. <link href="~/lib/bootstrap/dist/css/bootstrap.css" rel="stylesheet" />  
  3.   
  4. <link href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css" rel="stylesheet" />  
  5. <link href="https://cdn.datatables.net/responsive/2.1.1/css/responsive.bootstrap.min.css" rel="stylesheet" />  
  6.   
  7. <script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>  
  8. <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.
 
ASP.NET CORE
  1. <div class="container">  
  2.     <br />  
  3.     <div style="width:90%; margin:0 auto;">  
  4.         <table id="example" class="table table-striped table-bordered dt-responsive nowrap" width="100%" cellspacing="0">  
  5.             <thead>  
  6.                 <tr>  
  7.                     <th>CustomerID</th>  
  8.                     <th>Name</th>  
  9.                     <th>Address</th>  
  10.                     <th>Country</th>  
  11.                     <th>City</th>  
  12.                     <th>Phoneno</th>  
  13.                     <th>Edit</th>  
  14.                     <th>Delete</th>  
  15.                 </tr>  
  16.             </thead>  
  17.         </table>  
  18.     </div>  
  19. </div>  
After adding Markup, next we are going to add a datatables function to create datatables.
 
Adding DataTables Function to Create DataTables
 
Basic syntax:
  1. $('#example').dataTable( {  
  2. } );  
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 initialisation properties.
  • Columns - Set column specific initialization properties.
After completing an understanding of options or properties next we are going to set it.
 
We are going to set the “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 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
 
ASP.NET CORE
 
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.
 
ASP.NET CORE
 
columnDefs 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.
 
ASP.NET CORE
 
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 columns option.
 
E.g.
 
ASP.NET CORE
 
Render Buttons in Columns
 
Finally, we need to render a button in the grid for editing and deleting data.
 
ASP.NET CORE
 
Finally, when clicking the delete button, we can call a custom function to delete data, as I have created the “DeleteData function.
 
Complete Code Snippet of ShowGrid View
  1. @ {  
  2.     Layout = null;  
  3. }  
  4.   
  5. <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>   
  6. <link href = "~/lib/bootstrap/dist/css/bootstrap.css" rel = "stylesheet" / >  
  7.   
  8. <link href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css" rel="stylesheet" />   
  9. <link href = "https://cdn.datatables.net/responsive/2.1.1/css/responsive.bootstrap.min.css" rel = "stylesheet" / >  
  10.   
  11. <script src="https://cdn.datatables.net/1.10.15/js/jquery.dataTables.min.js"></script>   
  12. <script src = "https://cdn.datatables.net/1.10.15/js/dataTables.bootstrap4.min.js">< /script>    
  13.   
  14.     <div class = "container" >  
  15.     <br />   
  16.     <div style = "width:90%; margin:0 auto;" >  
  17.     <table id="example" class="table table-striped table-bordered dt-responsive nowrap" width="100%" cellspacing="0">    
  18.                 <thead>    
  19.                     <tr>    
  20.                         <th>CustomerID</th>    
  21.                         <th>Name</th>    
  22.                         <th>Address</th>    
  23.                         <th>Country</th>    
  24.                         <th>City</th>    
  25.                         <th>Phoneno</th>    
  26.                         <th>Edit</th>    
  27.                         <th>Delete</th>    
  28.                     </tr>    
  29.                 </thead>    
  30.             </table>   
  31.             </div>     
  32.             </div>    
  33.   
  34.     <script>  
  35.   
  36.     $(document).ready(function() {  
  37.         $("#example").DataTable({  
  38.             "processing"true// for show progress bar    
  39.             "serverSide"true// for process server side    
  40.             "filter"true// this is for disable filter (search box)    
  41.             "orderMulti"false// for disable multiple column at once    
  42.             "ajax": {  
  43.                 "url""/DemoGrid/LoadData",  
  44.                 "type""POST",  
  45.                 "datatype""json"  
  46.             },  
  47.             "columnDefs": [{  
  48.                 "targets": [0],  
  49.                 "visible"false,  
  50.                 "searchable"false  
  51.             }],  
  52.             "columns": [  
  53.                 { "data""CustomerID""name""CustomerID""autoWidth"true },  
  54.                 { "data""Name""name""Name""autoWidth"true },  
  55.                 { "data""Address""name""Address""autoWidth"true },  
  56.                 { "data""Country""name""Country""autoWidth"true },  
  57.                 { "data""City""name""City""autoWidth"true },  
  58.                 { "data""Phoneno""name""Phoneno""autoWidth"true },  
  59.                 {  
  60.                     "render": function(data, type, full, meta) { return '<a class="btn btn-info" href="/DemoGrid/Edit/' + full.CustomerID + '">Edit</a>'; }  
  61.                 },  
  62.                 {  
  63.                     data: null,  
  64.                     render: function(data, type, row) {  
  65.                         return "<a href='#' class='btn btn-danger' onclick=DeleteData('" + row.CustomerID + "'); >Delete</a>";  
  66.                     }  
  67.                 },  
  68.             ]  
  69.   
  70.         });  
  71.     });  
  72.   
  73.   
  74. function DeleteData(CustomerID) {  
  75.     if (confirm("Are you sure you want to delete ...?")) {  
  76.         Delete(CustomerID);  
  77.     } else {  
  78.         return false;  
  79.     }  
  80. }  
  81.   
  82.   
  83. function Delete(CustomerID) {  
  84.     var url = '@Url.Content("~/")' + "DemoGrid/Delete";  
  85.   
  86.     $.post(url, { ID: CustomerID }, function(data) {  
  87.         if (data) {  
  88.             oTable = $('#example').DataTable();  
  89.             oTable.draw();  
  90.         } else {  
  91.             alert("Something Went Wrong!");  
  92.         }  
  93.     });  
  94. }  
  95.   
  96. </script> 
After finishing the initialization of the DataTables grid, next 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. 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.
 
ASP.NET CORE
 
For using OrderBy in the query, we need to install System.Linq.Dynamic package from NuGet packages.
 
ASP.NET CORE
 
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.  
  1. public IActionResult LoadData()  
  2.        {  
  3.            try  
  4.            {  
  5.                var draw = HttpContext.Request.Form["draw"].FirstOrDefault();  
  6.   
  7.                // Skip number of Rows count  
  8.                var start = Request.Form["start"].FirstOrDefault();  
  9.   
  10.                // Paging Length 10,20  
  11.                var length = Request.Form["length"].FirstOrDefault();  
  12.   
  13.                // Sort Column Name  
  14.                var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();  
  15.   
  16.                // Sort Column Direction (asc, desc)  
  17.                var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();  
  18.   
  19.                // Search Value from (Search box)  
  20.                var searchValue = Request.Form["search[value]"].FirstOrDefault();  
  21.   
  22.                //Paging Size (10, 20, 50,100)  
  23.                int pageSize = length != null ? Convert.ToInt32(length) : 0;  
  24.   
  25.                int skip = start != null ? Convert.ToInt32(start) : 0;  
  26.   
  27.                int recordsTotal = 0;  
  28.   
  29.                // getting all Customer data  
  30.                var customerData = (from tempcustomer in _context.CustomerTB  
  31.                                    select tempcustomer);  
  32.                //Sorting  
  33.                if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))  
  34.                {  
  35.                    customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);  
  36.                }  
  37.                //Search  
  38.                if (!string.IsNullOrEmpty(searchValue))  
  39.                {  
  40.                    customerData = customerData.Where(m => m.Name == searchValue);  
  41.                }  
  42.   
  43.                //total number of rows counts   
  44.                recordsTotal = customerData.Count();  
  45.                //Paging   
  46.                var data = customerData.Skip(skip).Take(pageSize).ToList();  
  47.                //Returning Json Data  
  48.                return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data });  
  49.   
  50.            }  
  51.            catch (Exception)  
  52.            {  
  53.                throw;  
  54.            }  
  55.   
  56.        }     
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.
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Threading.Tasks;  
  5. using Microsoft.AspNetCore.Mvc;  
  6. using ExampleGrid.Models;  
  7. using System.Linq.Dynamic;  
  8. // For more information on enabling MVC for empty projects, visit https://go.microsoft.com/fwlink/?LinkID=397860  
  9.   
  10. namespace ExampleGrid.Controllers  
  11. {  
  12.     public class DemoGridController : Controller  
  13.     {  
  14.         private DatabaseContext _context;  
  15.   
  16.         public DemoGridController(DatabaseContext context)  
  17.         {  
  18.             _context = context;  
  19.         }  
  20.         // GET: /<controller>/  
  21.         public IActionResult ShowGrid()  
  22.         {  
  23.             return View();  
  24.         }  
  25.   
  26.         public IActionResult LoadData()  
  27.         {  
  28.             try  
  29.             {  
  30.                 var draw = HttpContext.Request.Form["draw"].FirstOrDefault();  
  31.                 // Skiping number of Rows count  
  32.                 var start = Request.Form["start"].FirstOrDefault();  
  33.                 // Paging Length 10,20  
  34.                 var length = Request.Form["length"].FirstOrDefault();  
  35.                 // Sort Column Name  
  36.                 var sortColumn = Request.Form["columns[" + Request.Form["order[0][column]"].FirstOrDefault() + "][name]"].FirstOrDefault();  
  37.                 // Sort Column Direction ( asc ,desc)  
  38.                 var sortColumnDirection = Request.Form["order[0][dir]"].FirstOrDefault();  
  39.                 // Search Value from (Search box)  
  40.                 var searchValue = Request.Form["search[value]"].FirstOrDefault();  
  41.   
  42.                 //Paging Size (10,20,50,100)  
  43.                 int pageSize = length != null ? Convert.ToInt32(length) : 0;  
  44.                 int skip = start != null ? Convert.ToInt32(start) : 0;  
  45.                 int recordsTotal = 0;  
  46.   
  47.                 // Getting all Customer data  
  48.                 var customerData = (from tempcustomer in _context.CustomerTB  
  49.                                     select tempcustomer);  
  50.   
  51.                 //Sorting  
  52.                 if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))  
  53.                 {  
  54.                     customerData = customerData.OrderBy(sortColumn + " " + sortColumnDirection);  
  55.                 }  
  56.                 //Search  
  57.                 if (!string.IsNullOrEmpty(searchValue))  
  58.                 {  
  59.                     customerData = customerData.Where(m => m.Name == searchValue);  
  60.                 }  
  61.   
  62.                 //total number of rows count   
  63.                 recordsTotal = customerData.Count();  
  64.                 //Paging   
  65.                 var data = customerData.Skip(skip).Take(pageSize).ToList();  
  66.                 //Returning Json Data  
  67.                 return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data });  
  68.   
  69.             }  
  70.             catch (Exception)  
  71.             {  
  72.                 throw;  
  73.             }  
  74.   
  75.         }  
  76.     }  
  77. }  
Save the entire Source code and run the application.
 
Run Application
 
To access the application, enter URL - http://localhost:#####/demogrid/showgrid.
“#####” is a localhost port number.
 
ASP.NET CORE
 
Real-time Debugging Snapshot
 
In this section, you can see the values that are populated when the post method is called.
 
ASP.NET CORE
 
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.
 
ASP.NET CORE
 
Adding More Columns to Search
 
ASP.NET CORE
 
Search demo
 
ASP.NET CORE
 
Edit and Delete with Confirmation in 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
 
ASP.NET CORE
 
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
 
ASP.NET CORE
  1. <script>  
  2.    function DeleteData(CustomerID)  
  3.         {  
  4.             if (confirm("Are you sure you want to delete ...?"))  
  5.             {  
  6.                 Delete(CustomerID);  
  7.             }  
  8.             else  
  9.             {  
  10.                 return false;  
  11.             }  
  12.         }  
  13.   
  14.   
  15.     function Delete(CustomerID)  
  16.     {  
  17.       var url = '@Url.Content("~/")' + "DemoGrid/Delete";  
  18.       $.post(url, { ID: CustomerID }, function (data)  
  19.       {  
  20.           if (data)  
  21.           {  
  22.               oTable = $('#example').DataTable();  
  23.               oTable.draw();  
  24.           }  
  25.           else  
  26.           {  
  27.               alert("Something Went Wrong!");  
  28.           }  
  29.       });  
  30.     }  
  31. </script>  
ASP.NET CORE
 
Finally, we have learned how to use the jQuery DataTables Grid with ASP.NET CORE MVC. I hope you enjoyed this article.