Pagination

Pagination Setup

When query has non-zero Query.RecordOffset NReco.Data skips first N records while reading from IDataReader, and reads only number of rows specified with Query.RecordCount. This happens because default SELECT template defined in DbCommandBuilder.SelectTemplate uses only standard SQL that can be executed by any SQL database:

SELECT @columns FROM @table@where[ WHERE {0}]@orderby[ ORDER BY {0}]

It is possible to configure database-level pagination (which is much more efficient) by defining DB-specific SQL template.

SQL Server

dbCmdBuilder.SelectTemplate = "SELECT @columns FROM @table @where[ WHERE {0}] @orderby[ ORDER BY {0}; ORDER BY (SELECT NULL)] @recordoffset[OFFSET {0} ROWS;OFFSET 0 ROWS] @recordcount[FETCH NEXT {0} ROWS ONLY]";
dbAdapter.ApplyOffset = false;

or, if you want to use TOP syntax:

dbCmdBuilder.SelectTemplate = "SELECT @recordtop[TOP {0}] @columns FROM @table @where[ WHERE {0}] @orderby[ ORDER BY {0}]";
dbAdapter.ApplyOffset = true; // skip first N rows by cursor move

MySql, PostgreSql, Sqlite

dbCmdBuilder.SelectTemplate = "SELECT @columns FROM @table@where[ WHERE {0}]@orderby[ ORDER BY {0}] @recordcount[LIMIT {0}] @recordoffset[OFFSET {0}]";
dbAdapter.ApplyOffset = false;