Exemple #1
0
        /// <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();
        }
Exemple #3
0
        /// <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();
        }