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; }