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
});
});
$('#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);
}
{
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.