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