If we have huge data to bind to Jquery Datatble, server side data binding is a handy option. As we can fetch a set of records and bind only those at time. Further next set of records can be retrieved when requested. e.g, if we have a page size set to 10 records in Datatable, it is a good choice to load only first 10 records in the datatable, when user click on "2" in pagination, we can retrieve 11-20 i.e, next 10 records from database and display in datatable. This will increase noticeable performance for datatable.
So let's dive in how we can achieve this,
<script src="~/Scripts/js/core/libraries/jquery.min.js"></script>
<script type="text/javascript" src="~/Scripts/js/plugins/tables/datatables/datatables.min.js"></script>
<script src="~/Scripts/js/plugins/tables/datatables/extensions/key_table.min.js"></script>
<!-- here I am using KeyTable (A datatable extension) to navigate through datatable using keyboard
$(document).ready(function () {
var table = $('#PurchaseorderList').DataTable({
"processing": true, // for show progress bar
"serverSide": true, // for process server side
"filter": true, // this is for disable filter (search box)
"orderMulti": true, // for disable multiple column at once
"bPaginate": true,
"ajax": {
"url": "@Url.Content("/Transactional/GetPurchaseOrder")",
"type": "POST",
"datatype": "json",
dataFilter: function (data) {
//debugger;
var json = jQuery.parseJSON(data);
json.recordsTotal = json.recordsTotal;
json.recordsFiltered = json.recordsFiltered;
json.data = json.data;
return JSON.stringify(json); // return JSON string
}
},
"oLanguage": {
"sSearch": "Find: "
},
"columns": [
{ data: 'VoucherType', name: 'VoucherType', "autoWidth": true },
{ data: 'VoucherNumber', name: 'VoucherNumber', "autoWidth": true },
{ data: 'VoucherSeries', name: 'VoucherSeries', "autoWidth": true },
{
data: 'VoucherDate', name: 'VoucherDate', "autoWidth": true,
'render': function (jsondate) {
var date = new Date(parseInt(jsondate.substr(6)));
var month = date.getMonth() + 1;
return date.getDate() + "/" + month + "/" + date.getFullYear();
}
},
{ data: 'Amount', name: 'Amount', "autoWidth": true },
{ data: 'masterid', name: 'masterid', "autoWidth": true, "visible": false }
],
keys: true,
keys: {
focus: ':eq(1)'
},
});
table
.on('key', function (e, datatable, key, cell, originalEvent) {
//alert('<div>Key press: ' + key + ' for cell <i>' + cell.data() + '</i></div>');
//debugger;
if (key == 13) //This will be called when user press enter key, and we can read the values in the row
{
//debugger;
var table = $('#PurchaseorderList').DataTable();
var rows = table.rows('.selected').indexes();
var data = table.rows(rows).data();
var masid = data[0].masterid;
$('#hidentab').val(masid);
}
})
.on('key-focus', function (e, datatable, cell) {
debugger;
var row = datatable.row(cell.index().row);
$(row.node()).addClass("selected");
})
.on('key-blur', function (e, datatable, cell) {
var row = datatable.row(cell.index().row);
$(row.node()).removeClass("selected");
})
.on('draw', function () {
$('tr td:nth-child(2)').each(function () {
})
});
});
Now let's have a look on controller side, I am using Asp.Net MVC, but you can use any server side language of your choice
[HttpPost]
public ActionResult GetPurchaseOrderList()
{
var draw = Request.Form.GetValues("draw").FirstOrDefault();
var start = Request.Form.GetValues("start").FirstOrDefault();
var length = Request.Form.GetValues("length").FirstOrDefault();
var search = Request["search[value]"];
//Find Order Column
var sortColumn = Request.Form.GetValues("columns[" + Request.Form.GetValues("order[0][column]").FirstOrDefault() + "][name]").FirstOrDefault();
var sortColumnDir = Request.Form.GetValues("order[0][dir]").FirstOrDefault();
int pageSize = length != null ? Convert.ToInt32(length) : 0;
int skip = start != null ? Convert.ToInt32(start) : 0;
int recordsTotal = 0;
//This is where we are fetching data from database
var data = (from purordlist in ObjDB.masterpurchaseorders
join MasterAcc in ObjDB.masteraccounts on purordlist.AccountID equals MasterAcc.AccountID
where purordlist.VoucherType == FixAccounts.VoucherTypeForPurchaseOrder
select new
{
VoucherSeries = purordlist.VoucherSeries,
VoucherType = purordlist.VoucherType,
VoucherNumber = purordlist.VoucherNumber,
VoucherDate = purordlist.VoucherDate,
Amount = purordlist.Amount,
masterid = purordlist.ID
});
if (!string.IsNullOrWhiteSpace(search))
{
data = data.Where(x => x.VoucherNumber.ToString().ToLower().Contains(search.ToLower()));
}
if (!(string.IsNullOrEmpty(sortColumn) && string.IsNullOrEmpty(sortColumnDir)))
{
data = data.OrderBy(q => sortColumn + " " + sortColumnDir);
}
recordsTotal = data.Count();
var v = data.Skip(skip).Take(pageSize).ToList();
return Json(new { draw = draw, recordsFiltered = recordsTotal, recordsTotal = recordsTotal, data = v }, JsonRequestBehavior.AllowGet);
//return Json(data);
}
That's it. Now your datatable is ready. You can load millions of records in datatable without performance issue.
The following image shows how fast we retrieve data with server side pagination/ search/ sort
Nice work dude
ReplyDelete:)