先看效果
涉及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> ");
}
else
{
str.Append("首页 ");
}
if (currPageIndex > 1)
{
str.Append(
"<a href='" + this.pageUrl + "&page=" + this.prePageIndex + "&pageSize=" + this.pageSize + "'>上页</a> ");
}
else
{
str.Append("上页 ");
}
str.Append(" 当前" + this.currPageIndex + "页 ");
if (currPageIndex < totalPage)
{
str.Append(
"<a href='" + this.pageUrl + "&page=" + this.nextPageIndex + "&pageSize=" + this.pageSize + "'>下页</a> ");
}
else
{
str.Append("下页");
}
if (totalPage > 1 && currPageIndex != totalPage)
{
str.Append(
"<a href='" + this.pageUrl + "&page=" + this.totalPage + "&pageSize=" + this.pageSize + "'>末页</a> ");
}
else
{
str.Append("末页");
}
str.Append(" 共" + totalRec + "条记录");
str.Append(" 每页<SELECT size=1 name=pagesize οnchange=/"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 οnchange=/"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();