Introduction: Server Side or Client Side Pagination
Often when a project needs to display a collection of data on the client an earlier question is "do we need pagination" and if the answer is "why, yes!" then another question with bigger implications pops up: "will the pagination take place on the client or on the server?". The answer now depends on requirements: Data set size, is sorting and filtering required.
Client side pagination is great for displaying a small data set with a few hundred records. A simple AJAX call to the server should suffice. Loading subsequence pages, and sorting and filtering would be very fast because the entire collection resides in memory on the client side.
However, when the data set is very large, or large enough, like a few thousand records, server side pagination gains more relevance. With server side pagination large amount of data do not need to be send down the wire to the client improving the client perceived response time. Filtering and sorting of the data on the server is also desirable and will increase performance when dealing with large data sets. As a result pages load faster than if the client were dealing with the entire collection.
Client Side Pagination with DataTables
DataTables is a very simple to use jQuery AJAX enabled plug-in which with very little effort can display a collection of objects in a grid, or table, and can also provide paging, sorting and filtering.The above function is very simple and it loads a collection of products to DataTables. In the above scenario there is no ajax to the server for pagination, sorting, or filtering and instead DataTables will do everything using the local collection of products. For a POST/PUT (add new/update existing) the collection will need to be retrieved again and the table needs to be recreated again.
The "products" json would look like something like this.
Server Side Pagination
In the case that we have thousands and more thousands of products then the above approach will cause problems and make the application seem slow.The solution is Server Side pagination and serve a page of data to the client on demand. Server side pagination would be simple to implement using LINQ Enumerable.Skip(number_of_records_to_skip) to bypass a specific number of records and Enumerable.Take(number_of_records_to_return) to return a specific number of records, "the page".
An alternative would be to use PagedList, which is essentially the same with the difference that instead of providing the number of records to skip PagedList requires the page number and the number of records to return:
Using Skip and Take
foreach (var product in queryProducts.Skip(model.RecordNumber).Take(pageMaxSize))
{
// TODO
}
Using PagedList
...The server side code looks like this:
var pageNumber = (iDisplayStart / iDisplayLength) + 1;
...
foreach (var product in queryProducts.ToPagedList(pageNumber, pageMaxSize))
{
// TODO
}
The above method is a WEB API action. The way the WEB API service was setup falls outside of the scope of this note. I leave you with a reference related to WEB API, in particular how I setup Dependency Injection for my ProductController in the case the reader is interested in DI to a WEB API controller.
Because it is relevant to the next section related to how the data table is setup, I would like to show you the DTO that the action is returning.
This DTO contains two properties which are very important for DataTables to work correctly and display your data and paginate as you intend. The properties are Count which is serialized as "iTotalRecords" and TotalDisplayRecords which is serialized as "iTotalDisplayRecords". iTotalRecords is the total number or records which are the database; this is the entire collection beforeapplying filtering and iTotalDisplayRecords is the count of records after filtering has been applied to the data. For this reason we need to execute the IQueryable before the pages are processed in the for-each loop.
Setting up the DataTable is now a little different because it is now ajax enabled. Note that the configuration object now contains two important parameters 'bServerSide" and "sAjaxSource" to instruct DataTables that processing is now done server side. Now it is up to the server side to do the heavy lifting. The configuration sPaginationType full_numbers is telling DataTables to display numbers for each page for the user to access directly a specific page.
This example is very simple. I learned about the parameters that DataTables sends to the server in the ajax call by inspecting the request.
Conclusion
- Use DataTables for a cool and efficient way to display data in a grid
- Data pagination is done server side using an IQueriable
extension, PagedList. PagedList can be obtained through Nuget. - Data pagination can also be carried out with LINQ Skip and Take and it is very simple.
- DataTables manages: Pagination, Filtering, and Sorting of the data by sending to a server action parameters containing the required information: page size, sort order, filter by, etc.
- Paginate on the server in the case that it makes sense due to the size of the data.
References
- LINQ Skip: http://msdn.microsoft.com/en-us/library/bb358985(v=vs.110).aspx
- LINK Take: http://msdn.microsoft.com/en-us/library/bb503062(v=vs.110).aspx
- PagedList: PagedList is a library that enables you to easily take an IEnumerable/IQueryable, chop it up into "pages". The Paged:List project also providesm with an Html Helper, PagedList.Mvc, for displaying the data on the client.
- DataTables: DataTables is a very simple to use jQuery plugin. This data table plug in can be loaded with json data resulting from an ajax call or it can also perform server side processing.
- jqGrid: Is an AJAX enabled javascript control which can be used as an alternative to DataTables. It is also very simple to use, very well documented and supported by the community.
- Sorting, Filtering, and Paging with the Entity Framework in an ASP.NET MVC Application: A nice article in the asp.net site which features PagedList and PagedList.Mvc.
- Supporting OData Query Options: Paginating OData
- Setup DI in a WEB API Controller
