public static IList FindPage( ObjectInfo state, String queryString )
        {
            // see: wojilu/wojilu/Web/Mvc/Routes/RouteTool.cs, line 211

            if (state.Pager.getCurrent() <= 0) {
                int page = CurrentRequest.getCurrentPage();
                state.Pager.setCurrent( page );
            }

            if ( queryString != null && queryString.ToLower().StartsWith( "order " )) {
                queryString = " " + queryString;
            }

            PageCondition pc = new PageCondition();
            pc.ConditionStr = queryString;
            pc.Property = state.Includer.SelectedProperty;
            pc.CurrentPage = state.Pager.getCurrent();
            pc.Size = state.Pager.getSize();
            pc.OrderStr = state.Order;
            pc.Pager = state.Pager;

            String sql = new SqlBuilder( state.EntityInfo ).GetPageSql( pc );
            return EntityPropertyUtil.FindList( state, sql );
        }
Beispiel #2
0
        private void getPageSql(PageCondition pc, String columnString, String joinTable, Boolean isJoin, Boolean isCustomerOrder, String orderString, String _condition_nowhere, StringBuilder sb)
        {
            if (_entityInfo.DbType == DatabaseType.MySql)
            {

                int startRow = pc.Size * (pc.CurrentPage - 1);

                sb.Append("select ");
                sb.Append(columnString);
                sb.Append(" from ");
                sb.Append(joinTable);
                sb.Append(" ");
                sb.Append(pc.ConditionStr);
                sb.Append(" ");
                sb.Append(orderString);
                sb.Append(" limit ");
                sb.Append(startRow);
                sb.Append(", ");
                sb.Append(pc.Size);

                return;
            }

            if (isCustomerOrder == false)
            {

                sb.Append("select top ");
                sb.Append(pc.Size);
                sb.Append(" ");
                sb.Append(columnString);
                sb.Append(" from ");
                sb.Append(joinTable);

                if (pc.OrderStr == "asc")
                {
                    if (isJoin)
                        sb.Append(" where t0.Id>(select max(Id) from (select top ");
                    else
                        sb.Append(" where Id>(select max(Id) from (select top ");
                }
                else
                {
                    if (isJoin)
                        sb.Append(" where t0.Id<(select min(Id) from (select top ");
                    else
                        sb.Append(" where Id<(select min(Id) from (select top ");
                }

                sb.Append(pc.Size * (pc.CurrentPage - 1));

                if (isJoin)
                    sb.Append(" t0.Id from ");
                else
                    sb.Append(" Id from ");

                sb.Append(joinTable);
                sb.Append(pc.ConditionStr);
                sb.Append(orderString);
                sb.Append(") as t) ");
                //sb.Append( "and " );
                sb.Append(_condition_nowhere);
                sb.Append(orderString);

            }

            else if (_entityInfo.DbType == DatabaseType.Access || _entityInfo.DbType == DatabaseType.SqlServer2000)
            {

                sb.Append("select top ");
                sb.Append(pc.Size);
                sb.Append(" ");
                sb.Append(columnString);
                sb.Append(" from ");
                sb.Append(joinTable);

                if (strUtil.IsNullOrEmpty(pc.ConditionStr))
                {
                    if (isJoin)
                        sb.Append(" where  t0.Id not in ( select top ");
                    else
                        sb.Append(" where  Id not in ( select top ");

                }
                else
                {

                    sb.Append(pc.ConditionStr);

                    if (isJoin)
                        sb.Append(" and  t0.Id not in ( select top ");
                    else
                        sb.Append(" and  Id not in ( select top ");

                }

                sb.Append(pc.Size * (pc.CurrentPage - 1));

                if (isJoin)
                    sb.Append(" t0.Id from ");
                else
                    sb.Append(" Id from ");

                sb.Append(joinTable);
                sb.Append(pc.ConditionStr);
                sb.Append(orderString);
                sb.Append(" ) ");
                //sb.Append( "and " );
                sb.Append(_condition_nowhere);
                sb.Append(" ");
                sb.Append(orderString);

            }

            else
            {

                int startRow = pc.Size * (pc.CurrentPage - 1);
                int endRow = startRow + pc.Size;

                sb.AppendFormat("select {0} from ", columnString);
                sb.Append("(");
                sb.AppendFormat("select ROW_NUMBER() over ( {0} ) as rowNumber, {1} from {2} {3}", orderString, columnString, joinTable, pc.ConditionStr);
                sb.Append(") as tmpTable ");
                sb.AppendFormat("where (rowNumber between {0} and {1}) {2}", (startRow + 1), endRow, _condition_nowhere);

            }
        }
Beispiel #3
0
        public String GetPageSql(PageCondition pc)
        {
            String columnString = getColumnsByProperty(pc.Property);

            //------------------------ condition ------------------------------
            String joinTable = "";
            pc.ConditionStr = processCondition(pc.ConditionStr, new Dictionary<String, Object>(), ref joinTable);

            Boolean isJoin = false;
            if (strUtil.HasText(joinTable))
            {
                isJoin = true;
                joinTable = _entityInfo.TableName + " t0, " + joinTable;

                // columnString中的每个columnName都要加上t0前缀
                columnString = addJoinTablePrefix(columnString, ',');
            }
            else
            {
                joinTable = _entityInfo.TableName;
            }
            joinTable = joinTable.Trim().TrimEnd(',');

            if (strUtil.HasText(pc.ConditionStr))
            {
                if (pc.ConditionStr.ToLower().Trim().StartsWith("and "))
                {

                    //"and id=7" 剔除and开头
                    pc.ConditionStr = " where " + pc.ConditionStr.Trim().Substring(3);
                }
                //"order by id" 加上一个空格
                else if (pc.ConditionStr.Trim().ToLower().StartsWith("order "))
                {
                    pc.ConditionStr = " " + pc.ConditionStr;
                }
                else
                {
                    //加上 where
                    pc.ConditionStr = " where " + pc.ConditionStr;
                }
            }

            //------------------------ order ------------------------------
            Boolean isCustomerOrder = false;
            if (pc.ConditionStr.ToLower().IndexOf(" order ") >= 0) isCustomerOrder = true;

            String orderString = "";
            pc.OrderStr = pc.OrderStr.ToLower();

            // 默认排序方式:逆序
            if (isCustomerOrder == false)
            {

                if (pc.OrderStr == "asc")
                    orderString = " order by Id asc";
                else
                    orderString = " order by Id desc";

                if (isJoin) orderString = orderString.Replace("Id", "t0.Id");

            }
            else
            {
                // 自定义排序方式
                String newOrderString = "";

                String tempString = pc.ConditionStr;
                //此处conditionString已经不带order
                pc.ConditionStr = pc.ConditionStr.Substring(0, pc.ConditionStr.ToLower().IndexOf("order "));

                orderString = tempString.Replace(pc.ConditionStr, "");

                String[] arrOrder = orderString.Split(new char[] { ' ', ',' });
                foreach (String s in arrOrder)
                {
                    if (s.ToLower() == "asc" || s.ToLower() == "desc")
                    {
                        newOrderString += s + ", ";
                    }
                    else if (s.ToLower() != "order" && s.ToLower() != "by" && s != null && s != "")
                    {
                        if (isJoin)
                            newOrderString += "t0." + s + " ";
                        else
                            newOrderString += s + " ";
                    }
                    else
                    {
                        newOrderString += s + " ";
                    }
                }

                orderString = newOrderString.Trim().TrimEnd(',');
            }

            String _condition_nowhere = pc.ConditionStr.Trim();
            if (strUtil.HasText(_condition_nowhere) && _condition_nowhere.ToLower().StartsWith("where"))
            {
                _condition_nowhere = pc.ConditionStr.Trim().Substring(5);
                _condition_nowhere = "and " + _condition_nowhere;
            }

            String sql = "";

            String pageCountSql = "select count(*) from " + joinTable + pc.ConditionStr;

            //callback执行beginCount方法
            pc.CurrentPage = pc.beginCount(pageCountSql, pc.Pager, _entityInfo);

            // TODO:最后一页优化

            if (pc.CurrentPage == 1)
            {
                sql = "select " + columnString + " from " + joinTable + pc.ConditionStr + orderString;

                sql = _entityInfo.Dialect.GetLimit(sql, pc.Size);
            }
            else
            {

                StringBuilder sb = new StringBuilder();
                getPageSql(pc, columnString, joinTable, isJoin, isCustomerOrder, orderString, _condition_nowhere, sb);

                sql = sb.ToString();

            }

            logger.Info(LoggerUtil.SqlPrefix + "[Page Sql] " + sql);
            return sql;
        }