In this article we will show how you can load the data from SharePoint List using REST API into JQuery DataTable using the Ajax Pagination.
In my previous article I showed you how to retrieve the data from the SharePoint list using REST API but it retrieves all the data from the list at once which is good for the list having less number of items/records but what if the list has large number of items as SharePoint client object model supports retrieval of 5000 items at once.
In my previous article I showed you how to retrieve the data from the SharePoint list using REST API but it retrieves all the data from the list at once which is good for the list having less number of items/records but what if the list has large number of items as SharePoint client object model supports retrieval of 5000 items at once.
In this case the SharePoint REST API provides the $TOP, $ORDERBY, $SKIP AND $INLINECOUNT parameters to retrieve only the records required to show on one page.
Parameter
|
Example
|
Description
|
$skip
|
$skip=n
|
Returns entries
skipping the first n entries,
according to the $orderby parameter
|
$top
|
$top=n
|
Returns only the top n entries, according to the $orderby and $skip parameters
|
$inlinecount
|
$inlinecount=allpages
|
It will
add the __count property to
the results which indicates the total number of entries which match the $filter expression
|
$orderby
|
$orderby =
CustomerName
|
Returns the
records ordered by the CustomerName
field
|
We will use these parameters to retrieve the data required and bind it to our JSON
We will use the same customers list we used in my previous article series
- Load the Data in JQuery DataTable from SharePoint List using REST API
- Custom DropDown Column Filter in JQuery Datatable
- Free Text Search on Column in JQuery Datatable
The rest query to get the top 10 items from the customers list would look like this,
../_vti_bin/listdata.svc/Customers?$select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue&$inlinecount=allpages&$top=10
Now in order to support pagination in DataTable the json results should have values of “sEcho”, “iTotalRecords”, “iTotalDisplayRecords in the json results which is not provided by the SharePoint by default so we will manipulate it in the fnServerData function of the JQuery DataTable.
$.ajax({
"dataType": "json",
"type": "GET",
"url": restQuery,
"data": aoData,
"success": function (json) {
json["sEcho"] = oSettings.iDraw.toString();
json["iTotalRecords"] = json.d.__count;
json["iTotalDisplayRecords"] = json.d.__count;
fnCallback(json);
}
});
var sortCol =
oSettings.aaSorting[0][0];
var sortDir =
oSettings.aaSorting[0][1];
|
So now we can include the sorting as well, so lets say we want to sort based on the CustomerName we can have the REST Query as,
../_vti_bin/listdata.svc/Customers?$select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue&$inlinecount=allpages&$top=10&$orderby=CustomerName
Below will be my entire CustomerJqueryDatatableAjax.txt code,
<script type="text/javascript" src="../SiteAssets/js/jquery-1.11.0.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/1.10.9/js/jquery.dataTables.min.js"></script>
<link href="https://cdn.datatables.net/1.10.9/css/jquery.dataTables.min.css" rel="Stylesheet" type="text/css" />
<script type="text/javascript" src="../SiteAssets/CustomerJqueryDatatableAjax.js"></script>
</br />
<hr />
</br />
<div id="CustomerPanel">
<table style="width: 100%;">
<tr>
<td>
<table id="CustomerTable" style="width: 100%">
<thead>
<tr>
<th>Name</th>
<th>Address</th>
<th>Home Phone</th>
<th>Mob No.</th>
<th>Email</th>
<th>Organization</th>
<th>Role</th>
</tr>
</thead>
</table>
</td>
</tr>
</table>
</div>
Below will be my entire CustomerJqueryDatatableAjax.js code,
$(document).ready(function() {
var rowCount= 5;
$('#CustomerTable').dataTable({
"sProcessing": "Retrieving Customers...",
"bSort": true,
"bLengthChange": false,
"bFilter": false,
"iDisplayLength": rowCount,
"bServerSide": true,
"sPaginationType": "full_numbers",
"sAjaxDataProp": "d.results",
"bAutoWidth": false,
"aaSorting": [[1, 'desc']],
"aoColumnDefs": [
{
"aTargets": [0],
"mData": function (source, type, val) {
return source.CustomerName
}
},
{
"aTargets": [1],
"mData": function (source, type, val) {
return source.Address }
},
{
"aTargets": [2],
"mData": function (source, type, val) {
return source.HomePhone}
},
{
"aTargets": [3],
"mData": function (source, type, val) {
return source.MobileNumber}
},
{
"aTargets": [4],
"mData": function (source, type, val) {
return source.Email}
},
{
"aTargets": [5],
"mData": function (source, type, val) {
return source.Organization}
},
{
"aTargets": [6],
"mData": function (source, type, val) {
return source.RolesValue}
}
],
"fnServerData": function (sSource, aoData, fnCallback, oSettings) {
var restQuery = "../_vti_bin/listdata.svc/Customers?$select=Id,CustomerName,Address,HomePhone,MobileNumber,Email,Organization,RolesValue&$inlinecount=allpages&$top=" + rowCount;
if (oSettings._iDisplayStart > 0) {
restQuery = restQuery + "&$skip=" + oSettings._iDisplayStart
}
var restSortParams = "&$orderby=";
var sortCol = oSettings.aaSorting[0][0];
var sortDir = oSettings.aaSorting[0][1];
if (sortCol == 0) {
restSortParams = restSortParams + "CustomerName";
}
else if (sortCol == 1) {
restSortParams = restSortParams + "Address";
}
else if (sortCol == 2) {
restSortParams = restSortParams + "HomePhone";
}
else if (sortCol == 3) {
restSortParams = restSortParams + "MobileNumber";
}
else if (sortCol == 4) {
restSortParams = restSortParams + "Email";
}
else if (sortCol == 5) {
restSortParams = restSortParams + "Organization";
}
else if (sortCol == 6) {
restSortParams = restSortParams + "RolesValue";
}
restSortParams = restSortParams + " " + sortDir;
restQuery = restQuery + restSortParams;
$.ajax({
"dataType": "json",
"type": "GET",
"url": restQuery,
"data": aoData,
"success": function (json) {
json["sEcho"] = oSettings.iDraw.toString();
json["iTotalRecords"] = json.d.__count;
json["iTotalDisplayRecords"] = json.d.__count;
fnCallback(json);
}
});
}
});
});
Happy SharePointing !
No comments:
Post a Comment