/// <summary> /// 获取选择语句内容 /// </summary> /// <param name="baseCommand">源选择语句</param> /// <param name="orderReverse">是否反转排序</param> /// <returns>选择语句内容</returns> internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse) { SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database); sb.AppendSelectPrefix(); sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList()); if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式 { /* * SELECT * FROM * ( * SELECT *, ROWNUM RN * FROM (SELECT * FROM TABLE_NAME) * WHERE ROWNUM <= 30 * ) * WHERE RN > 20 */ SelectCommand innestCommand = baseCommand.Database.InternalCreateSelectCommand(baseCommand, baseCommand.TableName); innestCommand.InternalSetQueryFieldList(baseCommand); innestCommand.InternalSetJoinList(baseCommand); innestCommand.InternalSetWhereCondition(baseCommand); innestCommand.InternalSetGroupByFieldList(baseCommand); innestCommand.InternalSetHavingCondition(baseCommand); innestCommand.InternalSetOrderList(baseCommand); SelectCommand innerCommand = baseCommand.Database.InternalCreateSelectCommand(baseCommand, innestCommand, ""); innerCommand.InternalSetWhereCondition(innerCommand.ConditionBuilder.LessThanOrEqualColumn("ROWNUM", (baseCommand.RecordStart + baseCommand.PageSize).ToString())); innerCommand.InternalQuerys(baseCommand.QueryFields); innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROWNUM", "RN")); sb.AppendSelectFrom(innerCommand.GetCommandText(), true); sb.AppendWhere(baseCommand.ConditionBuilder.GreaterThanColumn("RN", baseCommand.RecordStart.ToString())); } else//正常模式 { sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.InternalGetJoinList()); ISqlCondition where = baseCommand.WhereCondition; if (baseCommand.PageSize > 0) { where = baseCommand.ConditionBuilder.And(where, baseCommand.ConditionBuilder.LessThanOrEqualColumn("ROWNUM", baseCommand.PageSize.ToString())); } sb.AppendWhere(where); sb.AppendSelectGroupBys(baseCommand.InternalGetGroupByFieldList()); sb.AppendHaving(baseCommand.InternalGetHavingCondition()); sb.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), orderReverse); } return(sb.ToString()); }
/// <summary> /// 获取选择语句内容 /// </summary> /// <param name="baseCommand">源选择语句</param> /// <param name="orderReverse">是否反转排序</param> /// <returns>选择语句内容</returns> internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse) { SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database); sb.AppendSelectPrefix(); sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields); if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式 { /* SELECT * FROM ( SELECT *, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) WHERE ROWNUM <= 30 ) WHERE RN > 20 */ SelectCommand innestCommand = new SelectCommand(baseCommand.Database, baseCommand.TableName); innestCommand.QueryFields = baseCommand.QueryFields; innestCommand.SqlJoins = baseCommand.SqlJoins; innestCommand.SqlWhere = baseCommand.SqlWhere; innestCommand.GroupByColumns = baseCommand.GroupByColumns; innestCommand.SqlHaving = baseCommand.SqlHaving; innestCommand.SqlOrders = baseCommand.SqlOrders; SelectCommand innerCommand = new SelectCommand(baseCommand.Database, innestCommand, ""); innerCommand.SqlWhere = SqlCondition.LessThanOrEqualColumn(innerCommand, "ROWNUM", (baseCommand.RecordStart + baseCommand.PageSize).ToString()); innerCommand.InternalQuerys(baseCommand.QueryFields.ToArray()); innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROWNUM", "RN")); sb.AppendSelectFrom(innerCommand.GetCommandText(), true); sb.AppendWhere(SqlCondition.GreaterThanColumn(baseCommand, "RN", baseCommand.RecordStart.ToString())); } else//正常模式 { sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins); ISqlCondition where = baseCommand.SqlWhere; if (baseCommand.PageSize > 0) { where = SqlCondition.And(baseCommand, where, SqlCondition.LessThanOrEqualColumn(baseCommand, "ROWNUM", baseCommand.PageSize.ToString())); } sb.AppendWhere(where); sb.AppendSelectGroupBys(baseCommand.GroupByColumns); sb.AppendHaving(baseCommand.SqlHaving); sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse); } return sb.ToString(); }
/// <summary> /// 获取选择语句内容 /// </summary> /// <param name="baseCommand">源选择语句</param> /// <param name="orderReverse">是否反转排序</param> /// <returns>选择语句内容</returns> internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse) { SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database); sb.AppendSelectPrefix(); if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式 { /* * SELECT * FROM * ( * SELECT TOP 30 *, ROW_NUMBER() OVER(ORDER BY ID ASC) AS RN * FROM TABLE_NAME * ) AS T * WHERE RN > 20 */ sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList()); SqlCommandBuilder innerBuilder = new SqlCommandBuilder(baseCommand.Database); innerBuilder.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), false); SelectCommand innerCommand = baseCommand.Database.InternalCreateSelectCommand(baseCommand, baseCommand.TableName); innerCommand.InternalQuerys(baseCommand.QueryFields); innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROW_NUMBER() OVER( " + innerBuilder.ToString() + ")", "RN")); innerCommand.PageSize = baseCommand.RecordStart + baseCommand.PageSize; innerCommand.InternalSetJoinList(baseCommand); innerCommand.InternalSetWhereCondition(baseCommand); innerCommand.InternalSetGroupByFieldList(baseCommand); innerCommand.InternalSetHavingCondition(baseCommand); sb.AppendSelectFrom(innerCommand.GetCommandText("T"), true); sb.AppendWhere(baseCommand.ConditionBuilder.GreaterThanColumn("RN", baseCommand.RecordStart.ToString())); } else//正常模式 { if (baseCommand.PageSize > 0) { sb.AppendSelectTop(baseCommand.PageSize); } sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.UseDistinct, baseCommand.InternalGetQueryFieldList()); sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.InternalGetJoinList()); sb.AppendWhere(baseCommand.WhereCondition); sb.AppendSelectGroupBys(baseCommand.InternalGetGroupByFieldList()); sb.AppendHaving(baseCommand.InternalGetHavingCondition()); sb.AppendSelectOrderBys(baseCommand.InternalGetOrderList(), orderReverse); } return(sb.ToString()); }
/// <summary> /// 获取选择语句内容 /// </summary> /// <param name="baseCommand">源选择语句</param> /// <param name="orderReverse">是否反转排序</param> /// <returns>选择语句内容</returns> internal static String InternalGetPagerCommand(SelectCommand baseCommand, Boolean orderReverse) { SqlCommandBuilder sb = new SqlCommandBuilder(baseCommand.Database); sb.AppendSelectPrefix(); if (baseCommand.PageSize > 0 && baseCommand.RecordStart > 0)//分页模式 { /* SELECT * FROM ( SELECT TOP 30 *, ROW_NUMBER() OVER(ORDER BY ID ASC) AS RN FROM TABLE_NAME ) AS T WHERE RN > 20 */ sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields); SqlCommandBuilder innerBuilder = new SqlCommandBuilder(baseCommand.Database); innerBuilder.AppendSelectOrderBys(baseCommand.SqlOrders, false); SelectCommand innerCommand = new SelectCommand(baseCommand.Database, baseCommand.TableName); innerCommand.InternalQuerys(baseCommand.QueryFields.ToArray()); innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction(baseCommand, "ROW_NUMBER() OVER( " + innerBuilder.ToString() + ")", "RN")); innerCommand.PageSize = baseCommand.RecordStart + baseCommand.PageSize; innerCommand.SqlJoins = baseCommand.SqlJoins; innerCommand.SqlWhere = baseCommand.SqlWhere; innerCommand.GroupByColumns = baseCommand.GroupByColumns; innerCommand.SqlHaving = baseCommand.SqlHaving; sb.AppendSelectFrom(innerCommand.GetCommandText("T"), true); sb.AppendWhere(SqlCondition.GreaterThanColumn(baseCommand, "RN", baseCommand.RecordStart.ToString())); } else//正常模式 { if (baseCommand.PageSize > 0) { sb.AppendSelectTop(baseCommand.PageSize); } sb.AppendSelectDistinct(baseCommand.UseDistinct).AppendAllColumnNames(baseCommand.QueryFields); sb.AppendSelectFromAndJoins(baseCommand.TableName, baseCommand.IsFromSql, baseCommand.SqlJoins); sb.AppendWhere(baseCommand.SqlWhere); sb.AppendSelectGroupBys(baseCommand.GroupByColumns); sb.AppendHaving(baseCommand.SqlHaving); sb.AppendSelectOrderBys(baseCommand.SqlOrders, orderReverse); } return sb.ToString(); }
/// <summary> /// 获取选择语句内容 /// </summary> /// <param name="cmd">选择语句类</param> /// <param name="realPageIndex">实际页索引</param> /// <param name="realPageCount">实际页数量</param> /// <param name="orderReverse">是否反转排序</param> /// <returns>选择语句内容</returns> internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse) { SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType); sb.AppendSelectPrefix(); sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields); if (cmd.PageSize == 0)//正常模式 { sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins); sb.AppendWhere(cmd.SqlWhere); sb.AppendSelectGroupBys(cmd.GroupByColumns); sb.AppendHaving(cmd.SqlHaving); sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse); } else//分页模式 { /* * SELECT * FROM * ( * SELECT A.*, ROWNUM RN * FROM (SELECT * FROM TABLE_NAME) A * WHERE ROWNUM <= 40 * ) * WHERE RN >= 21 */ SelectCommand innestCommand = new SelectCommand(cmd.Database, cmd.TableName); innestCommand.QueryFields = cmd.QueryFields; innestCommand.SqlJoins = cmd.SqlJoins; innestCommand.SqlWhere = cmd.SqlWhere; innestCommand.GroupByColumns = cmd.GroupByColumns; innestCommand.SqlHaving = cmd.SqlHaving; innestCommand.SqlOrders = cmd.SqlOrders; SelectCommand innerCommand = new SelectCommand(cmd.Database, innestCommand, "T"); innerCommand.SqlWhere = SqlCondition.LessThanOrEqual("ROWNUM", realPageIndex * cmd.PageSize); innerCommand.InternalQuerys(cmd.QueryFields.ToArray()); innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction("ROWNUM", "RN")); sb.AppendSelectFrom(innerCommand.ToString("T"), true); sb.AppendWhere(SqlCondition.GreaterThanOrEqual("RN", (realPageIndex - 1) * cmd.PageSize + 1)); } return(sb.ToString()); }
/// <summary> /// 获取选择语句内容 /// </summary> /// <param name="cmd">选择语句类</param> /// <param name="realPageIndex">实际页索引</param> /// <param name="realPageCount">实际页数量</param> /// <param name="orderReverse">是否反转排序</param> /// <returns>选择语句内容</returns> internal static String InternalGetSelectCommand(SelectCommand cmd, Int32 realPageIndex, Int32 realPageCount, Boolean orderReverse) { SqlCommandBuilder sb = new SqlCommandBuilder(cmd.DatabaseType); sb.AppendSelectPrefix(); sb.AppendSelectDistinct(cmd.UseDistinct).AppendAllColumnNames(cmd.QueryFields); if (cmd.PageSize == 0)//正常模式 { sb.AppendSelectFromAndJoins(cmd.TableName, cmd.IsFromSql, cmd.SqlJoins); sb.AppendWhere(cmd.SqlWhere); sb.AppendSelectGroupBys(cmd.GroupByColumns); sb.AppendHaving(cmd.SqlHaving); sb.AppendSelectOrderBys(cmd.SqlOrders, orderReverse); } else//分页模式 { /* SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM <= 40 ) WHERE RN >= 21 */ SelectCommand innestCommand = new SelectCommand(cmd.Database, cmd.TableName); innestCommand.QueryFields = cmd.QueryFields; innestCommand.SqlJoins = cmd.SqlJoins; innestCommand.SqlWhere = cmd.SqlWhere; innestCommand.GroupByColumns = cmd.GroupByColumns; innestCommand.SqlHaving = cmd.SqlHaving; innestCommand.SqlOrders = cmd.SqlOrders; SelectCommand innerCommand = new SelectCommand(cmd.Database, innestCommand, "T"); innerCommand.SqlWhere = SqlCondition.LessThanOrEqual("ROWNUM", realPageIndex * cmd.PageSize); innerCommand.InternalQuerys(cmd.QueryFields.ToArray()); innerCommand.InternalQuerys(SqlQueryField.InternalCreateFromFunction("ROWNUM", "RN")); sb.AppendSelectFrom(innerCommand.ToString("T"), true); sb.AppendWhere(SqlCondition.GreaterThanOrEqual("RN", (realPageIndex - 1) * cmd.PageSize + 1)); } return sb.ToString(); }