public override string GetPagedQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
{
if (builder == null)
{
throw new ArgumentNullException(nameof(builder));
}
if (selectCommand == null)
{
throw new ArgumentNullException(nameof(selectCommand));
}
return(GetFirstSkipQuery(builder, selectCommand, ref paramIndex, fromRowIndex, toRowIndex));
}
protected string GetLimitOffsetSelectQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
{
StringBuilder commandText = new StringBuilder();
builder.GetSelectQuery(selectCommand, ref paramIndex, commandText, 0);
string limitParameterName;
var limitParam = builder.CreateIn32Parameter(toRowIndex - fromRowIndex + 1, ref paramIndex, out limitParameterName);
selectCommand.Parameters.Add(limitParam);
string offsetParameterName;
var offsetParam = builder.CreateIn32Parameter(fromRowIndex, ref paramIndex, out offsetParameterName);
selectCommand.Parameters.Add(offsetParam);
commandText.Append("\nLIMIT ").Append(limitParameterName).Append(" OFFSET ").Append(offsetParameterName);
return(commandText.ToString());
}
protected string GetFirstSkipQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
{
StringBuilder commandText = new StringBuilder();
string firstParameterName;
var firstParam = builder.CreateIn32Parameter(toRowIndex - fromRowIndex + 1, ref paramIndex, out firstParameterName);
selectCommand.Parameters.Add(firstParam);
string skipParameterName;
var skipParam = builder.CreateIn32Parameter(fromRowIndex, ref paramIndex, out skipParameterName);
selectCommand.Parameters.Add(skipParam);
commandText.Append("SELECT FIRST ").Append(firstParameterName).Append(" SKIP ").Append(skipParameterName).Append(' ');
builder.GetSelectQuery(selectCommand, ref paramIndex, commandText, 0, false);
var query = commandText.ToString();
return(query);
}
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)
{
var metadata = builder.QueryLite.EntityType.GetEntityMetadata();
if (metadata == null)
{
throw new InvalidOperationException("Entity " + builder.QueryLite.EntityType.Name + " has no metadata");
}
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__
*/
string columnList = builder.GetColumnList();
bool isStar = columnList == "*";
commandText.Append("SELECT ");
if (isStar)
{
commandText.Append("*");
}
else
{
commandText.NewIndentedLine(1).Append(columnList);
}
commandText.Append("\nFROM")
.NewIndentedLine(1).Append('(')
.NewIndentedLine(2).Append("SELECT")
.NewIndentedLine(3).Append(" *, ROW_NUMBER() OVER (ORDER BY ").Append(builder.GetSort()).Append(") AS __RowNumber__")
.NewIndentedLine(2).Append("FROM")
.NewIndentedLine(3).Append(builder.GetFromClauseContent(selectCommand, ref paramIndex, 3));
bool hasWhereClause = builder.QueryLite.Filter != null && !builder.QueryLite.Filter.IsEmpty();
if (hasWhereClause)
{
commandText.NewIndentedLine(2)
.Append("WHERE")
.NewIndentedLine(3).Append(builder.GetFilter(selectCommand, ref paramIndex, builder.QueryLite.Filter, 3, false));
}
commandText.NewIndentedLine(1).Append(") T");
string fromParameterName;
IDbDataParameter fromParameter = builder.CreateIn32Parameter(fromRowIndex + 1, ref paramIndex, out fromParameterName);
selectCommand.Parameters.Add(fromParameter);
string toParameterName;
IDbDataParameter toParameter = builder.CreateIn32Parameter(toRowIndex + 1, ref paramIndex, out toParameterName);
selectCommand.Parameters.Add(toParameter);
commandText.Append("\nWHERE")
.NewIndentedLine(1).Append("__RowNumber__ BETWEEN ")
.Append(fromParameterName).Append(" AND ").Append(toParameterName);
commandText.Append("\nORDER BY")
.NewIndentedLine(1).Append("__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();
if (builder.QueryLite.Alias == null)
{
builder.QueryLite.Alias = new Alias("IT", builder.QueryLite.EntityType);
}
var aliasName = builder.QueryLite.Alias.Name;
string columnList = builder.GetColumnList();
bool isStar = columnList == "*";
commandText.Append("SELECT ");
if (isStar)
{
commandText.Append("*");
}
else
{
commandText.NewIndentedLine(1).Append(columnList);
}
commandText.Append("\n")
.Append("FROM")
.NewIndentedLine(1).Append('(')
.NewIndentedLine(2).Append("SELECT ").Append(aliasName).Append(".*, rownum AS row_number__")
.NewIndentedLine(2).Append("FROM ")
.NewIndentedLine(3).Append(builder.GetFromClauseContent(selectCommand, ref paramIndex, 3));
bool hasWhereClause = builder.QueryLite.Filter != null && !builder.QueryLite.Filter.IsEmpty();
if (hasWhereClause)
{
commandText.NewIndentedLine(2).Append("WHERE").NewIndentedLine(3).Append(builder.GetFilter(selectCommand, ref paramIndex, builder.QueryLite.Filter, 3, false));
}
if (builder.QueryLite.Sort != null && builder.QueryLite.Sort.Count > 0)
{
commandText.NewIndentedLine(2).Append("ORDER BY").NewIndentedLine(3).Append(builder.GetSort());
}
commandText.NewIndentedLine(1).Append(") T\n");
string fromParameterName;
IDbDataParameter fromParameter = builder.CreateIn32Parameter(fromRowIndex + 1, ref paramIndex, out fromParameterName);
selectCommand.Parameters.Add(fromParameter);
string toParameterName;
IDbDataParameter toParameter = builder.CreateIn32Parameter(toRowIndex + 1, ref paramIndex, out toParameterName);
selectCommand.Parameters.Add(toParameter);
commandText.Append("WHERE")
.NewIndentedLine(1).Append("row_number__ BETWEEN ")
.Append(fromParameterName)
.Append(" AND ").Append(toParameterName);
return(commandText.ToString());
}
public override string GetPagedQuery(AbstractQueryBuilder builder, DbCommand selectCommand, ref int paramIndex, int fromRowIndex, int toRowIndex)
{
return(GetFirstSkipQuery(builder, selectCommand, ref paramIndex, fromRowIndex, toRowIndex));
}
