asp.net 分页实现

先看效果

 

涉及3个文件，一个数据库连接类DbUtil，一个分页类PageUtil，还有一个实例cs文件，用起来相当省心

DbUtil.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
using tour.exception;

namespace tour.db
{
    ////抛出TourException异常，上一层未捕捉处理异常，待处理
    public class DbUtil
    {
        public SqlConnection strcon;
        public DbUtil() {
            strcon = new SqlConnection(System.Configuration.ConfigurationManager.AppSettings["conn"]);
        }
        /*
         * 查询一条记录
         * 返回 SqlDataReader
         * 参数 sql语句
         * 放弃此方法，没有关闭连接，需注意外部关闭
         * */
        public SqlDataReader getOneData(string sql)
        {
            try
            {
                openConn();
            }
            catch (TourException e)
            {

                throw e;
            }
           
            SqlCommand comm = new SqlCommand(sql, strcon);
            SqlDataReader read = null;
            try
            {
               read = comm.ExecuteReader();
            }
            catch (SqlException) {
               // throw new TourException("在对锁定的行执行该命令期间发生了异常");
                throw new TourException(sql);
            }
            catch (Exception)
            {
               // throw new TourException("未能执行此命令");
                throw new TourException(sql);
            }
            return read;
        }
        /*
         * 执行增删改操作
         * 参数 sql语句
         *
         * */
        public void updateData(string sql)
        {
            try
            {
                openConn();
            }
            catch (Exception e)
            {

                throw new TourException("在打开连接时出现连接级别的错误！");
            }
            SqlCommand cmd = new SqlCommand(sql, strcon);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
              //  throw new TourException("在对锁定的行执行该命令期间发生了异常");
                throw new TourException(sql);
            }
            try
            {
                closeConn();
            }
            catch (Exception e)
            {

                throw new TourException("关闭连接故障");
            }
          
        }
        /*
         * 关闭连接
         * */
        public void closeConn()
        {
            try
            {
                if (strcon.State != ConnectionState.Closed) {
                    strcon.Close();
                }
            }
            catch (Exception)
            {
                throw new TourException("在打开连接时出现连接级别的错误");
            }
           
        }
        private void openConn()
        {
            try
            {
                if (strcon.State != ConnectionState.Open)
                {
                    strcon.Open();
                }

            }
            catch (InvalidOperationException ee)
            {
                throw new TourException("未指定数据源或服务器，不能打开连接或连接已打开！");
            }
            catch (Exception)
            {
                throw new TourException("在打开连接时出现连接级别的错误！");
            }
           
        }
        public DataSet getDataSet(string sql){
             try
            {
                openConn();
            }
            catch (Exception e)
            {
               // throw new TourException("未指定数据源或服务器，不能打开连接或连接已打开！");
                throw new TourException(sql);
            }
            SqlDataAdapter sda = new SqlDataAdapter(sql, strcon);
            DataSet ds = new DataSet();
            try
            {
                sda.Fill(ds);
            }
            catch (Exception te)
            {

                throw new TourException(sql);
            }
            try
            {
                closeConn();
            }
            catch (Exception e)
            {

                throw new TourException("关闭连接故障");
            }
          
           
            return ds;
        }
     
    }
}
PageUtil.cs

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text;
using tour.db;

namespace tour.tools.page
{
    public class PageUtil
    {
        private string pageUrl;//当前页面相对路径
      
        private int totalPage;//总页数

      
        private int prePageIndex;//上一页

      
        private int nextPageIndex;//下一页

      
        private int currPageIndex;//当前页

       
      //  private int firstPageIndex;//第一页

       
      //  private int lastPageIndex;//最后一页

       
        private int totalRec;//总记录数

       
        private int pageSize;//每页几条记录

        private string sql; //sql

        private string pk; //主键

        private string desc;//排序

        private DbUtil du = new DbUtil();
      
        public PageUtil(int totalRec, int currPageIndex, int pageSize,string pageUrl)
        {
            this.totalRec = totalRec;
            this.currPageIndex = currPageIndex;
            this.pageSize = pageSize;
            this.pageUrl = pageUrl;
            init();
        }
        public PageUtil(int currPageIndex,int pageSize,string pageUrl,string sql,string pk,string desc){
            this.currPageIndex = currPageIndex;
            this.pageSize = pageSize;
            this.pageUrl = pageUrl;
            this.sql = sql;
            this.pk = pk;
            this.desc = desc;
            init();
        }
        private void init()
        {
            //总页数
            this.totalRec = computeTotalRec();
            totalPage = totalRec / pageSize;
            if (totalRec % pageSize > 0)
                totalPage = totalPage + 1;
           // this.pageUrl += "?p=p";
        }
        private int computeTotalRec() {
          StringBuilder sb = new StringBuilder();
          sb.Append("select count(*) c from");
          sb.Append("(");
          sb.Append(this.sql);
          sb.Append(")count_table");
          DataSet ds = du.getDataSet(sb.ToString());
          return Convert.ToInt32(ds.Tables[0].Rows[0]["c"].ToString());
        }
        public DataSet pageDataSet() {
            int n1 = (Convert.ToInt32(currPageIndex) - 1) * Convert.ToInt32(pageSize) + 1;
            int n2 = n1 + Convert.ToInt32(pageSize) - 1;
            StringBuilder sb = new StringBuilder();
            sb.Append("select tb1.* from");
            sb.Append("(");
            sb.Append("select tb.*,row_number() over(order by "+this.pk+" "+this.desc+")rn from");
                sb.Append("(");
                sb.Append(this.sql);
                sb.Append(")tb");
            sb.Append(")tb1");
            sb.Append(" where tb1.rn<=" + n2);
            sb.Append(" and tb1.rn>=" + n1);
            return du.getDataSet(sb.ToString());
        }
        public string createPageFooter()
        {
            StringBuilder str = new StringBuilder();

           
            this.prePageIndex = currPageIndex - 1;
            this.nextPageIndex = currPageIndex + 1;

            if (currPageIndex > 1)
            {
                str.Append(
                    "<a href='" + this.pageUrl + "&page=1&pageSize="+this.pageSize+"'>首页</a>&nbsp;");
            }
            else
            {
                str.Append("首页&nbsp;");
            }
            if (currPageIndex > 1)
            {
                str.Append(
                    "<a href='" + this.pageUrl + "&page=" + this.prePageIndex + "&pageSize=" + this.pageSize + "'>上页</a>&nbsp;");
            }
            else
            {
                str.Append("上页&nbsp;");
            }
            str.Append("&nbsp;当前" + this.currPageIndex + "页&nbsp;");
            if (currPageIndex < totalPage)
            {
                str.Append(
                    "<a href='" + this.pageUrl + "&page=" + this.nextPageIndex + "&pageSize=" + this.pageSize + "'>下页</a>&nbsp;");
            }
            else
            {
                str.Append("下页");
            }
            if (totalPage > 1 && currPageIndex != totalPage)
            {
                str.Append(
                    "<a href='" + this.pageUrl + "&page=" + this.totalPage + "&pageSize=" + this.pageSize + "'>末页</a>&nbsp;&nbsp;");
            }
            else
            {
                str.Append("末页");
            }
            str.Append(" 共" + totalRec + "条记录");
            str.Append("  每页<SELECT size=1 name=pagesize onchange=/"window.location.href=this.value/">");

            if (pageSize == 3)
            {
                str.Append("<OPTION value="+this.pageUrl+"&pageSize=3 selected>3</OPTION>");
            }
            else
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=3>3</OPTION>");
            }

            if (pageSize == 10)
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=10 selected>10</OPTION>");
            }
            else
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=10>10</OPTION>");
            }
            if (pageSize == 20)
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=20 selected>20</OPTION>");
            }
            else
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=20>20</OPTION>");
            }
            if (pageSize == 50)
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=50 selected>50</OPTION>");
            }
            else
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=50>50</OPTION>");
            }
            if (pageSize == 100)
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=100 selected>100</OPTION>");
            }
            else
            {
                str.Append("<OPTION value=" + this.pageUrl + "&pageSize=100>100</OPTION>");
            }
            str.Append("</SELECT>");
            str.Append("条 分" + totalPage + "页显示 转到");
            str.Append("<SELECT size=1 name=Pagelist onchange=/"window.location.href=this.value/">");
            for (int i = 1; i < totalPage + 1; i++)
            {
                if (i == currPageIndex)
                {
                    str.Append("<OPTION value=" + this.pageUrl + "&page=" + i + "&pageSize=" + this.pageSize + " selected>" + i +
                               "</OPTION>");
                }        
                else        
                {
                    str.Append("<OPTION value=" + this.pageUrl + "&page=" + i + "&pageSize=" + this.pageSize + ">" + i + "</OPTION>");
                }
            }
            str.Append("</SELECT>页");
    
            return str.ToString();
        }

    }
}
以一个例子来说明如何应用，这里要实现根据栏目ID查询该栏目下所有文章并分页显示

 string lmid = Request["lmid"];
            //当前页
            string currPageIndex = Request["page"];
            //每页几条
            string pageSize = Request["pageSize"];
            if ("".Equals(currPageIndex) || currPageIndex == null)
            {
                currPageIndex = "1";
            }
            if ("".Equals(pageSize) || pageSize == null)
            {
                pageSize = "20";
            }
 
            StringBuilder sql = new StringBuilder();
      
            sql.Append("select t.*,tt.lmName from t_news t,t_lanmu tt where t.lid=tt.lid");
            sql.Append(" and t.lid ="+lmid);
            pu = new PageUtil(Convert.ToInt32(currPageIndex), Convert.ToInt32(pageSize), "newsBylm.aspx?lmid=" + lmid,sql.ToString(),"nid","desc");
            DataSet ds1 = pu.pageDataSet();
            Repeater1.DataSource = ds1; //前台用repeater绑定。
            Repeater1.DataBind();
            l_lmName.Text = ds1.Tables[0].Rows[0]["lmName"].ToString(); //这个是栏目名称
            l_page.Text = pu.createPageFooter();