public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber)
{
Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
Check.Require(topCount > 0, "topCount must > 0!");
if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null)
{
where.SetOrderBy(new KeyValuePair<string,bool>[] { new KeyValuePair<string,bool>(identyColumn, false) });
}
if (topCount == int.MaxValue && skipCount == 0)
{
return CreateSelectCommand(where, columns);
}
else
{
DbCommand cmd = CreateSelectCommand(where, columns);
if (skipCount == 0)
{
cmd.CommandText += " LIMIT " + topCount;
}
else
{
cmd.CommandText += " LIMIT " + skipCount;
cmd.CommandText += "," + topCount;
}
return cmd;
}
}
public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber)
{
Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
Check.Require(topCount > 0, "topCount must > 0!");
if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null)
{
where.SetOrderBy(new KeyValuePair <string, bool>[] { new KeyValuePair <string, bool>(identyColumn, false) });
}
if (topCount == int.MaxValue && skipCount == 0)
{
return(CreateSelectCommand(where, columns));
}
else
{
DbCommand cmd = CreateSelectCommand(where, columns);
if (skipCount == 0)
{
cmd.CommandText += " LIMIT " + topCount;
}
else
{
cmd.CommandText += " LIMIT " + skipCount;
cmd.CommandText += "," + topCount;
}
return(cmd);
}
}
public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber)
{
//Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
//Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
//Check.Require(topCount > 0, "topCount must > 0!");
if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null)
{
where.SetOrderBy(new KeyValuePair <string, bool>[] { new KeyValuePair <string, bool>(identyColumn, false) });
}
if (topCount == int.MaxValue && skipCount == 0)
{
return(CreateSelectCommand(where, columns));
}
else if (skipCount == 0)
{
return(CreateSelectTopCommand(where, columns, topCount));
}
else
{
//Check.Require(!string.IsNullOrEmpty(identyColumn), "identyColumn could not be null or empty!");
identyColumn = ColumnFormatter.ValidColumnName(identyColumn);
if (identyColumnIsNumber && where.OrderByStartsWith(identyColumn) && (string.IsNullOrEmpty(where.GroupBy) || where.GroupBy == identyColumn))
{
return(CreateSelectRangeCommandForSortedRows(where, columns, topCount, skipCount, identyColumn, where.OrderByStartsWith(identyColumn + " DESC")));
}
else
{
return(CreateSelectRangeCommandForUnsortedRows(where, columns, topCount, skipCount, identyColumn));
}
}
}
public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber)
{
Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
Check.Require(topCount > 0, "topCount must > 0!");
if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null)
{
where.SetOrderBy(new KeyValuePair<string,bool>[] { new KeyValuePair<string,bool>(identyColumn, false) });
}
if (topCount == int.MaxValue && skipCount == 0)
{
return CreateSelectCommand(where, columns);
}
else if (skipCount == 0)
{
return CreateSelectTopCommand(where, columns, topCount);
}
else
{
Check.Require(!string.IsNullOrEmpty(identyColumn), "identyColumn could not be null or empty!");
identyColumn = ColumnFormatter.ValidColumnName(identyColumn);
if (identyColumnIsNumber && where.OrderByStartsWith(identyColumn) && (string.IsNullOrEmpty(where.GroupBy) || where.GroupBy == identyColumn))
{
return CreateSelectRangeCommandForSortedRows(where, columns, topCount, skipCount, identyColumn, where.OrderByStartsWith(identyColumn + " DESC"));
}
else
{
return CreateSelectRangeCommandForUnsortedRows(where, columns, topCount, skipCount, identyColumn);
}
}
}
public SelectSqlSection OrderBy(params OrderByClip[] orderBys)
{
if (orderBys != null && orderBys.Length > 0)
{
if (orderBys.Length == 1)
{
whereClip.SetOrderBy(orderBys[0].OrderBys.ToArray());
}
else
{
OrderByClip combinedOrderBy = new OrderByClip();
for (int i = 0; i < orderBys.Length; ++i)
{
combinedOrderBy = combinedOrderBy & orderBys[i];
}
whereClip.SetOrderBy(combinedOrderBy.OrderBys.ToArray());
}
}
return(this);
}
public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber)
{
//Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
//Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
//Check.Require(topCount > 0, "topCount must > 0!");
if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null)
{
where.SetOrderBy(new KeyValuePair <string, bool>[] { new KeyValuePair <string, bool>(identyColumn, false) });
}
if (topCount == int.MaxValue && skipCount == 0)
{
return(CreateSelectCommand(where, columns));
}
else
{
//Check.Require(!string.IsNullOrEmpty(identyColumn), "identyColumn could not be null or empty!");
identyColumn = ColumnFormatter.ValidColumnName(identyColumn);
//page split algorithm using ROW_NUMBER() in Oracle9+
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT *");
//for (int i = 0; i < columns.Length; ++i)
//{
// sb.Append("[__T].[__C");
// sb.Append(i);
// sb.Append(']');
// if (i < columns.Length - 1)
// {
// sb.Append(',');
// }
//}
sb.Append(" FROM (");
sb.Append("SELECT ");
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(",ROW_NUMBER() OVER (ORDER BY ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(identyColumn);
}
else
{
sb.Append(where.OrderBy);
}
sb.Append(") AS [__Pos]");
sb.Append(" FROM ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(where.ToString());
}
else
{
lock (where)
{
string tempOrderBy = where.OrderBy;
where.OrderBy = null;
sb.Append(where.ToString());
where.OrderBy = tempOrderBy;
}
}
sb.Append(") [__T] WHERE [__T].[__Pos]>");
sb.Append(skipCount);
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append(" AND [__T].[__Pos]<=");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
}
public override DbCommand CreateSelectRangeCommand(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool identyColumnIsNumber)
{
Check.Require(((object)where) != null && where.From != null, "expr and expr.From could not be null!");
Check.Require(columns != null && columns.Length > 0, "columns could not be null or empty!");
Check.Require(topCount > 0, "topCount must > 0!");
if (string.IsNullOrEmpty(where.OrderBy) && identyColumn != null)
{
where.SetOrderBy(new KeyValuePair<string,bool>[] { new KeyValuePair<string,bool>(identyColumn, false) });
}
if (topCount == int.MaxValue && skipCount == 0)
{
return CreateSelectCommand(where, columns);
}
else
{
Check.Require(!string.IsNullOrEmpty(identyColumn), "identyColumn could not be null or empty!");
identyColumn = ColumnFormatter.ValidColumnName(identyColumn);
//page split algorithm using ROWNUMBER()
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder();
sb.Append("SELECT *");
//for (int i = 0; i < columns.Length; ++i)
//{
// sb.Append("[__T].[__C");
// sb.Append(i);
// sb.Append(']');
// if (i < columns.Length - 1)
// {
// sb.Append(',');
// }
//}
sb.Append(" FROM (");
sb.Append("SELECT ");
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(",ROWNUMBER() OVER (ORDER BY ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(identyColumn);
}
else
{
sb.Append(where.OrderBy);
}
sb.Append(") AS [__Pos]");
sb.Append(" FROM ");
if (string.IsNullOrEmpty(where.OrderBy))
{
sb.Append(where.ToString());
}
else
{
lock (where)
{
string tempOrderBy = where.OrderBy;
where.OrderBy = null;
sb.Append(where.ToString());
where.OrderBy = tempOrderBy;
}
}
sb.Append(") [__T] WHERE [__T].[__Pos]>");
sb.Append(skipCount);
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append(" AND [__T].[__Pos]<=");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return cmd;
}
}