protected override string GetSqlForPagination(string tableName, string columnNames, string orderColumns, string whereClause, long pageNumber, int pageSize)
{
var startEndRowNumber = PaginationHelper.GetStartEndRowNumber(pageNumber, pageSize);
var pagedSql = $@"SELECT {columnNames}
FROM {tableName}
{whereClause}
ORDER BY {(!string.IsNullOrEmpty(orderColumns) ? orderColumns : this.GetDefaultOrder())}
LIMIT { startEndRowNumber.StartRowNumber - 1 } , {pageSize}";
return(pagedSql);
}
protected override string GetPagedSql(string tableName, string columnNames, string primaryKeyColumns, string whereClause, long pageNumber, int pageSize)
{
var startEndRowNumber = PaginationHelper.GetStartEndRowNumber(pageNumber, pageSize);
var pagedSql = $@"SELECT {columnNames}
FROM {tableName}
{whereClause}
ORDER BY {(!string.IsNullOrEmpty(primaryKeyColumns) ? primaryKeyColumns : "1")}
LIMIT { startEndRowNumber.StartRowNumber - 1 } , {pageSize}";
return(pagedSql);
}
protected override string GetPagedSql(string tableName, string columnNames, string primaryKeyColumns, string whereClause, long pageNumber, int pageSize)
{
var startEndRowNumber = PaginationHelper.GetStartEndRowNumber(pageNumber, pageSize);
string pagedSql = $@"with PagedRecords as
(
SELECT TOP 100 PERCENT {columnNames}, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROWNUMBER
FROM {tableName}
{whereClause}
)
SELECT *
FROM PagedRecords
WHERE ROWNUMBER BETWEEN {startEndRowNumber.StartRowNumber} AND {startEndRowNumber.EndRowNumber}" ;
return(pagedSql);
}