public override ISQLBuilder Select(string tableName, IEnumerable <object> columns = null, bool distinct = false, int?limit = null, int?offset = null, IEnumerable <ColumnValue> columnMatches = null, string whereClause = null, string orderByClause = null, bool endStatement = false)
{
if (offset.HasValue)
{
if (!limit.HasValue)
{
throw new NotSupportedException("When selecting by an offset the limit parameter must also be supplied for this SQL Builder");
}
const string pagedQuery =
@"WITH __UNPAGED_RESULT AS (
{0}
), __PAGED_RESULT AS (
SELECT *, ROW_NUMBER() OVER (ORDER BY {3}) AS __ROWNUM FROM __UNPAGED_RESULT
)
SELECT * FROM __PAGED_RESULT WHERE __ROWNUM > {2} AND __ROWNUM <= ({2} + {1})";
Emit(
pagedQuery,
SQLBuilderCommand.Generic(b => b.Select(tableName, columns: columns, distinct: distinct, limit: null, offset: null, columnMatches: columnMatches, whereClause: whereClause, orderByClause: null, endStatement: false)),
limit.Value,
offset.Value,
string.IsNullOrEmpty(orderByClause) ? "@@IDENTITY" : orderByClause
);
if (endStatement)
{
base.EndOfStatement();
}
return(this);
}
return(base.Select(tableName, columns: columns, distinct: distinct, limit: limit, offset: null, columnMatches: columnMatches, whereClause: whereClause, orderByClause: orderByClause, endStatement: endStatement));
}
