Example #1
0
        /// <summary>构造分页SQL</summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="startRowIndex">开始行,0表示第一行</param>
        /// <param name="maximumRows">最大返回行数,0表示所有行</param>
        /// <param name="keyColumn">唯一键。用于not in分页</param>
        /// <returns>分页SQL</returns>
        public override String PageSplit(String sql, Int32 startRowIndex, Int32 maximumRows, String keyColumn)
        {
            // 从第一行开始,不需要分页
            if (startRowIndex <= 0 && maximumRows < 1)
            {
                return(sql);
            }

            if (startRowIndex > 0)
            {
                // 指定了起始行,并且是SQL2005及以上版本,使用MS SQL 2012特有的分页算法
                if (IsSQL2012)
                {
                    // var str = $"select * from spt_values where type = 'p' order by {keyColumn} offset {startRowIndex} rows fetch next {maximumRows} rows only";
                    //SelectBuilder builder = new SelectBuilder();
                    //builder.Parse(sql);
                    //return MSPageSplit.PageSplit_Sql2012(builder, startRowIndex, maximumRows);
                    // 从第一行开始,不需要分页
                    if (startRowIndex <= 0)
                    {
                        if (maximumRows < 1)
                        {
                            return(sql);
                        }
                        else
                        {
                            var sql_ = FormatSqlserver2012SQL(sql);
                            return($"{sql_} offset 1 rows fetch next {maximumRows} rows only ");
                        }
                    }
                    if (maximumRows < 1)
                    {
                        throw new NotSupportedException("不支持取第几条数据之后的所有数据!");
                    }

                    var sql__ = FormatSqlserver2012SQL(sql);
                    return($"{sql__} offset {startRowIndex} rows fetch next {maximumRows} rows only ");
                }

                // 指定了起始行,并且是SQL2005及以上版本,使用RowNumber算法
                if (IsSQL2005)
                {
                    //return PageSplitRowNumber(sql, startRowIndex, maximumRows, keyColumn);
                    SelectBuilder builder = new SelectBuilder();
                    builder.Parse(sql);
                    return(MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, IsSQL2005).ToString());
                }
            }


            // 如果没有Order By,直接调用基类方法
            // 先用字符串判断,命中率高,这样可以提高处理效率
            if (!sql.Contains(" Order "))
            {
                if (!sql.ToLower().Contains(" order "))
                {
                    return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
                }
            }
            //// 使用正则进行严格判断。必须包含Order By,并且它右边没有右括号),表明有order by,且不是子查询的,才需要特殊处理
            //MatchCollection ms = Regex.Matches(sql, @"\border\s*by\b([^)]+)$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            //if (ms == null || ms.Count < 1 || ms[0].Index < 1)
            String sql2    = sql;
            String orderBy = CheckOrderClause(ref sql2);

            if (String.IsNullOrEmpty(orderBy))
            {
                return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
            }
            // 已确定该sql最外层含有order by,再检查最外层是否有top。因为没有top的order by是不允许作为子查询的
            if (Regex.IsMatch(sql, @"^[^(]+\btop\b", RegexOptions.Compiled | RegexOptions.IgnoreCase))
            {
                return(base.PageSplit(sql, startRowIndex, maximumRows, keyColumn));
            }
            //String orderBy = sql.Substring(ms[0].Index);

            // 从第一行开始,不需要分页
            if (startRowIndex <= 0)
            {
                if (maximumRows < 1)
                {
                    return(sql);
                }
                else
                {
                    return(String.Format("Select Top {0} * From {1} {2}", maximumRows, CheckSimpleSQL(sql2), orderBy));
                }
                //return String.Format("Select Top {0} * From {1} {2}", maximumRows, CheckSimpleSQL(sql.Substring(0, ms[0].Index)), orderBy);
            }

            #region Max/Min分页
            // 如果要使用max/min分页法,首先keyColumn必须有asc或者desc
            String kc = keyColumn.ToLower();
            if (kc.EndsWith(" desc") || kc.EndsWith(" asc") || kc.EndsWith(" unknown"))
            {
                String str = PageSplitMaxMin(sql, startRowIndex, maximumRows, keyColumn);
                if (!String.IsNullOrEmpty(str))
                {
                    return(str);
                }
                keyColumn = keyColumn.Substring(0, keyColumn.IndexOf(" "));
            }
            #endregion

            sql = CheckSimpleSQL(sql2);

            if (String.IsNullOrEmpty(keyColumn))
            {
                throw new ArgumentNullException("keyColumn", "分页要求指定主键列或者排序字段!");
            }

            if (maximumRows < 1)
            {
                sql = String.Format("Select * From {1} Where {2} Not In(Select Top {0} {2} From {1} {3}) {3}", startRowIndex, sql, keyColumn, orderBy);
            }
            else
            {
                sql = String.Format("Select Top {0} * From {1} Where {2} Not In(Select Top {3} {2} From {1} {4}) {4}", maximumRows, sql, keyColumn, startRowIndex, orderBy);
            }
            return(sql);
        }
Example #2
0
 public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
 {
     return(MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, false, b => CreateSession().QueryCount(b)));
 }
Example #3
0
 public override SelectBuilder PageSplit(SelectBuilder builder, int startRowIndex, int maximumRows)
 {
     return(MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, IsSQL2005, b => CreateSession().QueryCount(b)));
 }