Database Pagination is something I have coded at every employer I have worked at. My approach has changed over the years, here is a look at how I would go about it now.
The requirements for this incarnation:
- One database query should go across the wire, and we should both the Total Row Count as well as the Rows of the current page. Only the rows on a page should be returned from the database.
- Support for querying ordering columns of more than one SQL type
- SQL server database
Note the query uses offset and fetch clauses to limit the number of rows sent over the wire.
Things that will be improved shortly:
- support for resilency
- support for isolation level / read uncommitted (https://www.carlrippon.com/scalable-and-performant-asp-net-core-web-apis-sql-server-isolation-level/)
- making more generic as more use cases are used
- validation of invalid requests
If you have any feedback please leave it on this blog or the GitHub Gist.