/// <summary>
/// Renders a SELECT statement which a result-set page
/// </summary>
/// <param name="pageIndex">The zero based index of the page to be returned</param>
/// <param name="pageSize">The size of a page</param>
/// <param name="totalRowCount">Total number of rows the query would yeild if not paged</param>
/// <param name="query">Query definition to apply paging on</param>
/// <returns>Generated SQL statement</returns>
/// <remarks>
/// To generate pagination SQL you must supply <paramref name="totalRowCount"/>.
/// To aquire the total number of rows use the <see cref="RenderRowCount"/> method.
/// </remarks>
public virtual string RenderPage(int pageIndex, int pageSize, int totalRowCount, SelectQuery query)
{
if (query.OrderByTerms.Count == 0)
{
throw new InvalidQueryException("OrderBy must be specified for paging to work.");
}
int currentPageSize = pageSize;
if (pageSize * (pageIndex + 1) > totalRowCount)
{
currentPageSize = totalRowCount - pageSize * pageIndex;
}
if (currentPageSize < 0)
{
currentPageSize = 0;
}
SelectQuery baseQuery = query.Clone();
baseQuery.Top = (pageIndex + 1) * pageSize;
//baseQuery.Columns.Add(new SelectColumn("*"));
foreach (OrderByTerm term in baseQuery.OrderByTerms)
{
baseQuery.Columns.Add(
new SelectColumn(term.Field, term.Table, FormatSortFieldName(term.Field),
SqlAggregationFunction.None));
}
string baseSql = RenderSelect(baseQuery);
SelectQuery reverseQuery = new SelectQuery();
reverseQuery.Columns.Add(new SelectColumn("*"));
reverseQuery.Top = currentPageSize;
reverseQuery.FromClause.BaseTable = FromTerm.SubQuery(baseSql, "r");
ApplyOrderBy(baseQuery.OrderByTerms, reverseQuery, false, reverseQuery.FromClause.BaseTable);
string reverseSql = RenderSelect(reverseQuery);
SelectQuery forwardQuery = new SelectQuery();
foreach (SelectColumn originalCol in query.Columns)
{
FromTerm forwardTable = FromTerm.TermRef("f");
SqlExpression expr = null;
if (originalCol.ColumnAlias != null)
{
expr = SqlExpression.Field(originalCol.ColumnAlias, forwardTable);
}
else if (originalCol.Expression.Type == SqlExpressionType.Field ||
originalCol.Expression.Type == SqlExpressionType.Constant)
{
expr = SqlExpression.Field((string)originalCol.Expression.Value, forwardTable);
}
if (expr != null)
{
forwardQuery.Columns.Add(new SelectColumn(expr, originalCol.ColumnAlias));
}
}
forwardQuery.FromClause.BaseTable = FromTerm.SubQuery(reverseSql, "f");
ApplyOrderBy(baseQuery.OrderByTerms, forwardQuery, true, forwardQuery.FromClause.BaseTable);
return(RenderSelect(forwardQuery));
}
