protected string GetLimitOffsetSelectQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
{
StringBuilder commandText = new StringBuilder();
builder.GetSelectQuery(selectCommand, ref paramIndex, commandText);
var limitParam = builder.CreateIn32Parameter(toRowIndex - fromRowIndex + 1, ref paramIndex);
selectCommand.Parameters.Add(limitParam);
var offsetParam = builder.CreateIn32Parameter(fromRowIndex, ref paramIndex);
selectCommand.Parameters.Add(offsetParam);
commandText.Append("\nLIMIT ").Append(limitParam.ParameterName).Append(" OFFSET ").Append(offsetParam.ParameterName);
return commandText.ToString();
}
public override string GetPagedQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
{
StringBuilder commandText = new StringBuilder();
bool hasOrderbyClause = builder.QueryLite.Sort != null && builder.QueryLite.Sort.Count > 0;
if (!hasOrderbyClause)
{
foreach (var primaryKey in builder.QueryLite.EntityType.GetEntityMetadata().PrimaryKeyPropertyNames)
{
if (builder.QueryLite.Sort == null) builder.QueryLite.Sort = new List<SortDescriptor>();
builder.QueryLite.Sort.Add(new SortDescriptor(primaryKey));
}
if (builder.QueryLite.Sort == null || builder.QueryLite.Sort.Count == 0)
{
throw new InvalidOperationException("OrderBy or primary key are requiered for a SQL Server paged query");
}
}
/*
SELECT *
FROM (
SELECT *, ROW_NUMBER() OVER (ORDER BY CompanyName) AS __RowNumber__,
FROM dbo.Customers C
WHERE C.Country = 'USA'
) T
WHERE __RowNumber__ BETWEEN 1 AND 5
ORDER BY __RowNumber__
*/
commandText.Append("\nSELECT ").Append(builder.GetColumnList()).Append("\n")
.Append("FROM (\n")
.Append("SELECT *, ROW_NUMBER() OVER (ORDER BY ").Append(builder.GetSort()).Append(") AS __RowNumber__\n")
.Append("FROM ").Append(builder.GetFromClauseContent(selectCommand, ref paramIndex)).Append("\n");
bool hasWhereClause = builder.QueryLite.Filter != null && !builder.QueryLite.Filter.IsEmpty();
if (hasWhereClause)
{
commandText.Append("\nWHERE\n ").Append(builder.GetFilter(selectCommand, ref paramIndex, builder.QueryLite.Filter));
}
commandText.Append("\n) T\n");
IDbDataParameter fromParameter = builder.CreateIn32Parameter(fromRowIndex + 1, ref paramIndex);
selectCommand.Parameters.Add(fromParameter);
IDbDataParameter toParameter = builder.CreateIn32Parameter(toRowIndex + 1, ref paramIndex);
selectCommand.Parameters.Add(toParameter);
commandText.Append("WHERE __RowNumber__ BETWEEN ").Append(fromParameter.ParameterName).Append(" AND ").Append(toParameter.ParameterName);
commandText.Append("\nORDER BY __RowNumber__;");
builder.SetOptions(commandText);
return commandText.ToString();
}
public virtual string GetPagedQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
{
return GetLimitOffsetSelectQuery(builder, selectCommand, ref paramIndex, fromRowIndex, toRowIndex);
}
public override string GetPagedQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
{
/*
SELECT *
FROM (
SELECT od.*, rownum AS RowNumber__
FROM order_details od
) T
WHERE RowNumber__ between 10 and 19;
*/
var commandText = new StringBuilder();
commandText.Append("\nSELECT ").Append(builder.GetColumnList()).Append("\n")
.Append("FROM (\n")
.Append("SELECT IT.*, rownum AS row_number__\n")
.Append("FROM ").Append(builder.GetFromClauseContent(selectCommand, ref paramIndex)).Append(" IT\n");
bool hasWhereClause = builder.QueryLite.Filter != null && !builder.QueryLite.Filter.IsEmpty();
if (hasWhereClause)
{
commandText.Append("\nWHERE\n ").Append(builder.GetFilter(selectCommand, ref paramIndex, builder.QueryLite.Filter));
}
commandText.Append("\n) T\n");
IDbDataParameter fromParameter = builder.CreateIn32Parameter(fromRowIndex + 1, ref paramIndex);
selectCommand.Parameters.Add(fromParameter);
IDbDataParameter toParameter = builder.CreateIn32Parameter(toRowIndex + 1, ref paramIndex);
selectCommand.Parameters.Add(toParameter);
commandText.Append("WHERE row_number__ BETWEEN ")
.Append(fromParameter.ParameterName)
.Append(" AND ").Append(toParameter.ParameterName);
return commandText.ToString();
}
