Repeater实现高效分页+静态批量删除（消息模块实现全过程）（一）
最近在做一个消息模块，这个消息模块需求如下：1、写消息，2、列表显示消息，3、根据不同的消息分类检索消息，4、查看消息，快速回复消息，5、批量删除消息，6、未读消息提醒。为实现以上一系列功能我使用了Repeater实现高效分页、JQuery+Ajax技术实现静态批量删除、查看、回复。
1、消息模块数据表
2、写消息，直接跳转到一个页面写消息，本篇重点在于Repeater实现高效分页介绍了
3、Repeater实现高效分页
Repeater在数据控件中是一个比较轻量级的控件，这也使得它用起来比较灵活，也是我非常喜欢的一个控件，使用它可以灵活的实现各种功能，在实现消息模块的时候我使用它来是实现了高效分页的功能。（为什么我说是高效分页？这里的分页并不是向其他一些实现分页的方案，是使用PagedDataSource来实现分页，它的把数据全部读出来然后再计算页数，并获取该页的数据，这也的方法在数据量比较大的时候就会导致系统运行比较慢。高效分页是每次只读出相应页数的数据，不会因数据量变大而导致的系统运行速度较慢的情况。）
用到的一个SQLHelper类的一个分页方法
/// <summary>
/// 分页查询方法，适用于任何表或者视图
/// author:Jerry
/// date:2011-7-6
///直接可以传递要取第几页，就可以取得该页数据,使用了SQL Server2005及以上版本可以计算Row_Number()的特性
/// </summary>
/// <param name="selectColumnName">要查询的字段</param>
/// <param name="tableName">要查询的表名或者视图名</param>
/// <param name="orderColumnName">要排序的字段名</param>
/// <param name="pageIndex">要查询第几页</param>
/// <param name="size">返回的最大记录数</param>
/// <param name="parameters">查询中用到的参数集合</param>
/// <returns>返回分页查询结果</returns>
public DataTable GetPagedDataTable(string selectColumnName, string orderColumnName,string where, string tableName, OrderBy orderBy, int pageIndex, int size, SqlParameter[] parameters)
{
int startIndex = (pageIndex - 1) * size + 1;//计算开始的位置
int endIndex = pageIndex * size;//计算结束的位置
string orderByString = orderBy == OrderBy.ASC ? " ASC " : " DESC ";//排序方式
StringBuilder buffer = new StringBuilder();
buffer.Append("select * from (");
buffer.AppendFormat("select {0},Row_Number() over (order by {1} {2}) rownum from {3} where {4})temp",selectColumnName,orderColumnName,orderByString,tableName,where);
buffer.AppendFormat(" where temp.rownum>={0} and temp.rownum<={1}",startIndex,endIndex);
string commText = buffer.ToString();
return ExecuteDataTable(commText,CommandType.Text,parameters);
}
实现repeater高效分页的后台代码
using System;
using System.Collections;
using System.Configuration;
using System.Data;
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 NS.Entity;
namespace erp.SystemManager
{
public partial class message : System.Web.UI.Page
{
string id = "123";//id是从session中获取,表示登录人的编号ID
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Message mess = new Message();
//Employee emp = new Employee();
//emp = (Employee)Session["ThisUser"];
string sql = "select top 10 mes_ID,mes_sendPer,mes_title,mes_addTime,mes_type,mes_Status from Message ";
sql = sql + " where mes_acceptPer='"+id+"' order by mes_addTime desc";
SqlHelper.SqlDbHelper db = new SqlHelper.SqlDbHelper();
string sql2 = "select count(*) from Message ";
sql2 = sql2 + "where mes_acceptPer='"+id+"'";
int countOfpage = int.Parse(db.ExecuteScalar(sql2).ToString());
labCurPage.Text = "1";
labZongShu.Text = countOfpage.ToString();
if (countOfpage % 10 == 0)
{
labPage.Text = (countOfpage / 10).ToString();
}
else
labPage.Text = (countOfpage / 10 + 1).ToString();
DataTable dt = new DataTable();
dt = db.ExecuteDataTable(sql);
Repeater1.DataSource = newData(dt);
Repeater1.DataBind();
}
}
private static DataTable newData(DataTable dt)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["mes_Status"].ToString() == "0")
{
dt.Rows[i]["mes_Status"] = "未阅读";
}
else if (dt.Rows[i]["mes_Status"].ToString() == "1")
{
dt.Rows[i]["mes_Status"] = "已阅读";
}
else
{
dt.Rows[i]["mes_Status"] = "已回复";
}
}
return dt;
}
protected void btnFirst_Click(object sender, ImageClickEventArgs e)
{
labCurPage.Text = "1";
bind(1); pagecount();
}
protected void btnSearch_Click(object sender, EventArgs e)
{
string where = " and ";
string type = DropDownList1.Text;
string status = DropDownList2.Text;
if (type == "全部类型的消息" && status == "全部状态的消息")
{
where = "";
}
else if (type != "全部类型的消息" && status == "全部状态的消息")
{
where = where + " mes_type ='"+type+"'";
}
else if (type == "全部类型的消息" && status != "全部状态的消息")
{
if (status == "已阅读")
{
where = where + "mes_Status='1'";
}
else if (status == "未阅读")
{
where = where + "mes_Status='0'";
}
else if (status == "已回复")
{
where = where + "mes_Status='2'";
}
}
else
{
if (status == "已阅读")
{
where = where + "mes_Status='1'";
}
else if (status == "未阅读")
{
where = where + "mes_Status='0'";
}
else if (status == "已回复")
{
where = where + "mes_Status='2'";
}
where = where + " and mes_type ='" + type + "'";
}
Session["where"] = where;
Message mess = new Message();
//Employee emp = new Employee();
//emp = (Employee)Session["ThisUser"];
string sql = "select top 10 mes_ID,mes_sendPer,mes_title,mes_addTime,mes_type,mes_Status from Message ";
sql = sql + " where mes_acceptPer='"+id+"' "+where+" order by mes_addTime desc";
SqlHelper.SqlDbHelper db = new SqlHelper.SqlDbHelper();
string sql2 = "select count(*) from Message ";
sql2 = sql2 + "where mes_acceptPer='"+id+"'";
sql2 += where;
int countOfpage = int.Parse(db.ExecuteScalar(sql2).ToString());
labCurPage.Text = "1";
labZongShu.Text = countOfpage.ToString();
if (countOfpage % 10 == 0)
{
labPage.Text = (countOfpage / 10).ToString();
}
else
labPage.Text = (countOfpage / 10 + 1).ToString();
DataTable dt = new DataTable();
dt = db.ExecuteDataTable(sql);
Repeater1.DataSource = newData(dt);
Repeater1.DataBind();
}
protected void btnback_Click(object sender, ImageClickEventArgs e)
{
string cur = labCurPage.Text;
int num = int.Parse(cur);
if (num == 1)
{
Response.Write(" <script type='text/javascript'>alert('这是第一页！');</script>");
}
else
{
num--;
labCurPage.Text = num.ToString();
bind(num); pagecount();
}
}
/// <summary>
/// 根据页数不同绑定数据
/// </summary>
/// <param name="num">页数</param>
private void bind(int num)
{
string where;
if (Session["where"] == null)
{
where = "";
}
else
where = Session["where"].ToString();
SqlHelper.SqlDbHelper db = new SqlHelper.SqlDbHelper();
where = "mes_acceptPer='" + id + "' " + where;
string selectColumn = "mes_ID,mes_sendPer,mes_title,mes_addTime,mes_type,mes_Status";
DataTable dt = new DataTable();
dt = db.GetPagedDataTable(selectColumn, "mes_addTime", where, "Message", SqlHelper.OrderBy.DESC, num, 10, null, 0);
// dt = db.GetPagedDataTable(selectColumn, "mes_addTime", "Message", SqlHelper.OrderBy.DESC, num, 10, null,0);
Repeater1.DataSource = newData(dt);
Repeater1.DataBind();
}
private void pagecount()
{
string where;
if (Session["where"] == null)
{
where = "";
}
else
where = Session["where"].ToString();
SqlHelper.SqlDbHelper db = new SqlHelper.SqlDbHelper();
string sql2 = "select count(*) from Message ";
sql2 = sql2 + "where mes_acceptPer='" + id + "'";
sql2 += where;
int countOfpage = int.Parse(db.ExecuteScalar(sql2).ToString());
labZongShu.Text = countOfpage.ToString();
if (countOfpage % 10 == 0)
{
labPage.Text = (countOfpage / 10).ToString();
}
else
labPage.Text = (countOfpage / 10 + 1).ToString();
}
protected void btnnext_Click(object sender, ImageClickEventArgs e)
{
string cur = labCurPage.Text;
string last = labPage.Text;
int zongshu = int.Parse(last);
int num = int.Parse(cur);
if (num == zongshu)
{
Response.Write(" <script type='text/javascript'>alert('这是最后一页！');</script>");
}
else
{
num++;
labCurPage.Text = num.ToString();
bind(num);
pagecount();
}
}
protected void btnLast_Click(object sender, ImageClickEventArgs e)
{
string last = labPage.Text;
int zongshu = int.Parse(last);
labCurPage.Text = last;
bind(zongshu);
pagecount();
}
protected void ImageButton1_Click(object sender, ImageClickEventArgs e)
{
string page = tbnum.Text;
string cur = labCurPage.Text;
string last = labPage.Text;
int zongshu = int.Parse(last);
int curpage = int.Parse(page);
labCurPage.Text = cur;
if (curpage > 0 && curpage <= zongshu)
{
bind(curpage); pagecount();
}
else
{
Response.Write(" <script type='text/javascript'>alert('输入页数有误！');</script>");
}
}
}
}
前台代码只贴repeater相关模块
<asp:Repeater ID="Repeater1" runat="server" EnableViewState="False">
<ItemTemplate>
<tr>
<td bgcolor="#FFFFFF" ><div align="center">
<input type="checkbox" name="checkbox3" value="checkbox" checkbox=true curId="<%# DataBinder.Eval(Container.DataItem, "mes_ID")%>" />
</div></td>
<td bgcolor="#FFFFFF"><div align="center"><span class="STYLE1"><%# DataBinder.Eval(Container.DataItem, "mes_sendPer")%></span></div></td>
<td bgcolor="#FFFFFF"><div align="center"><span class="STYLE1"><%# DataBinder.Eval(Container.DataItem, "mes_title")%></span></div></td>
<td bgcolor="#FFFFFF"><div align="center"><span class="STYLE1"><%# DataBinder.Eval(Container.DataItem, "mes_addTime")%></span></div></td>
<td bgcolor="#FFFFFF" ><div align="center"><span class="STYLE1"><%# DataBinder.Eval(Container.DataItem, "mes_type")%></span></div></td>
<td bgcolor="#FFFFFF" ><div align="center"><span status=true curId="<%# DataBinder.Eval(Container.DataItem, "mes_ID")%>" ><%# DataBinder.Eval(Container.DataItem, "mes_Status")%></span></div></td>
<td bgcolor="#FFFFFF" ><div align="center"><span class="STYLE4">
<img src="http://images.cnblogs.com/edit.gif" width="16" height="16" /><a href="#" chankan=true curId="<%# DataBinder.Eval(Container.DataItem, "mes_ID")%>">查看</a>
<img src="http://images.cnblogs.com/add.gif" width="16" height="16"><a href="EditMessage.aspx?id=<%# DataBinder.Eval(Container.DataItem, "mes_sendPer")%>">回复</a>
<img src="http://images.cnblogs.com/delete.gif" width="16" height="16" /><a href="#" delete=true curId="<%# DataBinder.Eval(Container.DataItem, "mes_ID")%>">删除</a>
</span></div></td>
</tr>
</ItemTemplate>
</asp:Repeater>
Repeater实现高效分页实现完成
由于本人是个新手，贴了很多代码，因为篇幅太长批量静态删除下篇再说，欢迎批评指正。。
作者: Jerry_Wang 发表于 2011-07-15 14:04 原文链接
推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架