Datatables Method Start Again if New Request

May 19, 2022 Post a Comment

ASP.NET

What is DataTables?

DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, and will add advanced interaction controls to any HTML table.

Referenced from - https://datatables.internet/

Pre prerequisite for Application

Visual Studio 2012 / 2013 / 2022 / 2017
Entity Framework 5 and above
SQL Server 2008

Let's commencement with database part first.

Database Part

I accept created a database with the name "Northwind" and in that, it has "Customers" table.

ASP.NET

Adjacent, we are going to create an ASP.NET MVC5 Web application.

Creating ASP.Net MVC5 Spider web Application

Open New Visual Studio 2022 IDE.

ASP.NET

Later opening IDE just, side by side we are going to create MVC project for doing that merely click File - inside that New - Project.

ASP.NET

After choosing a project, a new dialog volition pop up with the proper noun "New Projection". In that, we are going to cull Visual C# Project Templates - Spider web - ASP.Cyberspace Web Application. Then, nosotros are going to name the projection as "DemoDatatables".

ASP.NET

After naming the project nosotros are going click on OK button to create a project.

A new dialog will popular up for choosing templates for Creating "ASP.Cyberspace Web Application;" in that template, we are going to Create MVC application. That's why we are going to choose "MVC template" and adjacent click on OK button to create a projection.

ASP.NET

After clicking on OK button information technology volition outset to create a project.

Project Structure

Later creating project next, nosotros are going to create Model.

Creating Customer Model

We are going to add Customer Model to the Models binder.

ASP.NET

Code Snippet

[Table( "Customers" )]
public class Customers
{
[Key]
public int ? CustomerID { get; fix; }
[Required(ErrorMessage ="Required CompanyName" )]
public string CompanyName { go; set; }
[Required(ErrorMessage ="Required ContactName" )]
public string ContactName { go; fix; }
[Required(ErrorMessage ="Required ContactTitle" )]
public string ContactTitle { become; prepare; }
public string Address { go; set; }
[Required(ErrorMessage ="Required City" )]
public string City { go; set; }
public string Region { get; prepare; }
[Required(ErrorMessage ="Required PostalCode" )]
public string PostalCode { get; gear up; }
[Required(ErrorMessage ="Required Country" )]
public string Land { become; ready; }
[Required(ErrorMessage ="Required Phone" )]
public string Telephone { get; prepare; }
public string Fax { go; prepare; }
}

After calculation model side by side, we are going to employ Entity framework for accessing database to doing that we need to setup DbContext class.

Notation - What is DbContext?

DbContext is an important function of Entity Framework.

It is a span between your domain or entity classes and the database.

DbContext is the primary form that is responsible for interacting with data as an object.

Referenced from here.

Setting up DbContext Class

In this role, nosotros are first going to create a grade with name "DatabaseContext" and this grade will exist inheriting from "DbContext" class.

We are going create this class in Model Folder.

ASP.NET

Note
"DBConnection" is Connectedness string name.

Note - What is DBSet?

Referenced from hither.

DBSet form represents an entity ready that is used for creating, read, update, and delete operations. A generic version of DBSet (DbSet<TEntity>) tin can be used when the type of entity is not known at build fourth dimension.

Afterward adding "DatabaseContext" form next we are going to inherit this class with "DbContext" form.

After inheriting course next in constructor nosotros are going to create a connection for doing that we demand to pass connexion cord proper name to "DbContext" class, we are passing connection string name every bit DBConnection.

After passing connectedness string next we are going to declare DbSet in "DbContext" class which will help us to perform create, read, update, and delete operations.

Code Snippet

namespace DemoDatatables.Models
{
public class DatabaseContext : DbContext
{
public DatabaseContext() : base( "DBConnection" )
{
}
public DbSet<Customers> Customers {get; set;}
}
}

Connexion cord in Web.config file

<connectionStrings>
<addname="DBConnection"
connectionString="Data Source=####; initial catalog=Northwind; user id=sa; password=Pass####;"
providerName="System.Data.SqlClient" />
</connectionStrings>

Next, we are going to add a controller.

Adding DemoController

In this part, we are going to add a new controller with the name "Demo".

ASP.NET

Afterwards we have clicked on Add button, it has created DemoController in Controller folder, every bit shown in the beneath view.

ASP.NET

After adding DemoController adjacent we are going to download DataTables Scripts and add it to project.

Getting DataTables Scripts

The post-obit Javascript library files are used in this example,

http://ajax.googleapis.com/ajax/libs/jquery/1.11.i/jquery.min.js
https://cdn.datatables.net/one.x.15/js/jquery.dataTables.min.js
https://cdn.datatables.cyberspace/1.ten.15/js/dataTables.bootstrap4.min.js

The following CSS files are used in this example,

Bootstrap v3.3.seven

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

DataTables CSS files

https://cdn.datatables.net/ane.10.15/css/dataTables.bootstrap.min.css
https://cdn.datatables.net/responsive/two.i.ane/css/responsive.bootstrap.min.css

Later on completing with downloading script and css next nosotros are going to add ShowGrid Action Method in DemoController.

Adding ShowGrid Action Method in Demo Controller

Subsequently adding Activeness Method now allow add View with proper noun "ShowGrid".

ASP.NET

Adding DataTables Filigree Scripts and Css on ShowGrid View

In get-go pace we are going to add together Script and Css reference.

<script src= "//ajax.googleapis.com/ajax/libs/jquery/1.xi.1/jquery.min.js" ></script>
<link href="~/Content/bootstrap.css" rel= "stylesheet" />
<link href="https://cdn.datatables.internet/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.internet/1.10.xv/js/jquery.dataTables.min.js" ></script>
<script src="https://cdn.datatables.net/1.ten.xv/js/dataTables.bootstrap4.min.js " ></script>

After adding Script and CSS reference side by side nosotros are going to add DataTables Markup.

Adding DataTables Markup

It is elementary Html Table in that we are going to add together columns headers ("<th>") with all the columns names which we want to display on the grid.

Afterwards adding Markup side by side, we are going to add DataTables function to Create DataTables.

<div class = "container" >
<br />
<div style="width:ninety%; margin:0 auto;" >
<table id="demoGrid" class = "table table-striped table-bordered dt-responsive nowrap" width= "100%" cellspacing= "0" >
<thead>
<tr>
<th>CustomerID</th>
<thursday>CompanyName</th>
<th>ContactName</thursday>
<th>ContactTitle</thursday>
<thursday>Metropolis</th>
<th>PostalCode</th>
<thursday>Country</th>
<th>Phone</th>
<th>Edit</th>
<th>Delete</thursday>
</tr>
</thead>
</tabular array>
</div>
</div>

Adding DataTables Function to create DataTables

Lawmaking Snippet

<script>
$('#demoGrid' ).dataTable({
});
</script>

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 beingness candy (e.chiliad. a sort).
server Side - 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 prison cell for each column. Although this can be quite useful for users, it tin also increase the complication of the order, potentiality increasing the processing fourth dimension of ordering the data. Therefore, this pick is provided to allow this shift-click multiple column abilities
Ajax - Ajax asking is made to become data to DataTables.
columnDefs - Prepare column definition initialisation properties.
Columns - Set column specific initialisation backdrop.

After completing with an understanding of options or properties next nosotros are going to ready it.

We are going to set "processing" option to true to brandish processing bar, after that, we are going to gear up the "serverSide" option to truthful because we are going to do paging and filtering at serverSide.

Next options after "serverSide" option are "filter." We are going to utilize the search box; that's why we have set this holding to true, "orderMulti" is also set up to fake because we do non want to sort multiple columns at once.

DataTables Options snapshot

Ajax Selection

And the principal pick is Ajax which we are going to employ for calling an Activeness Method for getting data to bind DataTables Grid the data is in Json format. For that we are going to laissez passer URL: -"/Demo/LoadData", this asking is Mail service request. And data type we are going to set as Json.

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

ASP.NET

columnDefs Pick

After setting Ajax we have a "columnDefs" choice which I accept used for hiding Principal key of the table ("CustomerID") and which should besides be not searchable.

ASP.NET

columns Selection

Finally, the second to last choice is columns which are used for initialization of DataTables filigree. Add that belongings which yous demand to render on the filigree, which must be defined in this columns selection.

ASP.NET

Render buttons in Columns

At terminal, nosotros need to return button in the grid for editing information and deleting information.

ASP.NET

Finally, on click of the delete button, nosotros tin can call a custom function to delete data as I take created "DeleteData" function.

Complete code Snippet of ShowGrid View

@{
Layout =null ;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content= "width=device-width" />
<title>ShowGrid</title>
<script src="//ajax.googleapis.com/ajax/libs/jquery/ane.11.ane/jquery.min.js" ></script>
<link href="~/Content/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.cyberspace/responsive/2.ane.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.cyberspace/1.10.xv/js/dataTables.bootstrap4.min.js " ></script>
<script>
$(certificate).set(function () {
$("#demoGrid" ).DataTable({
"processing" : true ,
"serverSide" : true ,
"filter" : true ,
"orderMulti" : fake ,
"pageLength" : 5,
"ajax" : {
"url" : "/Demo/LoadData" ,
"blazon" : "Mail service" ,
"datatype" : "json"
},
"columnDefs" :
[{
"targets" : [0],
"visible" : false ,
"searchable" : fake
},
{
"targets" : [vii],
"searchable" : imitation ,
"orderable" : simulated
},
{
"targets" : [viii],
"searchable" : false ,
"orderable" : fake
},
{
"targets" : [ix],
"searchable" : fake ,
"orderable" : false
}],
"columns" : [
{"data" : "CustomerID" , "proper name" : "CustomerID" , "autoWidth" : truthful },
{"information" : "CompanyName" , "proper noun" : "CompanyName" , "autoWidth" : truthful },
{"information" : "ContactName" , "title" : "ContactName" , "name" : "ContactName" , "autoWidth" : true },
{"data" : "ContactTitle" , "proper noun" : "ContactTitle" , "autoWidth" : true },
{"information" : "City" , "name" : "City" , "autoWidth" : truthful },
{"data" : "PostalCode" , "proper noun" : "PostalCode" , "autoWidth" : truthful },
{"information" : "Country" , "name" : "Country" , "autoWidth" : true },
{"information" : "Phone" , "proper name" : "Phone" , "title" : "Status" , "autoWidth" : true },
{
"render" : office (data, blazon, full, meta)
{return '<a class="btn btn-info" href="/Demo/Edit/' + full.CustomerID + '">Edit</a>' ; }
},
{
data:null , render: function (information, type, row) {
return "<a href='#' class='btn btn-danger' onclick=DeleteData('" + row.CustomerID + "'); >Delete</a>" ;
}
},
]
});
});
</script>
</caput>
<torso>
<divclass = "container" >
<br />
<div style="width:90%; margin:0 car;" >
<table id="demoGrid" class = "table table-striped table-bordered dt-responsive nowrap" width= "100%" cellspacing= "0" >
<thead>
<tr>
<thursday>CustomerID</th>
<thursday>CompanyName</th>
<th>ContactName</th>
<thursday>ContactTitle</th>
<th>City</th>
<thursday>PostalCode</th>
<th>Country</th>
<th>Telephone</thursday>
<th>Edit</th>
<th>Delete</th>
</tr>
</thead>
</table>
</div>
</div>
</body>
</html>

After completing with initialization of DataTables grid adjacent we are going to create LoadData Action Method.

Adding LoadData Action Method to Demo Controller

Hither we are going to Add Action Method with name LoadData. In this action method, we are going to go all Customer records from the database to display and on the basis of the parameter nosotros 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 volition execute queries with filters on the server side.

ASP.NET

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

Snapshot while adding System.Linq.Dynamic package from NuGet packages

After calculation the package, next, we come across the consummate code snippet and how to go data and do paging and filtering with it.

Complete code Snippet of LoadData Activity Method

All processes are step by step with comments; so it'southward like shooting fish in a barrel to sympathise.

All Asking.Form.GetValues parameters value will get populated when AJAX post method gets called on a load of if you practise paging or sorting and search.

Code Snippet

public ActionResult LoadData()
{
try
{
var draw = Request.Form.GetValues( "depict" ).FirstOrDefault();
var start = Request.Course.GetValues( "start" ).FirstOrDefault();
var length = Asking.Grade.GetValues( "length" ).FirstOrDefault();
var sortColumn = Request.Course.GetValues( "columns[" + Asking.Form.GetValues( "order[0][cavalcade]" ).FirstOrDefault() + "][name]" ).FirstOrDefault();
var sortColumnDir = Request.Form.GetValues( "order[0][dir]" ).FirstOrDefault();
var searchValue = Request.Form.GetValues( "search[value]" ).FirstOrDefault();
int pageSize = length != zippo ? Convert.ToInt32(length) : 0;
int skip = start != null ? Convert.ToInt32(start) : 0;
int recordsTotal = 0;
var customerData = (from tempcustomer in _context.Customers
select tempcustomer);
if (!(string.IsNullOrEmpty(sortColumn) && cord.IsNullOrEmpty(sortColumnDir)))
{
customerData = customerData.OrderBy(sortColumn +" " + sortColumnDir);
}
if (!string.IsNullOrEmpty(searchValue))
{
customerData = customerData.Where(m => m.CompanyName == searchValue);
}
recordsTotal = customerData.Count();
var data = customerData.Skip(skip).Take(pageSize).ToList();
return Json( new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = information });
}
catch (Exception)
{
throw ;
}
}

Complete code Snippet of DemoController

using DemoDatatables.Models;
using System;
using System.Linq;
using System.Spider web.Mvc;
using System.Linq.Dynamic;
using System.Data.Entity;
namespace DemoDatatables.Controllers
{
public course DemoController : Controller
{
public ActionResult ShowGrid()
{
return View();
}
public ActionResult LoadData()
{
attempt
{
using (DatabaseContext _context =new DatabaseContext())
{
var depict = Request.Grade.GetValues( "draw" ).FirstOrDefault();
var start = Request.Grade.GetValues( "start" ).FirstOrDefault();
var length = Request.Form.GetValues( "length" ).FirstOrDefault();
var sortColumn = Request.Course.GetValues( "columns[" + Request.Form.GetValues( "club[0][cavalcade]" ).FirstOrDefault() + "][name]" ).FirstOrDefault();
var sortColumnDir = Request.Form.GetValues( "club[0][dir]" ).FirstOrDefault();
var searchValue = Asking.Course.GetValues( "search[value]" ).FirstOrDefault();
int pageSize = length != nix ? Convert.ToInt32(length) : 0;
int skip = beginning != null ? Catechumen.ToInt32(start) : 0;
int recordsTotal = 0;
var customerData = (from tempcustomer in _context.Customers
select tempcustomer);
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
customerData = customerData.OrderBy(sortColumn +" " + sortColumnDir);
}
if (!cord.IsNullOrEmpty(searchValue))
{
customerData = customerData.Where(m => thou.CompanyName == searchValue);
}
recordsTotal = customerData.Count();
var information = customerData.Skip(skip).Accept(pageSize).ToList();
render Json( new { depict = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = information });
}
}
take hold of (Exception)
{
throw ;
}
}
}
}

Save the entire Source code and run the application.

Run Application

To admission the application, enter URL - http://localhost:#####/demo/showgrid .

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

ASP.NET

Existent-fourth dimension Debugging Snapshot

In this section, yous can see what values are populated when mail service method gets chosen.

ASP.NET

Search with DataTables grid

In this section we take implemented a search for only Companyname cavalcade, if yous desire to add another column just use or status ("||") with it.

ASP.NET

Calculation more columns to search

Snapshot while Search Information

Debugging View of Search

Later on completing with search Implementation next nosotros are going to piece of work on Edit Button of DataTables Grid.

Edit Event in DataTables filigree

In this section first nosotros are going add Edit Action Method in Demo Controller which will handle edit request and it will take Client ID as input from which we are going to become details of that client.

Code Snippet of Edit Action Method

[HttpGet]
public ActionResult Edit( int ? ID)
{
effort
{
using (DatabaseContext _context =new DatabaseContext())
{
var Customer = (from customer in _context.Customers
where customer.CustomerID == ID
select customer).FirstOrDefault();
return View(Customer);
}
}
catch (Exception)
{
throw ;
}
}

Later having a look on Edit action method next let'southward run into how to render Edit link (button).

Below is syntax for rendering Edit button

Finally, you tin see Edit View below.

Edit View

After completing with Edit office next we are going to accept a look at delete part.

Delete Event in DataTables filigree

In this section first we are going add DeleteCustomer Action Method in DemoController which will handle delete asking and it will take Client ID ("ID") every bit input from which we are going to delete customer data.

Code Snippet of DeleteCustomer

[HttpPost]
public JsonResult DeleteCustomer( int ? ID)
{
using (DatabaseContext _context =new DatabaseContext())
{
var customer = _context.Customers.Find(ID);
if (ID == zilch )
return Json(data: "Not Deleted" , behavior: JsonRequestBehavior.AllowGet);
_context.Customers.Remove(customer);
_context.SaveChanges();
return Json(data: "Deleted" , behavior: JsonRequestBehavior.AllowGet);
}
}

After having a look on DeleteCustomer action method next permit'southward see how to render delete link (button).

Below is syntax for rendering Delete button

Now you can come across that we are generating elementary href push button and on that button, nosotros accept added an onclick effect to call DeleteData function which we have non created yet, and so allow'due south create DeleteData function.

Code Snippet

In this part when user clicks on Delete button DeleteData function volition get chosen and commencement thing information technology volition show is confirmation alarm ("Are y'all sure you want to delete ...?") if y'all click on ok (ostend) button then it will call Delete function. This office takes CustomerID as input, side by side we are generating URL of DeleteCustomer Action Method and passing it as ajax post request and forth with it we are passing Customer ID as parameter.

If data is deleted, then we are going to get "Deleted" as a response from Deletecustomer Activity Method, finally, we show alert to the user and reload grid.