[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"