datatables.query.net is a quick and easy way to generate responses and handle POST requests generated by the datatables.net package. This simplifies handling large datasets as it returns only paged data(i.e. 25 results at a time) and handles all the search, sort and individual pages on the server side. When handling large datasets this can dramatically improve performance for the end user.
To make this as speedy as possible if you're using an ORM such as Entity passing an IQueryable will ensure that the SQL database does not have to pull down all the records for your application but only the amount required for the page. An IEnumerable can still be passed if required.
C# example below:
using Datatables;
using Datatables.Models; //includes the models such as SearchRequest
namespace YourAppNamespace
{
public class YourController : ApiController
{
public IHttpActionResult Post(SearchRequest request)
{
/*Data must be flattened for this to work, project using LINQ
select if required before passing into ProcessDataTablePost method.
Only strings will be searchable.*/
var data = service.GetData(); //Pull your data
DataTable dt = new DataTable(); //Create new instance of datatable
var output = dt.ProcessDataTablePost(data,request); //Pass in your data and the request object to get the final output
return Ok(output);
}
}
}Sample return data:
{
"data": [{
"name": "Glass Finch",
"company": "CHORIZON",
"email": "glassfinch@chorizon.com",
"phone": "+1 (849) 558-3701",
"address": "696 Eastern Parkway, Elfrida, Arkansas, 4139"
}, {
"name": "Farmer Marquez",
"company": "CONFERIA",
"email": "farmermarquez@conferia.com",
"phone": "+1 (825) 582-3721",
"address": "232 Auburn Place, Riverton, North Dakota, 1167"
}, {
"name": "Francesca Tanner",
"company": "METROZ",
"email": "francescatanner@metroz.com",
"phone": "+1 (830) 441-2569",
"address": "801 Liberty Avenue, Lorraine, District Of Columbia, 2946"
}
],
"Draw": 25,
"RecordsFiltered": 53,
"RecordsTotal": 5102
}An example of the JavaScript used to handle a post request such as the above for filtering and sorting.
<script>
(function ($) {
var table = $("#table")
.dataTable({
"processing": true,
"serverSide": true,
"ajax": {
"url": "/api/Data", //replace with your API URL
"method": "POST"
},
"language": {
"emptyTable": "There are no items at present.",
"zeroRecords": "There were no matching items found."
},
"searching": true,
"ordering": true,
"paging": true,
"pageLength": 25, //Default starting page length
"columnDefs": [
{ "data": "heading1", "targets": 0 },
{ "data": "heading2", "targets": 1 },
{ "data": "heading3", "targets": 2 },
{ "data": "heading4", "targets": 3 },
{ "data": "heading5", "targets": 4 }
//"heading5" targets the the column header that contains heading5 while the 4 represents the 5th column in the data object that is returned by this package.
],
"order": [0, 'asc'] //Default sorting by column 0 ascending
});
})(jQuery);
</script>An example of the HTML that would accompany the JavaScript seem below.
<head>
<!--CDN sources can be used on the page or yoy can include these manually, if you've already included this; remove these lines-->
<script src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.min.js"></script>
<link rel="stylesheet" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.min.css">
<!--If you're already including JQuery remove the line below-->
<script src="https://code.jquery.com/jquery-3.3.1.min.js" integrity="sha256-FgpCb/KJQlLNfOu91ta32o/NMZxltwRo8QtmkMRdAu8=" crossorigin="anonymous"></script>
</head>
<body>
<table id="table" class="display no-wrap">
<thead>
<tr>
<th>heading1</th>
<th>heading2</th>
<th>heading3</th>
<th>heading4</th>
<th>heading5</th>
</tr>
</thead>
</table>
</body>