protected virtual DbCommand CreateSelectRangeCommandForSortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool isIdentyColumnDesc)
{
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;
}
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);
}
protected virtual DbCommand CreateSelectRangeCommandForSortedRows(WhereClip where, string[] columns, int topCount, int skipCount, string identyColumn, bool isIdentyColumnDesc)
{
//SELECT TOP 10 *
//FROM TestTable
//WHERE (ID >
// (SELECT MAX(id)/MIN(id)
// FROM (SELECT TOP 20 id
// FROM TestTable
// ORDER BY id) AS T))
//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(isIdentyColumnDesc ? '<' : '>');
sbInside.Append('(');
sbInside.Append("SELECT ");
sbInside.Append(isIdentyColumnDesc ? "MIN(" : "MAX(");
sbInside.Append("[__T].");
string[] splittedIdentyColumn = identyColumn.Split('.');
sbInside.Append(splittedIdentyColumn[splittedIdentyColumn.Length - 1]);
sbInside.Append(") FROM (SELECT TOP ");
sbInside.Append(skipCount);
sbInside.Append(' ');
sbInside.Append(identyColumn);
sbInside.Append(" AS ");
sbInside.Append(splittedIdentyColumn[splittedIdentyColumn.Length - 1]);
sbInside.Append(" FROM ");
sbInside.Append(where.ToString());
sbInside.Append(") [__T])");
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;
}
