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();