Pagination - nreco/data GitHub Wiki
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;