我的最新分页

ALTER proc [dbo].[pageing]
(
    @tableName varchar(255),    --表名
    @showField varchar(1000),    --显示的字段
    @orderField varchar(255),    --排序的字段
    @pageSize int,                --页尺寸
    @pageIndex int,                --页码
    @orderType bit,                --排序类型,1是升序,0是降序
    @strWhere varchar(3000),    --查询条件
    @total int output           --返回总记录数
)
as
begin
    declare @strSql varchar(4000)        --主语句
    declare @strOrder varchar(200)        --排序
    declare @strSqlCount nvarchar(500)    --查询记录总数主语句
    if(@orderType != 0)
    begin
        set @strOrder=' order by '+@orderField+' asc'
    end
    else
    begin
        set @strOrder=' order by '+@orderField+' desc'
    end
    if(len(@strWhere) > 0)
    begin
        set @strSqlCount='select @totalCout=count(1) from '+@tableName+' where 1=1 '+@strWhere
    end
    else
    begin
        set @strSqlCount='select @totalCout=count(1) from '+@tableName
    end
    exec sp_executesql @strSqlCount,N'@totalCout int output',@total output
    if(@pageIndex <= 0)
    begin
        set @pageIndex=1
    end
    if(len(@strWhere) > 0)
    begin
        set @strSql='select * from (select top '+str(@total)+' row_number() over('+@strOrder+') rowId,'+@showField
            +' from '+@tableName+' where 1=1 '+@strWhere+@strOrder+') tb where tb.rowId>'+str((@pageIndex-1)*@pageSize)
            +' and tb.rowId<='+str(@pageIndex*@pageSize)
    end
    else
    begin
        set @strSql='select * from (select top '+str(@total)+' row_number() over('+@strOrder+') rowId,'+@showField
            +' from '+ @tableName+' where 1=1 '+@strWhere+@strOrder+') tb where tb.rowId>'+str((@pageIndex-1)*@pageSize)
            +' and tb.rowId<='+str(@pageIndex*@pageSize)
    end    
    print @strSql
    exec(@strSql)
end

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;

/// <summary>
///MyPageing 的摘要说明
/// </summary>
public class MyPageing : Page
{
    public MyPageing()
    {
        //
        //TODO: 在此处添加构造函数逻辑
        //
    }

    public static string GetPageNum(int total, int pagesize)
    {
        string[] canshu = HttpContext.Current.Request.QueryString.ToString().Split('&');
        StringBuilder sb = new StringBuilder();
        foreach (string cs in canshu)
        {
            if (cs.IndexOf("PageNo") > -1)
                continue;
            sb.Append("&" + cs);
        }
        int page;
        if (HttpContext.Current.Request.QueryString["PageNo"] != null)
            page = Convert.ToInt32(HttpContext.Current.Request.QueryString["PageNo"]);
        else
            page = 1;
        int allpage = 0;
        int next = 0;
        int pre = 0;
        int startcount = 0;
        int endcount = 0;

        StringBuilder pagestr = new StringBuilder();
        pagestr.Append("<style type=\"text/css\">");
        pagestr.Append("* { margin:0; padding:0;}");        
        pagestr.Append("a{ color:#333; text-decoration:none;}");
        pagestr.Append("ul{ list-style:none;}");
        pagestr.Append("#pagelist {padding:6px 0px; height:20px; float:right;}");
        pagestr.Append("#pagelist ul li { float:left; height:20px; line-height:20px; margin:0px 2px;}");
        pagestr.Append(".hrjaa{border:1px solid #d0d0d0; }");
        pagestr.Append(".hrjaa a{ display:block; padding:0px 6px; background:#f6f6f6;}");        
        pagestr.Append(".current { background:#fdf3f3; display:block; padding:0px 6px; font-weight:bold;border:1px solid #d00202; color:#d00202;}");
        pagestr.Append("</style>");

        if (page < 1) { page = 1; }
        //计算总页数
        if (pagesize != 0)
        {
            allpage = (total / pagesize);
            allpage = ((total % pagesize) != 0 ? allpage + 1 : allpage);
            allpage = (allpage == 0 ? 1 : allpage);
        }
        next = page + 1;
        pre = page - 1;
        startcount = (page + 5) > allpage ? allpage - 9 : page - 4;//中间页起始序号
        //中间页终止序号
        endcount = page < 5 ? 10 : page + 5;
        if (startcount < 1) { startcount = 1; } //为了避免输出的时候产生负数，设置如果小于1就从序号1开始
        if (allpage < endcount) { endcount = allpage; } //页码+5的可能性就会产生最终输出序号大于总页码，那么就要将其控制在页码数之内        
        //pagestr.Append("共<font color='red'>" + total + "</font>条记录&nbsp;&nbsp;&nbsp;&nbsp;每页<font color='red'>" + pagesize + "</font>条&nbsp;&nbsp;&nbsp;&nbsp;共<font color='red'>" + allpage + "</font>页");
        pagestr.Append("<div id=\"pagelist\">");        
        pagestr.Append("<ul>");

        if (page > 1)
            pagestr.Append("<li class=\"hrjaa\"><a href=\"" + HttpContext.Current.Request.CurrentExecutionFilePath + "?PageNo=" + pre + sb.ToString() + "\">上一页</a></li>");
        else
            pagestr.Append("<li>上一页</li>");
        //中间页处理，这个增加时间复杂度，减小空间复杂度
        for (int i = startcount; i <= endcount; i++)
        {
            if (page == i)
                pagestr.Append("<li class=\"current\">" + i + "</li>");
            else
                pagestr.Append("<li class=\"hrjaa\"><a href=\"" + HttpContext.Current.Request.CurrentExecutionFilePath + "?PageNo=" + i + sb.ToString() + "\">" + i + "</a></li>");
        }
        if (page != allpage)
            pagestr.Append("<li class=\"hrjaa\"><a href=\"" + HttpContext.Current.Request.CurrentExecutionFilePath + "?PageNo=" + next + sb.ToString() + "\">下一页</a></li>");
        else
            pagestr.Append(" <li>下一页</li>");
        pagestr.Append("</ul>");
        pagestr.Append("</div>");
        return pagestr.ToString();
    }
}

 

--第一页数据
select top 2 id,SerialNumber,AddTime from TB_Voucher 
--第n页数据(n>1) 2*1 2*2
 SELECT TOP 2 id,SerialNumber,AddTime from TB_Voucher where 
 (id > (SELECT MAX(id) FROM (SELECT TOP 6 id FROM TB_Voucher ORDER BY id) AS T)) ORDER BY ID 

/// <summary>
        /// 分页使用
        /// </summary>
        /// <param name="query"></param>
        /// <param name="passCount"></param>
        /// <returns></returns>
        private static string recordID(string query, int passCount)
        {
            using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString))
            {
                m_Conn.Open();
                OleDbCommand cmd = new OleDbCommand(query, m_Conn);
                string result = string.Empty;
                using (OleDbDataReader dr = cmd.ExecuteReader())
                {
                    while (dr.Read())
                    {
                        if (passCount < 1)
                        {
                            result += "," + dr.GetInt32(0);
                        }
                        passCount--;
                    }
                }
                m_Conn.Close();
                m_Conn.Dispose();
                return result.Substring(1);
            }
        }
        /// <summary>
        /// ACCESS高效分页
        /// </summary>
        /// <param name="pageIndex">当前页码</param>
        /// <param name="pageSize">分页容量</param>
        /// <param name="strKey">主键</param>
        /// <param name="showString">显示的字段</param>
        /// <param name="queryString">查询字符串，支持联合查询</param>
        /// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param>
        /// <param name="orderString">排序规则</param>
        /// <param name="pageCount">传出参数：总页数统计</param>
        /// <param name="recordCount">传出参数：总记录统计</param>
        /// <returns>装载记录的DataTable</returns>
        public static DataTable ExecutePager(int pageIndex, int pageSize, string strKey,string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount)
        {
            if (pageIndex < 1) pageIndex = 1;
            if (pageSize < 1) pageSize = 10;
            if (string.IsNullOrEmpty(showString)) showString = "*";
            if (string.IsNullOrEmpty(orderString)) orderString = strKey+" asc ";
            using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString))
            {
                m_Conn.Open();
                string myVw = string.Format(" ( {0} ) tempVw ", queryString);
                OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), m_Conn);

                recordCount = Convert.ToInt32(cmdCount.ExecuteScalar());

                if ((recordCount % pageSize) > 0)
                    pageCount = recordCount / pageSize + 1;
                else
                    pageCount = recordCount / pageSize;
                OleDbCommand cmdRecord;
                if (pageIndex == 1)//第一页
                {
                    cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), m_Conn);
                }
                else if (pageIndex > pageCount)//超出总页数
                {
                    cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn);
                }
                else
                {
                    int pageLowerBound = pageSize * pageIndex;
                    int pageUpperBound = pageLowerBound - pageSize;
                    string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound);
                    cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw,strKey, recordIDs, orderString), m_Conn);

                }
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord);
                DataTable dt = new DataTable();
                dataAdapter.Fill(dt);
                m_Conn.Close();
                m_Conn.Dispose();
            return dt;
            }
        }

 

public static System.Data.DataSet getPageTable(int pageSize, int pageIndex, string tab, string show, string where, string order, bool mulit)
    {
        if (pageIndex < 1) { pageIndex = 1; };
        if (pageSize < 1) { pageSize = 1; };
        StringBuilder sb = new StringBuilder();
        if (pageIndex == 1)
        {
            if (string.IsNullOrEmpty(where))
            {
                sb.AppendFormat("select COUNT(0) from {0};select top {1} {2} from {0} order by {3}", tab, pageSize, show, order);
            }
            else
            {
                sb.AppendFormat("select COUNT(0) from {0} where {4};select top {1} {2} from {0} where {4} order by {3}", tab, pageSize, show, order, where);
            }
        }
        else
        {
            int start = pageIndex * pageSize;
            int end = start - pageSize + 1;

            string pagestr = start == end ? string.Format("={0}", start) : string.Format(" between {0} and {1}", end, start);
            if (mulit)
            {
                int kgIndex = show.IndexOf(' ');
                if (string.IsNullOrEmpty(where))
                {
                    sb.AppendFormat("select COUNT(0) from {0};select top {5} {1} from (select ROW_NUMBER() over(order by {2}) rowid,{4} from {0}) tb0 where rowid{3}",
                        tab, show.Substring(kgIndex), order, pagestr, show, pageSize);
                }
                else
                {
                    sb.AppendFormat("select COUNT(0) from {0} where {1};select top {6} {2} from (select ROW_NUMBER() over(order by {3}) rowid,{5} from {0} where {1}) tb0 where rowid{4}",
                        tab, where, show.Substring(kgIndex), order, pagestr, show, pageSize);
                }
            }
            else
            {
                if (string.IsNullOrEmpty(where))
                {
                    sb.AppendFormat("select COUNT(0) from {0};select top {4} {1} from (select ROW_NUMBER() over(order by {2}) rowid,{1} from {0}) tb0 where rowid{3}",
                        tab, show, order, pagestr, pageSize);
                }
                else
                {
                    sb.AppendFormat("select COUNT(0) from {0} where {1};select top {5} {2} from (select ROW_NUMBER() over(order by {3}) rowid,{2} from {0} where {1}) tb0 where rowid{4}",
                        tab, where, show, order, pagestr, pageSize);
                }
            }
        }
        return DbHelperSQL.Query(sb.ToString());
    }