protected virtual DbCommand CreateSelectTopCommand(WhereClip where, string[] columns, int topCount)
{
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("SELECT TOP ");
sb.Append(topCount);
sb.Append(' ');
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(" FROM ");
sb.Append(where.ToString());
AddExpressionParameters(where, cmd);
cmd.CommandText = SqlQueryUtils.ReplaceDatabaseTokens(sb.ToString(), leftToken, rightToken, paramPrefixToken, wildcharToken, wildsinglecharToken);
PrepareCommand(cmd);
return(cmd);
}
private string BuildParameterPrefix(string sqlCommandText)
{
if (string.IsNullOrEmpty(sqlCommandText) ||
string.IsNullOrWhiteSpace(sqlCommandText))
{
return(null);
}
return(SqlQueryUtils.ReplaceDatabaseTokens(sqlCommandText,
this.database.DBProvider.ParameterLeftToken,
this.database.DBProvider.ParameterRightToken,
this.database.DBProvider.ParameterPrefix,
this.database.DBProvider.WildCharToken,
this.database.DBProvider.WildSingleCharToken));
}
protected override string CreateSelectRangeStatementForUnsortedRows(string tableName, string where, string[] columns, string orderBy, string groupBy, int topCount, int skipCount, string identyColumn)
{
//SELECT TOP 10 *
//FROM TestTable
//WHERE (ID NOT IN
// (SELECT TOP 20 id
// FROM TestTable
// ORDER BY id))
//ORDER BY ID
StringBuilder outerSqlBuilder = new StringBuilder("SELECT ");
if (topCount < int.MaxValue)
{
outerSqlBuilder.Append("TOP ");
outerSqlBuilder.Append(topCount);
outerSqlBuilder.Append(' ');
}
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(outerSqlBuilder, columns[i], parameterLeftToken, parameterRightToken);
if (i < columns.Length - 1)
{
outerSqlBuilder.Append(',');
}
}
outerSqlBuilder.Append(" FROM ");
outerSqlBuilder.Append(tableName);
outerSqlBuilder.Append(" WHERE ");
StringBuilder innerWhereClipBuilder = new StringBuilder();
innerWhereClipBuilder.Append(tableName);
if (!string.IsNullOrEmpty(where) &&
!string.IsNullOrWhiteSpace(where))
{
innerWhereClipBuilder.Append(" WHERE " + where);
}
StringBuilder orderByGroupByBuilder = new StringBuilder();
if (!string.IsNullOrEmpty(orderBy) &&
!string.IsNullOrWhiteSpace(orderBy))
{
orderByGroupByBuilder.Append(" ORDER BY " + orderBy);
}
if (!string.IsNullOrEmpty(groupBy) &&
!string.IsNullOrWhiteSpace(groupBy))
{
orderByGroupByBuilder.Append(" GROUP BY " + groupBy);
}
innerWhereClipBuilder.Append(orderByGroupByBuilder.ToString());
#region Construct & extend CloneWhere
StringBuilder innerSqlBuilder = new StringBuilder();
innerSqlBuilder.Append(identyColumn);
innerSqlBuilder.Append(" NOT IN (SELECT TOP ");
innerSqlBuilder.Append(skipCount);
innerSqlBuilder.Append(' ');
innerSqlBuilder.Append(identyColumn);
innerSqlBuilder.Append(" FROM ");
innerSqlBuilder.Append(innerWhereClipBuilder.ToString());
innerSqlBuilder.Append(")");
string outerWhereSql = string.Empty;
if (where.Length == 0)
{
outerWhereSql = innerSqlBuilder.ToString();
}
else
{
outerWhereSql = "(" + where + ") AND " + innerSqlBuilder.ToString();
}
#endregion
outerSqlBuilder.Append(outerWhereSql);
outerSqlBuilder.Append(orderByGroupByBuilder.ToString());
return(SqlQueryUtils.ReplaceDatabaseTokens(outerSqlBuilder.ToString(), this.parameterLeftToken, this.parameterRightToken, this.parameterPrefix, this.wildCharToken, this.wildSingleCharToken));
}
protected override System.Data.Common.DbCommand CreateSelectRangeCommandForUnsortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn)
{
//page split algorithm using ROW_NUMBER() in SqlServer 2005
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("WITH [__T] AS (SELECT ");
if (topCount < int.MaxValue && (int.MaxValue - topCount > skipCount))
{
sb.Append("TOP ");
sb.Append(topCount + skipCount);
sb.Append(' ');
}
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(") 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 [__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);
}
protected virtual DbCommand CreateSelectRangeCommandForUnsortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn)
{
//SELECT TOP 10 *
//FROM TestTable
//WHERE (ID NOT IN
// (SELECT TOP 20 id
// FROM TestTable
// ORDER BY id))
//ORDER BY ID
DbCommand cmd = fac.CreateCommand();
cmd.CommandType = CommandType.Text;
StringBuilder sb = new StringBuilder("SELECT ");
if (topCount < int.MaxValue)
{
sb.Append("TOP ");
sb.Append(topCount);
sb.Append(' ');
}
for (int i = 0; i < columns.Length; ++i)
{
SqlQueryUtils.AppendColumnName(sb, columns[i]);
if (i < columns.Length - 1)
{
sb.Append(',');
}
}
sb.Append(" FROM ");
WhereClip cloneWhere = (WhereClip) where.Clone();
#region Construct & extend CloneWhere
StringBuilder sbInside = new StringBuilder();
sbInside.Append(identyColumn);
sbInside.Append(" NOT IN (SELECT TOP ");
sbInside.Append(skipCount);
sbInside.Append(' ');
sbInside.Append(identyColumn);
sbInside.Append(" FROM ");
sbInside.Append(where.ToString());
sbInside.Append(")");
if (cloneWhere.Sql.Length == 0)
{
cloneWhere.Sql = sbInside.ToString();
}
else
{
cloneWhere.Sql = "(" + cloneWhere.Sql.ToString() + ") AND " + sbInside.ToString();
}
#endregion
sb.Append(cloneWhere.ToString());
AddExpressionParameters(where, cmd);
AddExpressionParameters(cloneWhere, 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 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);
}
}