Server-Side Paging with PetaPoco and DataTables

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.

Fork me on GitHub

Be Sociable, Share!

About Todd Wood

Solution Architect and owner of Wood Consulting Practice, LLC. C#, ASP.NET MVC, Oracle, RoR, and Linux et al rolled up into one on my Mac.
This entry was posted in DataTables, PetaPoco and tagged , . Bookmark the permalink.
  • http://twitter.com/BenHoltsclaw Ben Holtsclaw

    How would you work a multi-poco query into that sample? I haven’t seen anything showing how to do paging with multi-poco.

    • http://twitter.com/iToddWood Todd A. Wood

      What is an example of a multi-poco result that you are thinking of that would be represented in a grid?

      The typical PetaPoco usage returns a single POCO and the PetaPoco Fetch() feature to combine 1:M and M:M does not work with the Page() feature that I am aware. If you can define a SQL query for the grid row then a combined View Model POCO to support it is an option. 

  • Pingback: PetaPoco Custom SQL Paging | Implement IToddWood

  • Kevin Jensen

    Nice work here…

    • http://www.woodcp.com/ Todd A. Wood

      Thank you Kevin.

  • Matt Pontel

    Great write up – This should really take off!
    I’m trying to implement it myself but coming across an issue where my DataTablesPageRequest pageRequest

    isn’t being populated when it makes it’s first request for data at my controller.  Was this something you came across ? 

    • http://www.woodcp.com/ Todd A. Wood

      Thanks Matt,

      I did not encounter any issues similar to that scenario. Is the first callback to the controller coming through as null? I would first check the Chrome console for js errors, XHR network request for what was posted and the response if any, and then the Model Binding C# method if it’s clear the post is sending JSON and functioning.

      I am glad you find this useful.

      • Matt Pontel

         I can see in Firebug the proper request being made by the javsscript but if I catch the pageRequest when it hits the controller all the fields are null/0.

        Just thought perhaps there was something quirky that perhaps was missing.  I’ll keep having a hunt through.

        Obviously, with no columns being specified, I’m getting null errors in the data access class when it tried to sort the data.

      • Matt Pontel

        PS:
        The problem ended up being me forgetting to add to Globals.asax

                protected void Application_Start()
                {
                    AreaRegistration.RegisterAllAreas();

        //FORGOT THIS :)            ModelBinders.Binders.Add(typeof(DataTablesPageRequest), new DataTablesModelBinder());

                    RegisterGlobalFilters(GlobalFilters.Filters);
                    RegisterRoutes(RouteTable.Routes);
                }

  • http://twitter.com/NaiNyshaH Hussnain Raza

    outstanding work

  • boofar

    hi, this is awesome. just wanted to tell you that i appreciate all the posts on petapoco.