仿orm自动生成分页SQL分享(2)-木庄网络博客

当前第2页返回上一页

            if (dbType == DBType.SqlServer2000)
            {
                pageSql.AppendFormat("SELECT TOP @PageLimit * FROM ( SELECT TOP @PageEnd {0} {1} ) ", sqls[2], sqlOrderChange);
            }
            else if (dbType == DBType.SqlServer)
            {
                //组织分页SQL语句
                pageSql.AppendFormat("SELECT PageTab.* FROM ( SELECT TOP @PageEnd ROW_NUMBER() over ({0}) RN , {1} ) PageTab ",
                    sqlOrderChange,
                    sqls[2]);

                //如果查询不是第一页，则需要判断起始行号
                if (start > 1)
                {
                    pageSql.Append("Where RN >= :PageStart ");
                }
            }
            else if (dbType == DBType.Oracle)
            {
                pageSql.AppendFormat("SELECT ROWNUM RN, PageTab.* FROM ( Select {0} {1} ) PageTab where ROWNUM <= :PageEnd ", sqls[2], sqlOrderChange);

                //如果查询不是第一页，则需要判断起始行号
                if (start > 1)
                {
                    pageSql.Insert(0, "SELECT * FROM ( ");
                    pageSql.Append(" ) ");
                    pageSql.Append(" WHERE RN>= :PageStart ");
                }
            }
            else if (dbType == DBType.SQLite)
            {
                pageSql.AppendFormat("SELECT * FROM ( SELECT {0} {1} limit @PageStart,@PageLimit ) PageTab ", sqls[2], sqlOrderChange);
            }

//恢复排序
pageSql.Append(sqlOrder);

//存储生成的分页SQL语句
sqls[1] = pageSql.ToString();

//临时测试
sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");

            Console.WriteLine("【count】{0}", sqls[0]);
            Console.WriteLine("【page】{0}", sqls[1]);
            Console.WriteLine();
        }

        /// <summary>
        /// 生成常规Sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqls"></param>
        /// <param name="start"></param>
        /// <param name="limit"></param>
        /// <param name="createCount"></param>
        public void CreatePageSql(string sql, out string[] sqls, int start, int limit, bool createCount = false)
        {
            //需要输出的sql数组
            sqls = null;

            //生成count的SQL语句 SqlServer生成分页，必须通过正则拆分
            if (createCount || dbType == DBType.SqlServer || dbType == DBType.SqlServer2000)
            {
                sqls = SplitSqlForPaging(sql);
                if (sqls == null)
                {
                    //无法解析的SQL语句
                    throw new Exception("can't parse sql to pagesql ,the sql is " + sql);
                }
            }
            else
            {
                sqls = new string[2];
            }

//组织分页SQL语句
var pageSql = new StringBuilder();

            var end = start + limit;
            if (dbType == DBType.SqlServer2000)
            {
                pageSql.AppendFormat("SELECT TOP @PageEnd {0} {1}", sqls[2], sqls[3]);

                if (start > 1)
                {
                    var orderChange = string.IsNullOrEmpty(sqls[5]) ? null :
                        string.Compare(sqls[5], "desc", true) == 0 ?
                        string.Format("{0} ASC ", sqls[4]) :
                        string.Format("{0} DESC ", sqls[4]);
                    pageSql.Insert(0, "SELECT TOP 100 PERCENT * FROM (SELECT TOP @PageLimit * FROM ( ");
                    pageSql.AppendFormat(" ) PageTab {0} ) PageTab2 {1}", orderChange, sqls[3]);
                }
            }
            else if (dbType == DBType.SqlServer)
            {
                pageSql.AppendFormat(" Select top @PageEnd ROW_NUMBER() over ({0}) RN , {1}",
                    string.IsNullOrEmpty(sqls[3]) ? "ORDER BY (SELECT NULL)" : sqls[3],
                    sqls[2]);

                //如果查询不是第一页，则需要判断起始行号
                if (start > 1)
                {
                    pageSql.Insert(0, "Select PageTab.* from ( ");
                    pageSql.Append(" ) PageTab Where RN >= @PageStart");
                }
            }
            else if (dbType == DBType.Oracle)
            {
                pageSql.Append("select ROWNUM RN, PageTab.* from ");
                pageSql.AppendFormat(" ( {0} ) PageTab ", sql);
                pageSql.Append(" where ROWNUM <= :PageEnd ");

                //如果查询不是第一页，则需要判断起始行号
                if (start > 1)
                {
                    pageSql.Insert(0, "select * from ( ");
                    pageSql.Append(" ) Where RN>= :PageStart ");
                }
            }
            else if (dbType == DBType.SQLite)
            {
                pageSql.AppendFormat("{0} limit @PageStart,@PageLimit", sql, start, limit);
            }

//存储生成的分页SQL语句
sqls[1] = pageSql.ToString();

//临时测试
sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", ++start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + "");

            Console.WriteLine("【count】{0}", sqls[0]);
            Console.WriteLine("【page】{0}", sqls[1]);
            Console.WriteLine();
        }
    }

1.交换2个整数用了这样的算法。交换a和b，a=a+b;b=a-b;b=a-b;这是原来找工作的时候被考到的，如果在不使用第三方变量的情况下交换2个整数。

2.Sql2000下由于是使用top进行分页，除非条件一条数据都查不到，否则在分页start和limit参数超过了总行数时，也会查询出数据。

3.拆分Sql语句，参考了PetaPoco的部分源代码。

4.我的应用场景则是在dbhelp类，某个方法传递sql，start，limit参数即可对sql查询出来的结果进行分页。其中start：查询结果的起始行号(不包括它)，limit：需要取出的行数。如 start:0,limit:15 则是取出前15条数据。

更多SQL内容来自木庄网络博客

标签：SQL

返回前面的内容