C#將SQL語句轉換為分頁SQL和擷取記錄數SQL

   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;
        }

歡迎訪問：http://121.18.78.216 適易查詢分析、工作流程、內容管理及專案管理示範平台