PM's Blog

Pramod Mohanan's notes about ASP.NET, MVC, C#, SQL, jQuery, Bootstrap

jQuery DataTables – Async and Responsive

I have used jQuery Datatables in few MVC projects until now and find it pretty easy to integrate. It allows data to loaded via ajax like an example here

$(document).ready(function() {
  $('#grid-table').DataTable({
    "columnDefs": [
    { "name": "Id", "targets": 0, "orderable": true },
    { "name": "ClientId", "targets": 1, "orderable": true },
    { "name": "CurrencyCode", "targets": 2 },
    { "name": "Amount", "targets": 3 },
    { "name": "PaymentStatusCode", "targets": 4 },
    { "name": "Message", "targets": 5 },
    { "name": "GatewayRef", "targets": 6 },
    { "name": "TransactionDate", "targets": 7 },
    { "name": "GatewayId", "targets": 8 },
    { "name": "IsApproved", "targets": 9 },
    { "name": "InvoiceNumber", "targets": 10 }
     ],
     "processing": true,          //this shows the processing message while the data is being loaded
     "serverSide": true,          //configured to access data from server
     "ajax" : "/Ajax/FetchData/"  //your ajax URL for loading data
  });
});

and the server side method would look like below. Teh same method should be able to handle for all UI events like pagination, search and sort.

public JsonResult FetchData(int draw, List<column> columns,int start, int length, List<order> order)
{
 var request = System.Web.HttpContext.Current.Request;
 var searchValue =  request.QueryString["search[value]"];
 var sortField = string.Empty;
 var sortDir = string.Empty;
 
 //fill the columns object
 foreach (var column in columns)
 {
  var index = columns.IndexOf(column);
  column.data = int.Parse(request.QueryString["columns[" + index + "][data]"]);
  column.name = request.QueryString["columns[" + index + "][name]"];
  column.oderable = bool.Parse(request.QueryString["columns[" + index + "][orderable]"]);
  column.searchable = bool.Parse(request.QueryString["columns[" + index + "][searchable]"]);
 }
 //fill the order object
 order[0].column = int.Parse(request.QueryString["order[0][column]"]);
 order[0].dir = request.QueryString["order[0][dir]"];
 
 if (order != null && order.Count > 0)
 {
   sortField = columns[order.First().column].name;
   sortDir = order.First().dir;
 }
 var dbOps = new DataOperation(Helper.ConnectionString);
 var count = dbOps.GetAllTransactionsCount();
 int filteredCount = 0;

//fetch data from Data Layer
//the GetTransactions method below is called to fetch the required records and does the search, sort etc.
 var requiredTransactions = dbOps.GetTransactions(start, searchValue, sortField, sortDir, ref filteredCount).Take(length);

//format the field data for the UI
 var transactions = requiredTransactions.Take(length).Select(t => new[]
 {
   t.Id.ToString(),
   t.ClientCurrencyFunction.Client.Name,
   t.ClientCurrencyFunction.CurrencyCode,
   t.Amount.ToString(),
   t.PaymentStatusCode ?? string.Empty,
   t.Message ?? string.Empty,
   t.GatewayRef ?? string.Empty,
   t.TransactionDate.ToString(),
   t.ClientCurrencyFunction.Client.Gateway.Name,
   t.IsApproved.ToString(),
   t.InvoiceNumber ?? string.Empty
 });
//return the json object
 return Json(new DataTableObject()
 {
   draw = draw,
   recordsTotal = count,
   recordsFiltered = filteredCount,
   data = transactions
 },JsonRequestBehavior.AllowGet);
}

Responsive

Especially while using bootstrap it will almost be imperative that the datatable be responsive. the easiest way to achieve this is by adding the below option while initiating the DataTable

 responsive:true

That should work with bootstrap and other responsive CSS frameworks as well.

Updated: August 28, 2015 — 8:20 am

Leave a Reply

Your email address will not be published. Required fields are marked *

PM's Blog © 2015