Skip to content Skip to sidebar Skip to footer
Home / Jquery Datatable How to Sort Again After Editable Column

Jquery Datatable How to Sort Again After Editable Column

Post a Comment

Introduction

In this commodity, we are going to learn how to employ a Client-side Filigree (DataTables Grid) with ASP.Internet Cadre MVC in a step-past-step fashion. A few months back, while interviewing for my company, I asked nigh developers what kind of Grids they were using in their current project. Most people answered with: a "DataTables Filigree". The next question was, why? The answer was: "Considering information technology is like shooting fish in a barrel to use and it is open-source".

Now, in this ongoing era of web development, well-nigh of the work is done at the Customer-side and less work is washed on the server-side.

As you know, there are no inbuilt Grids in ASP.Internet Cadre. You need to either create your own or use third-party Grids available from the NuGet package. The best selection is to employ the customer-side filigree. In this article, I am going to use the jQuery DataTables Grid.

ASP.NET CORE

Let'due south starting time with the database part first.

Database Office

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

ASP.NET CORE

Adjacent, nosotros are going to create an ASP.Internet 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.Cyberspace Core MVC project. For that, just click on File >> New >> Project.

ASP.NET CORE

Subsequently choosing a project, a new dialog will popular up with the name "New Project". In that, we are going to choose Visual C# Projection Templates >> Spider web >> ASP.NET Cadre Web Application. And then, nosotros are going to name the project "ExampleGrid".

ASP.NET CORE

Subsequently 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 Spider 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 take the option to cull framework 1.) Net core, or, 2.) .Cyberspace Framework. Also, there's the ASP.Net Cadre Version. For that, we are going to choose ".Internet Framework" and "ASP.Net Core i.1" every bit the ASP.Cyberspace Core Version. Click on the OK button to create a projection.

ASP.NET CORE

After clicking on the OK push, it will showtime to create a project.

Project Structure

ASP.NET CORE

After creating the application, adjacent, nosotros have to add together the references needed for Entity Framework Cadre.

Installing the Package for Entity Framework Core from NuGet

To install the bundle, but right click on the project (ExampleGrid) and then select Manage NuGet bundle. The beneath dialog of the NuGet Package Director will pop up.

In the browse tab, blazon "Microsoft.EntityFrameworkCore.SqlServer" in the search box and merely click on the Install push button.

Microsoft.EntityFrameworkCore.SqlServer

ASP.NET CORE

Adding a Connection String and Setting Up DbContext

Afterwards adding a reference, now add together a connexion string in the appsetting.json file.

ASP.NET CORE

After adding a connexion string, the side by side stride is to add a class that will inherit the DbContext grade. Before doing this, permit'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 card that pops up. Inside that, choose New Binder.

Add - New Folder.

ASP.NET CORE

Now, allow's add a class with the name DatabaseContext in the Models binder.

For calculation a model, just right click on the Models folder. And so, select Add >> Class. An "Add New Item" dialog will pop up with the default grade selected. Name the form as DatabaseContext and click on the Add push.

ASP.NET CORE

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

After inheriting with DbContext, next we are creating a constructor which takes DbContextOptions equally an input parameter and also inherits the base class constructor (: base of operations(options)) [DbContext].

ASP.NET CORE

Next, we are going to add together 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 together DbSet of all models in the DatabaseContext form.

Adding DbSet for CustomerTB Model in DatabaseContext class

Now, allow's add DbSet for CustomerTB Model in DatabaseContext grade, equally shown beneath.

ASP.NET CORE

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

ASP.NET CORE

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

ASP.NET CORE

Later on calculation DemoGridController, next, we are going to download DataTables Scripts and add it to the project.

Getting DataTables Scripts

The post-obit Javascript library files are used in this instance:

  • http://ajax.googleapis.com/ajax/libs/jquery/1.xi.1/jquery.min.js
  • https://cdn.datatables.internet/1.10.15/js/jquery.dataTables.min.js
  • https://cdn.datatables.net/1.x.xv/js/dataTables.bootstrap4.min.js

The post-obit CSS files are used in this example:

Bootstrap v3.3.seven

https://getbootstrap.com/docs/3.iii/getting-started/

DataTables CSS files

https://cdn.datatables.net/1.10.xv/css/dataTables.bootstrap.min.css https://cdn.datatables.net/responsive/ii.1.i/css/responsive.bootstrap.min.css

Calculation ShowGrid Action Method in DemoGrid Controller

ASP.NET CORE

After adding the Action Method, now permit'due south add a View with the name "ShowGrid".

Adding ShowGrid View in DemoGrid Binder

ASP.NET CORE

Calculation DataTables Grid to ShowGrid View

In the offset 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"  />
  4. <link href="https://cdn.datatables.net/1.10.15/css/dataTables.bootstrap.min.css"  rel= "stylesheet"  />
  5. <link href="https://cdn.datatables.cyberspace/responsive/2.1.1/css/responsive.bootstrap.min.css"  rel= "stylesheet"  />
  7. <script src="https://cdn.datatables.net/1.10.fifteen/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, adjacent, nosotros are going to add a Datatables Markup.

Adding DataTables Markup

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

ASP.NET CORE

  1. <div class = "container" >
  2.     <br />
  3.     <div style="width:xc%; margin:0 motorcar;" >
  4.         <tabular array 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>Proper noun</th>
  9.                     <thursday>Address</th>
  10.                     <th>Country</th>
  11.                     <th>City</th>
  12.                     <th>Phoneno</thursday>
  13.                     <th>Edit</th>
  14.                     <th>Delete</thursday>
  15.                 </tr>
  16.             </thead>
  17.         </table>
  18.     </div>
  19. </div>

After adding Markup, next nosotros are going to add a datatables function to create datatables.

Adding DataTables Function to Create DataTables

Bones syntax:

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

DataTables Options

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

  • Processing - Enables or disables the brandish of a 'processing' indicator when the table is being processed (east.1000. 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 tin can be quite useful for users, it can also increase the complexity of the order, potentially increasing the processing fourth dimension of ordering the information. Therefore, this selection is provided to allow this shift-click multiple column power
  • Ajax - Ajax request is made to go information 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, afterwards that, we are going to gear up the "serverSide" option to truthful considering we are going to do paging and filtering at the serverSide.

The next choice afterward the "serverSide" option is "filter." We are going to use the search box; that'south why we have set this belongings to true, "orderMulti" is too fix to faux 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 asking. We are going to set the data type every bit JSON.

Side by side, we are going to call the LoadData Activity Method, which is under the DemoGrid Controller that I will explain in the upcoming steps.

E.grand.

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 as well exist unsearchable.

Eastward.chiliad.

ASP.NET CORE

columns Option

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

Due east.g.

ASP.NET CORE

Render Buttons in Columns

Finally, we demand to return a push in the grid for editing and deleting information.

ASP.NET CORE

Finally, when clicking the delete button, we can telephone call a custom function to delete information, as I have created the "DeleteData" function.

Consummate Code Snippet of ShowGrid View

  1. @ {
  2.     Layout =nada ;
  3. }
  5. <script src="//ajax.googleapis.com/ajax/libs/jquery/1.11.i/jquery.min.js" ></script>
  6. <link href ="~/lib/bootstrap/dist/css/bootstrap.css"  rel = "stylesheet"  / >
  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"  / >
  11. <script src="https://cdn.datatables.cyberspace/1.10.15/js/jquery.dataTables.min.js" ></script>
  12. <script src ="https://cdn.datatables.net/ane.10.15/js/dataTables.bootstrap4.min.js" >< /script>
  14.     <divclass  = "container"  >
  15.     <br />
  16.     <div style ="width:ninety%; margin:0 automobile;"  >
  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>Accost</thursday>
  23.                         <thursday>Country</th>
  24.                         <th>City</thursday>
  25.                         <th>Phoneno</th>
  26.                         <th>Edit</th>
  27.                         <th>Delete</th>
  28.                     </tr>
  29.                 </thead>
  30.             </tabular array>
  31.             </div>
  32.             </div>
  34.     <script>
  36.     $(document).set up(function() {
  37.         $("#case" ).DataTable({
  38. "processing" : true ,
  39. "serverSide" : true ,
  40. "filter" : true ,
  41. "orderMulti" : false ,
  42. "ajax" : {
  43. "url" : "/DemoGrid/LoadData" ,
  44. "blazon" : "POST" ,
  45. "datatype" : "json"
  46.             },
  47. "columnDefs" : [{
  48. "targets" : [0],
  49. "visible" : false ,
  50. "searchable" : false
  51.             }],
  52. "columns" : [
  53.                 {"information" : "CustomerID" , "proper noun" : "CustomerID" , "autoWidth" : truthful  },
  54.                 {"information" : "Name" , "name" : "Name" , "autoWidth" : true  },
  55.                 {"data" : "Address" , "name" : "Accost" , "autoWidth" : true  },
  56.                 {"data" : "State" , "name" : "Country" , "autoWidth" : true  },
  57.                 {"information" : "City" , "name" : "Urban center" , "autoWidth" : true  },
  58.                 {"data" : "Phoneno" , "name" : "Phoneno" , "autoWidth" : true  },
  59.                 {
  60. "render" : function(information, type, full, meta) { return '<a grade="btn btn-info" href="/DemoGrid/Edit/'  + total.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.             ]
  70.         });
  71.     });
  74. office DeleteData(CustomerID) {
  75. if  (confirm( "Are you sure yous desire to delete ...?" )) {
  76.         Delete(CustomerID);
  77.     }else  {
  78. render false ;
  79.     }
  80. }
  83. part Delete(CustomerID) {
  84.     var url ='@Url.Content("~/")'  + "DemoGrid/Delete" ;
  86.     $.postal service(url, { ID: CustomerID }, function(information) {
  87. if  (data) {
  88.             oTable = $('#example' ).DataTable();
  89.             oTable.draw();
  90.         }else  {
  91.             warning("Something Went Incorrect!" );
  92.         }
  93.     });
  94. }
  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 together 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 demand to install System.Linq.Dynamic package from NuGet packages.

ASP.NET CORE

Afterward adding the packet, next, we encounter the complete code snippet and how to go data and do paging and filtering with information technology.

Complete code Snippet of LoadData Action Method

All processes are step-by-step with comments, then it's easy to sympathize.

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

  1. public  IActionResult LoadData()
  2.        {
  3. try
  4.            {
  5. var  depict = HttpContext.Request.Form[ "draw" ].FirstOrDefault();
  8. var  start = Request.Course[ "starting time" ].FirstOrDefault();
  11. var  length = Request.Form[ "length" ].FirstOrDefault();
  14. var  sortColumn = Request.Course[ "columns["  + Request.Form[ "social club[0][cavalcade]" ].FirstOrDefault() + "][name]" ].FirstOrDefault();
  17. var  sortColumnDirection = Request.Form[ "guild[0][dir]" ].FirstOrDefault();
  20. var  searchValue = Asking.Form[ "search[value]" ].FirstOrDefault();
  23. int  pageSize = length != null  ? Convert.ToInt32(length) : 0;
  25. int  skip = showtime != nix  ? Convert.ToInt32(start) : 0;
  27. int  recordsTotal = 0;
  30. var  customerData = (from tempcustomer in  _context.CustomerTB
  31.                                    select tempcustomer);
  33. if  (!(string.IsNullOrEmpty(sortColumn) && cord.IsNullOrEmpty(sortColumnDirection)))
  34.                {
  35.                    customerData = customerData.OrderBy(sortColumn +" "  + sortColumnDirection);
  36.                }
  38. if  (!string.IsNullOrEmpty(searchValue))
  39.                {
  40.                    customerData = customerData.Where(m => m.Name == searchValue);
  41.                }
  44.                recordsTotal = customerData.Count();
  46. var  information = customerData.Skip(skip).Accept(pageSize).ToList();
  48. return  Json( new  { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data });
  50.            }
  51. catch  (Exception)
  52.            {
  53. throw ;
  54.            }
  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 Organization.Collections.Generic;
  3. using Organization.Linq;
  4. using System.Threading.Tasks;
  5. using Microsoft.AspNetCore.Mvc;
  6. using ExampleGrid.Models;
  7. using Organisation.Linq.Dynamic;
  10. namespace ExampleGrid.Controllers
  11. {
  12. public class  DemoGridController : Controller
  13.     {
  14. individual  DatabaseContext _context;
  16. public  DemoGridController(DatabaseContext context)
  17.         {
  18.             _context = context;
  19.         }
  21. public  IActionResult ShowGrid()
  22.         {
  23. return  View();
  24.         }
  26. public  IActionResult LoadData()
  27.         {
  28. try
  29.             {
  30. var  draw = HttpContext.Request.Form[ "describe" ].FirstOrDefault();
  32. var  beginning = Request.Form[ "start" ].FirstOrDefault();
  34. var  length = Request.Form[ "length" ].FirstOrDefault();
  36. var  sortColumn = Asking.Form[ "columns["  + Asking.Form[ "order[0][column]" ].FirstOrDefault() + "][name]" ].FirstOrDefault();
  38. var  sortColumnDirection = Request.Form[ "order[0][dir]" ].FirstOrDefault();
  40. var  searchValue = Request.Form[ "search[value]" ].FirstOrDefault();
  43. int  pageSize = length != nil  ? Convert.ToInt32(length) : 0;
  44. int  skip = showtime != null  ? Convert.ToInt32(starting time) : 0;
  45. int  recordsTotal = 0;
  48. var  customerData = (from tempcustomer in  _context.CustomerTB
  49.                                     select tempcustomer);
  52. if  (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDirection)))
  53.                 {
  54.                     customerData = customerData.OrderBy(sortColumn +" "  + sortColumnDirection);
  55.                 }
  57. if  (!string.IsNullOrEmpty(searchValue))
  58.                 {
  59.                     customerData = customerData.Where(m => chiliad.Name == searchValue);
  60.                 }
  63.                 recordsTotal = customerData.Count();
  65. var  information = customerData.Skip(skip).Have(pageSize).ToList();
  67. return  Json( new  { describe = describe, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = data });
  69.             }
  70. grab  (Exception)
  71.             {
  72. throw ;
  73.             }
  75.         }
  76.     }
  77. }

Relieve the entire Source code and run the awarding.

Run Application

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

"#####" is a localhost port number.

ASP.NET CORE

Existent-time Debugging Snapshot

In this department, yous tin can run across the values that are populated when the post method is chosen.

ASP.NET CORE

Search with DataTables grid

In this section, we have implemented a search for simply the Proper noun column. If yous want to add another column, simply use or condition ("||") with information technology.

ASP.NET CORE

Adding More than Columns to Search

ASP.NET CORE

Search demo

ASP.NET CORE

Edit and Delete with Confirmation in DataTables Grid

In this section, nosotros are focusing on two buttons edit and delete. Edit is used for editing records. In this part, you but 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 but do the same style as Edit but for that, we demand to create another page. However, if we want a line deleted, then we demand to apply the below syntax for rendering the button.

Below is the syntax for Rendering a Delete Button

ASP.NET CORE

  1. <script>
  2. office  DeleteData(CustomerID)
  3.         {
  4. if  (ostend( "Are you sure yous want to delete ...?" ))
  5.             {
  6.                 Delete(CustomerID);
  7.             }
  8. else
  9.             {
  10. return false ;
  11.             }
  12.         }
  15. part  Delete(CustomerID)
  16.     {
  17. var  url = '@Url.Content("~/")'  + "DemoGrid/Delete" ;
  18.       $.post(url, { ID: CustomerID },part  (data)
  19.       {
  20. if  (information)
  21.           {
  22.               oTable = $('#instance' ).DataTable();
  23.               oTable.draw();
  24.           }
  25. else
  26.           {
  27.               warning("Something Went Wrong!" );
  28.           }
  29.       });
  30.     }
  31. </script>

ASP.NET CORE

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

reeddreasockinly80.blogspot.com

Source: https://www.c-sharpcorner.com/article/using-jquery-datatables-grid-with-asp-net-core-mvc/

Post a Comment for "Jquery Datatable How to Sort Again After Editable Column"