C#将SQL语句转换为分页SQL和获取记录数SQL

原创

义jsyhello 2022-11-18 19:03:48 博主文章分类：技术 ©著作权

文章标签 sql c# string exception oracle 文章分类 后端开发

©著作权归作者所有：来自51CTO博客作者义jsyhello的原创作品，请联系作者获取转载授权，否则将追究法律责任

C#将SQL语句转换为分页SQL和获取记录数SQL

        /// <summary>
        /// 将语句转化为分页SQL语句，两条包含分页的获取数据SQL；获取记录数SQL 贾世义
        /// </summary>
        /// <param name="dbtype">支持数据类型（Sql2005、Oracle、Db2、Infomix）</param>
        /// <param name="sql">SQL语句（Sql2005、Oracle、Db2必须包含order by，正常的一条SQL语句）</param>
        /// <param name="pageIndex">开始（从0开始）</param>
        /// <param name="pageSize">分页大小</param>
        /// <returns>两条语句，包含分页的获取数据SQL；获取记录数SQL</returns>
        public static string GetPageSql(DBType dbtype, string sql, int pageIndex, int pageSize)
        {
            if (String.IsNullOrEmpty(sql))
            {
                return null;
            }
            //不支持分页SQL
            if (dbtype == DBType.Sql2000 || dbtype == DBType.OleDb || dbtype == DBType.Odbc)
            {
                return sql;
            }
            string start = "0";
            if (pageIndex > 0)
            {
                start = Convert.ToString(pageIndex * pageSize);
            }
            int select = GetStartWith(sql, "select");
            if (dbtype == DBType.OdbcInformix)
            {
                return "select skip " + start + " first " + pageSize.ToString()
                       + " " + sql.Substring(select);
            }
            //拆分SQL
            string end = Convert.ToString((pageIndex + 1) * pageSize);
            string fromSql = GetFromSql(sql, "from");
            //select部分含select
            string selectSql = "select " + sql.Substring(select, sql.Length - select - fromSql.Length);
            string orderSql = GetFromSql(sql, "order by");
            if (!String.IsNullOrEmpty(orderSql))
            {
                //将from后的order by去除
                fromSql = fromSql.Substring(0, fromSql.Length - orderSql.Length);
            }
            else
            {
                throw new Exception(dbtype + "使用分页必须包含order by");
            }
            //合并成分页SQL
            string strSql = "select * from (" + selectSql;
            //order部分含 order by
            string rownum;
            if (dbtype == DBType.OdbcDb2)
            {
                rownum = "rownumber()";
            }
            else
            {
                rownum = "row_number()";
            }
            strSql += "," + rownum + " over (" + orderSql + ") as rn " + fromSql
                   + ") as data where rn>" + start + " and rn<=" + end;
            if (!String.IsNullOrEmpty(GetFromSql(fromSql, "group by")))
            {
                fromSql = " from (select count(*) as amount " + fromSql + ") tbl";
            }
            return strSql + ";select count(*) as [" + Constants.MYQUERY_AMOUNT + "] " + fromSql;
        }