Server-Side Paging with PetaPoco and DataTables

3 minute read

TL;DR – Server-side paging of table data is not only faster and more responsive, but very easy to implement with PetaPoco and DataTables. I will demonstrate how millisecond response times are still possible even when dealing with a million rows of data.

This blog post can be forked on GitHub: tawman / PetaPocoPage.

Sample Web Site and Data

For the sample web site, I used the Spawner Data Generator tool to generate 1,000,000 rows of test data for use in this example. As you can see from the timings, each table grid page request is limited to approximately 1.25 KB of data and a response time around 75 milliseconds.

SiteTimings

On my current project, we initially used jQuery tablesorter with a table loading between 1,000 and 1,500 detail records for a report. There was a noticeable delay of a couple seconds while the data loaded. Obviously, we needed to implement server-side paging and I knew PetaPoco could help on the backend. However, tablesorter had some other issues with searching paged tables that lead me to discover DataTables

DataTables

DataTables is a great extensible jQuery plug-in and satisfied our searching, paging, and server-side processing requirements. The only negative issue I had is cosmetic since DataTables uses Hungarian notation. The other features and extensibility options shine through making DataTables an excellent client side UI choice.

Client Side Setup

Wiring up a view table with DataTables is very simple, and adding the server-side callbacks and Twitter Bootstrap formatting is also very straight forward. In my example view, there is very little markup required and actually no model is provided:

The sample project also includes some additional JavaScript to setup a 500 ms filtering delay before sending the search term back to the server, pagination styling, and CSS styling. Fortunately, the DataTables library is thoroughly documented on its web site along with these extension configurations.

Server Side Setup

DataTables will POST a JSON request back to the server Controller that I process with a custom IModelBinder to keep the Controller code clean and remove the Hungarian notation:

The combination of the IModelBinder and a helper method to format the DataTable response keeps the Controller action code simple:

The DataTable response formatter simply combines the DataTablesPageRequest object with the PetaPoco Page<Customer> object into a JSON response expected by DataTables:

Fortunately, the DataTable request and response object formats are very compatible with how PetaPoco support paging.

PetaPoco

PetaPoco makes it very simple and efficient to issue paged queries to SQL Server and it handles the query rewrite to use ROW_NUMBER for you. The resulting PetaPoco Page<T> object makes it easy to interface with the DataTables response object:


public class Page<T> where T:new()
{
    public long CurrentPage { get; set; }
    public long ItemsPerPage { get; set; }
    public long TotalPages { get; set; }
    public long TotalItems { get; set; }
    public List<T> Items { get; set; }
}

To support the DataTables searching and ordering in my sample project, I used some helper methods to apply the search criteria against all columns and order by the columns selected. The resulting repository method to handle the PetaPoco Page<T> query request is quite straightforward:

In the end, it does not matter if you are dealing with 100, 1,000, or 1,000,000 rows of data. You can still achieve fast page response times and low latency calls to your backend database to serve data up one page at a time. The following timings are from pressing the Next page link 7 times:

Timings

Conclusion

The million row sample data is not a practical use case and merely used to illustrate a point. In fact, I reduced the sample data down to 4,000 rows for publishing on GitHub as a 250 MB database is not very clone friendly. Have a look at the project source on GitHub: tawman / PetaPocoPage and I hope it provides a useful reference for someone faced with a similar challenge.

DataTables and PetaPoco were made to page together.