[C#]利用『TOP NOTIN』『TOP MAX』『OVER ROW_NUMBER』分頁代碼封裝

分頁是經久不衰的話題，在項目中也經常用到，這次特意封裝下，方便后續使用，順便整理知識點。而譬如三者效率以及其他分頁方法，還待后續研究。

關鍵代碼：

        /// <summary>        /// 利用[ROW_NUMBER() over]分頁，生成sql語句        /// </summary>        /// <param name="tableName">表名稱『eg:Orders』</param>        /// <param name="columns">需要顯示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>        /// <param name="orderColumn">依據排序的列『eg:OrderID』</param>        /// <param name="orderType">升序降序『1：desc;其他:asc』</param>        /// <param name="pSize">每頁頁數『需大于零』</param>        /// <param name="pIndex">頁數『從壹開始算』</param>        /// <returns>生成分頁sql腳本</returns>        public static string PageDBWithRowNumberString(string tableName, string columns, string orderColumn, int orderType, int pSize, int pIndex)        {            CheckPageDbParameter(tableName, columns, orderColumn, pSize, pIndex);            int _pageStart = pSize * (pIndex - 1) + 1;            int _pageEnd = pSize * pIndex + 1;            string _sql = string.Format("select * from  (select (ROW_NUMBER() over(order by {2} {3})) as ROWNUMBER,{1}  from {0})as tp where ROWNUMBER >= {4} and ROWNUMBER< {5} ",                                         tableName,                                         columns,                                         orderColumn,                                         orderType == 1 ? "desc" : "asc",                                         _pageStart,                                         _pageEnd);            return _sql;        }        /// <summary>        /// 利用[Top NotIn]分頁，生成sql語句        /// </summary>        /// <param name="tableName">表名稱『eg:Orders』</param>        /// <param name="columns">需要顯示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>        /// <param name="orderColumn">依據排序的列『eg:OrderID』</param>        /// <param name="orderType">升序降序『1：desc;其他:asc』</param>        /// <param name="pSize">每頁頁數『需大于零』</param>        /// <param name="pIndex">頁數『從壹開始算』</param>        /// <returns>生成分頁sql腳本</returns>        public static string PageDBWithTopNotInString(string tableName, string columns, string orderColumn, int orderType, int pSize, int pIndex)        {            CheckPageDbParameter(tableName, columns, orderColumn, pSize, pIndex);            /*             *eg:             *1=>SELECT orderID FROM Orders ORDER BY orderID             *2=>SELECT TOP 20 orderID FROM Orders ORDER BY orderID //查詢前一頁數據              *3=> SELECT TOP 10 * FROM Orders WHERE (orderID NOT IN (SELECT TOP 20 orderID FROM Orders ORDER BY orderID)) ORDER BY orderID //在所有數據中，截去掉上一頁數據(not in)，然后select top 10 即當前頁數據             */            string _sql = string.Format("SELECT TOP {4} {1} FROM {0} WHERE ({2} NOT IN (SELECT TOP {5} {2} FROM {0} ORDER BY {2} {3})) ORDER BY {2} {3}",                                         tableName,                                         columns,                                         orderColumn,                                         orderType == 1 ? "desc" : "asc",                                         pSize,                                         (pIndex - 1) * pSize);            return _sql;        }        PRivate static void CheckPageDbParameter(string tableName, string columns, string orderColumn, int pSize, int pIndex)        {            if (string.IsNullOrEmpty(tableName))                throw new ArgumentNullException("tableName");            if (string.IsNullOrEmpty(columns))                throw new ArgumentNullException("columns");            if (string.IsNullOrEmpty(orderColumn))                throw new ArgumentNullException("orderColumn");            if (pSize <= 0)                throw new ArgumentException("pageSize");            if (pIndex <= 0)                throw new ArgumentNullException("pageIndex");        }        /// <summary>        /// 利用[Top Max]分頁，生成sql語句        /// </summary>        /// <param name="tableName">表名稱『eg:Orders』</param>        /// <param name="columns">需要顯示列『*:所有列；或者：eg:OrderID,OrderDate,ShipName,ShipCountry』</param>        /// <param name="orderColumn">依據排序的列『eg:OrderID』</param>        /// <param name="orderType">升序降序『1：desc;其他:asc』</param>        /// <param name="pSize">每頁頁數『需大于零』</param>        /// <param name="pIndex">頁數『從壹開始算』</param>        /// <returns>生成分頁sql腳本</returns>        public static string PageDBWithTopMaxString(string tableName, string columns, string orderColumn, int orderType, int pSize, int pIndex)        {            CheckPageDbParameter(tableName, columns, orderColumn, pSize, pIndex);            /*             *eg:             *1=>select top 30 orderID from Orders order by orderID asc             *2=>(select max (orderID) from (select top 30 orderID from Orders order by orderID asc) as T) //查詢前一頁數據              *3=> select top 15 OrderID,OrderDate,ShipName,ShipCountry from Orders where orderID>                   ISNULL((select max (orderID) from (select top 30 orderID from Orders order by orderID asc) as T),0)                   order by orderID asc             */            string _sql = string.Format("select top {4} {1} from {0} where {2}> ISNULL((select max ({2}) from (select top {5} {2} from {0} order by {2} {3}) as T),0) order by {2} {3}",                                         tableName,                                         columns,                                         orderColumn,                                         orderType == 1 ? "desc" : "asc",                                         pSize,                                         (pIndex - 1) * pSize);            return _sql;        }        /// <summary>        /// 獲取分頁總頁數        /// </summary>        /// <param name="recordCnt">總記錄條數</param>        /// <param name="pageSize">每頁頁數</param>        /// <returns>分頁總頁數</returns>        public static int GetPageTotalCount(int recordCnt, int pageSize)        {            if (recordCnt < 0)                throw new ArgumentException("recordCnt");            if (pageSize < 0)                throw new ArgumentException("pageSize");            int _pageTotalCount = recordCnt / pageSize;            if (recordCnt % pageSize != 0)                _pageTotalCount++;            return _pageTotalCount;        }

使用例子：

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Text;using System.Windows.Forms;using DBUtilHelpV2;namespace WindowsFormsapplication5{    public partial class Form1 : Form    {        public Form1()        {            InitializeComponent();        }        MSSQLToolV2 SQLHelper = new MSSQLToolV2(msConnectionString);        static string msConnectionString = DBToolV2.BuilderMSSqlConnectString(@"YANZHIWEI-PC/SQLEXPRESS"