存储过程分页

一，首先创建分页存储过程
CREATE PROCEDURE usp_PagingLarge @TableNames VARCHAR(300), --表名，可以是多个表，但不能用别名 @PrimaryKey VARCHAR(100), --主键，可以为空，但@Order为空时该值不能为空 @Fields VARCHAR(350), --要取出的字段，可以是多个表的字段，可以为空，为空表示select * @PageSize INT, --每页记录数 @CurrentPage INT, --当前页，0表示第1页 @Filter VARCHAR(200) = '', --条件，可以为空，不用填 where @Group VARCHAR(200) = '', --分组依据，可以为空，不用填 group by @Order VARCHAR(200) = '', --排序，可以为空，为空默认按主键升序排列，不用填 order by @RecordCount int = 0 output AS BEGIN DECLARE @SortColumn VARCHAR(200) DECLARE @Operator CHAR(2) DECLARE @SortTable VARCHAR(200) DECLARE @SortName VARCHAR(200) DECLARE @TmpSelect NVarchar(200) IF @Fields = '' SET @Fields = '*' IF @Filter = '' SET @Filter = 'WHERE 1=1' ELSE SET @Filter = 'WHERE ' + @Filter IF @Group <>'' SET @Group = 'GROUP BY ' + @Group IF @Order <> '' BEGIN DECLARE @pos1 INT, @pos2 INT SET @Order = REPLACE(REPLACE(@Order, ' asc', ' ASC'), ' desc', ' DESC') IF CHARINDEX(' DESC', @Order) > 0 IF CHARINDEX(' ASC', @Order) > 0 BEGIN IF CHARINDEX(' DESC', @Order) < CHARINDEX(' ASC', @Order) SET @Operator = '<=' ELSE SET @Operator = '>=' END ELSE SET @Operator = '<=' ELSE SET @Operator = '>=' SET @SortColumn = REPLACE(REPLACE(REPLACE(@Order, ' ASC', ''), ' DESC', ''), ' ', '') SET @pos1 = CHARINDEX(',', @SortColumn) IF @pos1 > 0 SET @SortColumn = SUBSTRING(@SortColumn, 1, @pos1-1) SET @pos2 = CHARINDEX('.', @SortColumn) IF @pos2 > 0 BEGIN SET @SortTable = SUBSTRING(@SortColumn, 1, @pos2-1) IF @pos1 > 0 SET @SortName = SUBSTRING(@SortColumn, @pos2+1, @pos1-@pos2-1) ELSE SET @SortName = SUBSTRING(@SortColumn, @pos2+1, LEN(@SortColumn)-@pos2) END ELSE BEGIN SET @SortTable = @TableNames SET @SortName = @SortColumn END END ELSE BEGIN SET @SortColumn = @PrimaryKey SET @SortTable = @TableNames SET @SortName = @SortColumn SET @Order = @SortColumn SET @Operator = '>=' END DECLARE @type varchar(50) DECLARE @prec int SELECT @type=t.name, @prec=c.prec FROM sysobjects o JOIN syscolumns c on o.id=c.id JOIN systypes t on c.xusertype=t.xusertype WHERE o.name = @SortTable AND c.name = @SortName IF CHARINDEX('char', @type) > 0 SET @type = @type + '(' + CAST(@prec AS varchar) + ')' DECLARE @TopRows INT SET @TopRows = @PageSize * @CurrentPage + 1 print @TopRows print @Operator EXEC(' DECLARE @SortColumnBegin ' + @type + ' SET ROWCOUNT ' + @TopRows + ' SELECT @SortColumnBegin=' + @SortColumn + ' FROM ' + @TableNames + ' ' + @Filter + ' ' + @Group + ' ORDER BY ' + @Order + ' SET ROWCOUNT ' + @PageSize + ' SELECT ' + @Fields + ' FROM ' + @TableNames + ' ' + @Filter + ' AND ' + @SortColumn + '' + @Operator + '@SortColumnBegin ' + @Group + ' ORDER BY ' + @Order + ' ') DECLARE @str_Count_SQL nvarchar(500) SET @str_Count_SQL= 'SELECT @TotalCount=count('+@PrimaryKey+') FROM ' + @TableNames + ' ' + @Filter EXEC sp_executesql @str_Count_SQL,N'@TotalCount int=0 output',@RecordCount output End GO
二，编写sqlHelper方法 
        /// <summary> /// 执行存储过程返回 DataTable /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>DataTable</returns> public static DataTable ExecuteDataTableByProc(string storedProcName, SqlParameter[] parameters) { using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); SqlDataAdapter da = new SqlDataAdapter(); DataSet ds = new DataSet(); da.SelectCommand = command; da.Fill(ds); DataTable dt = ds.Tables[0]; return dt; } } /// <summary> /// 执行存储过程返回SqlDataReader /// </summary> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlDataReader</returns> public static SqlDataReader ExecuteSqlDataReaderByProc(string storedProcName, SqlParameter[] parameters) { SqlConnection connection = new SqlConnection(connectionString); SqlDataReader returnReader; connection.Open(); SqlCommand command = BuildQueryCommand(connection, storedProcName, parameters); command.CommandType = CommandType.StoredProcedure; returnReader = command.ExecuteReader(CommandBehavior.CloseConnection); return returnReader; } /// <summary> /// 构建 SqlCommand 对象(用来返回一个结果集，而不是一个整数值) /// </summary> /// <param name="connection">数据库连接</param> /// <param name="storedProcName">存储过程名</param> /// <param name="parameters">存储过程参数</param> /// <returns>SqlCommand</returns> private static SqlCommand BuildQueryCommand(SqlConnection connection,string storedProcName, SqlParameter[] parameters) { SqlCommand command = new SqlCommand(storedProcName, connection); command.CommandType = CommandType.StoredProcedure; foreach (SqlParameter parameter in parameters) command.Parameters.Add( parameter ); return command; }
 

数据库层代码如下：

public class Notice:INotice { public Notice() { } public int GetMaxId() { StringBuilder strSQL = new StringBuilder(); strSQL.Append("select max(NoticeID)+1 from Notice"); object obj = DbHelperSQL.ExecuteScalar(strSQL.ToString()); if (obj == null) return 1; else return Convert.ToInt32(obj); } public bool Exists(int NoticeID) { StringBuilder strSQL = new StringBuilder(); strSQL.Append("select count(1) from Notice "); strSQL.Append("where NoticeID=@NoticeID"); SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4); parm.Value = NoticeID; object obj = DbHelperSQL.ExecuteScalar(strSQL.ToString(), parm); int result; if ((object.Equals(obj, null)) || (object.Equals(obj, System.DBNull.Value))) result = 0; else result = Convert.ToInt32(obj.ToString()); if (result == 0) return false; else return true; } public void Add(Model.Notice model) { StringBuilder strSQL = new StringBuilder(); strSQL.Append("insert into Notice values ("); strSQL.Append("@NoticeTitle,@TitleColor,@NoticeContent,@Disabled,@IsTop,@AddDate,@AddAdminName)"); SqlParameter[] parlist ={ new SqlParameter("@NoticeTitle",SqlDbType.VarChar,100), new SqlParameter("@TitleColor",SqlDbType.VarChar,50), new SqlParameter("@NoticeContent",SqlDbType.Text), new SqlParameter("@Disabled",SqlDbType.Bit,1), new SqlParameter("@IsTop",SqlDbType.Bit,1), new SqlParameter("@AddDate",SqlDbType.DateTime,8), new SqlParameter("@AddAdminName",SqlDbType.VarChar,50), }; parlist[0].Value = model.NoticeTitle; parlist[1].Value = model.TitleColor; parlist[2].Value = model.NoticeContent; parlist[3].Value = model.Disabled; parlist[4].Value = model.IsTop; parlist[5].Value = model.AddDate; parlist[6].Value = model.AddAdminName; DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parlist); } public void Update(Model.Notice model) { StringBuilder strSQL = new StringBuilder(); strSQL.Append("update Notice set "); strSQL.Append("NoticeTitle=@NoticeTitle,"); strSQL.Append("TitleColor=@TitleColor,"); strSQL.Append("NoticeContent=@NoticeContent,"); strSQL.Append("Disabled=@Disabled,"); strSQL.Append("IsTop=@IsTop,"); strSQL.Append("AddDate=@AddDate,"); strSQL.Append("AddAdminName=@AddAdminName "); strSQL.Append("where NoticeID=@NoticeID"); SqlParameter[] parlist ={ new SqlParameter("@NoticeTitle",SqlDbType.VarChar,100), new SqlParameter("@TitleColor",SqlDbType.VarChar,50), new SqlParameter("@NoticeContent",SqlDbType.Text), new SqlParameter("@Disabled",SqlDbType.Bit,1), new SqlParameter("@IsTop",SqlDbType.Bit,1), new SqlParameter("@AddDate",SqlDbType.DateTime,8), new SqlParameter("@AddAdminName",SqlDbType.VarChar,50), new SqlParameter("@NoticeID",SqlDbType.Int,4), }; parlist[0].Value = model.NoticeTitle; parlist[1].Value = model.TitleColor; parlist[2].Value = model.NoticeContent; parlist[3].Value = model.Disabled; parlist[4].Value = model.IsTop; parlist[5].Value = model.AddDate; parlist[6].Value = model.AddAdminName; parlist[7].Value = model.NoticeID; DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parlist); } public void Delete(int NoticeID) { StringBuilder strSQL = new StringBuilder(); strSQL.Append("delete from Notice "); strSQL.Append("where NoticeID=@NoticeID"); SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4); parm.Value = NoticeID; DbHelperSQL.ExecuteNonQueryBySQL(strSQL.ToString(), parm); } public Model.Notice GetModel(int NoticeID) { StringBuilder strSQL = new StringBuilder(); strSQL.Append("select NoticeTitle,TitleColor,NoticeContent,Disabled,IsTop,AddDate,AddAdminName from Notice "); strSQL.Append("where NoticeID=@NoticeID"); SqlParameter parm = new SqlParameter("@NoticeID", SqlDbType.Int, 4); parm.Value = NoticeID; Model.Notice model = new Model.Notice(); //using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderBySQL(strSQL.ToString(), parm)) //{ // if (dr.Read()) // { // model.NoticeTitle = dr.GetString(0); // model.TitleColor = dr.GetString(1); // model.NoticeContent = dr.GetString(2); // model.Disabled = dr.GetBoolean(3); // model.IsTop = dr.GetBoolean(4); // model.AddDate = dr.GetDateTime(5); // model.AddAdminName = dr.GetString(6); // } //} //return model; DataSet ds = DbHelperSQL.ExecuteDataSetBySQL(strSQL.ToString(), parm); if (ds.Tables[0].Rows.Count > 0) { model.NoticeTitle = ds.Tables[0].Rows[0]["NoticeTitle"].ToString(); model.TitleColor = ds.Tables[0].Rows[0]["TitleColor"].ToString(); model.NoticeContent = ds.Tables[0].Rows[0]["NoticeContent"].ToString(); model.AddAdminName = ds.Tables[0].Rows[0]["AddAdminName"].ToString(); if ((ds.Tables[0].Rows[0]["Disabled"].ToString() == "1") || (ds.Tables[0].Rows[0]["Disabled"].ToString().ToLower() == "true")) model.Disabled = true; else model.Disabled = false; if ((ds.Tables[0].Rows[0]["IsTop"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsTop"].ToString().ToLower() == "true")) model.IsTop = true; else model.IsTop = false; if (ds.Tables[0].Rows[0]["AddDate"].ToString() != "") model.AddDate = DateTime.Parse(ds.Tables[0].Rows[0]["AddDate"].ToString()); return model; } else return null; } public DataSet GetDataSet(string strWhere) { StringBuilder strSQL = new StringBuilder(); strSQL.Append("select * from Notice"); if (strWhere.Trim() != "") strSQL.Append(" where " + strWhere); strSQL.Append(" order by NoticeID desc"); return DbHelperSQL.ExecuteDataSetBySQL(strSQL.ToString()); } public int GetRecordCount(string strWhere) { int result = 0; SqlParameter[] parlist ={ new SqlParameter("@tblName",SqlDbType.VarChar,50), new SqlParameter("@strWhere",SqlDbType.VarChar,1000), }; parlist[0].Value = "Notice"; parlist[1].Value = strWhere; using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("GetRecordCount", parlist)) { if (dr.Read()) result = dr.GetInt32(0); } return result; } public IList<Model.Notice> GetList(string filename, string sortfilename, int pageSize, int pageIndex, int OrderType, string strWhere) { IList<Model.Notice> notices = new List<Model.Notice>(); Model.Notice model = null; SqlParameter[] parlist ={ new SqlParameter("@tblName",SqlDbType.VarChar,255), new SqlParameter("@strGetFields",SqlDbType.VarChar,1000), new SqlParameter("@fldName",SqlDbType.VarChar,255), new SqlParameter("@PageSize",SqlDbType.Int), new SqlParameter("@PageIndex",SqlDbType.Int), new SqlParameter("@OrderType",SqlDbType.Int), new SqlParameter("@strWhere",SqlDbType.VarChar,500), }; parlist[0].Value = "Notice"; parlist[1].Value = filename; parlist[2].Value = sortfilename; parlist[3].Value = pageSize; parlist[4].Value = pageIndex; parlist[5].Value = OrderType; parlist[6].Value = strWhere; using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("UP_GetRecordByPage", parlist)) { while (dr.Read()) { model = new Model.Notice(); model.NoticeID = dr.GetInt32(0); model.NoticeTitle = dr.GetString(1); model.TitleColor = dr.GetString(2); model.NoticeContent = dr.GetString(3); model.Disabled = dr.GetBoolean(4); model.IsTop = dr.GetBoolean(5); model.AddDate = dr.GetDateTime(6); model.AddAdminName = dr.GetString(7); notices.Add(model); } } return notices; } public IList<Model.Notice> GetListByWhere(string strWhere, string Order, int PageSize, int CurrentPage, out int Record) { IList<Model.Notice> notice = new List<Model.Notice>(); Model.Notice model = null; SqlParameter[] parlist ={ new SqlParameter("@TableNames",SqlDbType.VarChar,300), new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100), new SqlParameter("@Fields",SqlDbType.VarChar,350), new SqlParameter("@PageSize",SqlDbType.Int), new SqlParameter("@CurrentPage",SqlDbType.Int), new SqlParameter("@Filter",SqlDbType.VarChar,200), new SqlParameter("@Group",SqlDbType.VarChar,200), new SqlParameter("@Order",SqlDbType.VarChar,200), new SqlParameter("@RecordCount",SqlDbType.Int), }; parlist[0].Value = "Notice"; parlist[1].Value = "NoticeID"; parlist[2].Value = "NoticeID,NoticeTitle,TitleColor,NoticeContent,AddDate,IsTop"; parlist[3].Value = PageSize; parlist[4].Value = CurrentPage; parlist[5].Value = strWhere; parlist[6].Value = ""; parlist[7].Value = Order; parlist[8].Direction = ParameterDirection.Output; using (SqlDataReader dr = DbHelperSQL.ExecuteSqlDataReaderByProc("usp_PagingLarge", parlist)) { while (dr.Read()) { model = new Model.Notice(); model.NoticeID = dr.GetInt32(0); model.NoticeTitle = dr.GetString(1); model.TitleColor = dr.GetString(2); model.NoticeContent = dr.GetString(3); model.AddDate = dr.GetDateTime(4); model.IsTop = dr.GetBoolean(5); notice.Add(model); } } Record = Convert.ToInt32(parlist[8].Value); return notice; } public DataTable GetListByWhereDataTable(string strWhere, string Order, int PageSize, int CurrentPage, out int Record) { DataTable dt = new DataTable(); SqlParameter[] parlist ={ new SqlParameter("@TableNames",SqlDbType.VarChar,300), new SqlParameter("@PrimaryKey",SqlDbType.VarChar,100), new SqlParameter("@Fields",SqlDbType.VarChar,350), new SqlParameter("@PageSize",SqlDbType.Int), new SqlParameter("@CurrentPage",SqlDbType.Int), new SqlParameter("@Filter",SqlDbType.VarChar,200), new SqlParameter("@Group",SqlDbType.VarChar,200), new SqlParameter("@Order",SqlDbType.VarChar,200), new SqlParameter("@RecordCount",SqlDbType.Int), }; parlist[0].Value = "Notice"; parlist[1].Value = "NoticeID"; parlist[2].Value = "NoticeID,NoticeTitle,TitleColor,NoticeContent,AddDate,IsTop"; parlist[3].Value = PageSize; parlist[4].Value = CurrentPage; parlist[5].Value = strWhere; parlist[6].Value = ""; parlist[7].Value = Order; parlist[8].Direction = ParameterDirection.Output; dt = DbHelperSQL.ExecuteDataTableByProc("usp_PagingLarge", parlist); Record = Convert.ToInt32(parlist[8].Value); return dt; } }
前台页面代码：

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head id="Head1" runat="server"> <title>存储过程分页</title> <meta http-equiv="Content-Language" content="zh-CN" /> <meta content="all" name="MyWeb" /> <meta name="author" content="晴儿" /> <meta name="Copyright" content="版权归晴儿所有" /> <meta name="description" content="晴儿个人网站系统" /> <meta content="文章,新闻,相册,实用工具" name="keywords" /> <meta content="文章,新闻,相册,实用工具" name="searchtitle" /> <meta http-equiv="Content-Type" content="text/html; charset=gb2312" /> <link href="../images/style.css" type="text/css" rel="stylesheet" /> <script language="javascript" type="text/javascript" src="../Script/DateOperate.js"></script> <script language="javascript" type="text/javascript" src="../Script/calendar.js"></script> <script language="javascript" type="text/javascript"> function check() { var checks=false; var innar=document.getElementsByTagName("input"); for(var i=0;i<innar.length;i++) { if(innar.type=="checkbox") { if(innar.checked==true) checks=true; } } if(checks==false) { alert("您未选中任何记录！"); return false; } else { return confirm('您确定要删除所有选中的记录吗?'); return true; } } function quanxuan() { var innar=document.getElementsByTagName("input"); for(var i=0;i<innar.length;i++) { if(innar.type=="checkbox") innar.checked=true; } } function fanxuan() { var innar=document.getElementsByTagName("input"); for(var i=0;i<innar.length;i++) { if(innar.type=="checkbox") { if(innar.checked==true) innar.checked=false; else innar.checked=true; } } } function yanzheng() { if(isDate(document.getElementById("txtStartTime").value)==false) { alert('开始日期时间格式错误！正确格式为：2000-12-12 00:00:00'); return false; } if(isDate(document.getElementById("txtEndTime").value)==false) { alert('结束日期时间格式错误！正确格式为：2000-12-12 00:00:00'); return false; } if(compareDate(document.getElementById("txtStartTime").value,document.getElementById("txtEndTime").value)=="0") { alert('查询开始日期必须小于结束日期！'); return false; } } </script> </head> <body> <form id="form2" runat="server"> <div id="hearder"> <span>公告管理</span></div> <div id="site"> 您的位置：<a href="../index.aspx"> 管理首页 </a>>> 公告管理 </div> <table class="table1" border="1" cellpadding="2" cellspacing="1"> <tr class="tr1"> <td class="td1"> 公告标题： </td> <td class="td2"> <asp:TextBox ID="txtKey" runat="server" CssClass="input1" Width="150px"></asp:TextBox> </td> <td class="td1"> 状态： </td> <td class="td2"> <select id="Status" runat="server" class="select1"> <option selected="selected" value="">所有</option> <option value="1">置顶</option> <option value="0">不置顶</option> </select> </td> <td rowspan="2" class="td3"> <asp:Button ID="btnSearch" runat="server" Text="查询" CssClass="button" OnClientClick="return yanzheng();" OnClick="btnSearch_Click" /> </td> </tr> <tr class="tr1"> <td class="td1"> 时间段： </td> <td class="td2"> <table cellpadding="0" cellspacing="0" border="0"> <tr> <td> <asp:TextBox ID="txtStartTime" CssClass="input1" Width="140px" runat="server"></asp:TextBox> </td> <td> <a href="javascript:void(0);" οnclick="javascript:setday(this,txtStartTime,true,false);" style="font-weight: bold; color: Red;">选择</a> </td> <td> --- </td> <td> <asp:TextBox ID="txtEndTime" CssClass="input1" Width="140px" runat="server"></asp:TextBox> </td> <td> <a href="javascript:void(0);" οnclick="javascript:setday(this,txtEndTime,true,false);" style="font-weight: bold; color: Red;">选择</a> </td> <td style="font-weight: bold; color: Fuchsia; font-size: 13px">(默认查询一个月记录)</td> </tr> </table> </td> </tr> </table> <table class="table2"> <tr class="tablehearder4"> <td class="tablehearder1" align="center"> 选择 </td> <td class="tablehearder1" align="center"> 公告标题 </td> <td class="tablehearder1" align="center"> 公告信息 </td> <td align="center" class="tablehearder1" > 是否置顶 </td> <td align="center" class="tablehearder1"> 添加时间 </td> <td align="center" class="tablehearder1"> 修改 </td> <td align="center" class="tablehearder1"> 删除 </td> </tr> <asp:Repeater ID="Repeater1" runat="server" OnItemCommand="Repeater1_ItemCommand"> <ItemTemplate> <tr class="tr2" οnmοuseοver="this.style.backgroundColor='#fffff2';this.style.color='red'" οnmοuseοut="this.style.backgroundColor='#ffffff';this.style.color=''"> <td height="25"> <asp:CheckBox ID="CheckBox1" runat="server" /><asp:HiddenField ID="HiddenField1" runat="server" Value='<%#Eval("NoticeID") %>' /> </td> <td> <span style='color: <%#Eval("TitleColor") %>'> <%#PageValidate.CutString(Eval("NoticeTitle").ToString(),12)%> </span> </td> <td align="center"> <%#PageValidate.CutString(Eval("NoticeContent").ToString(),20)%> </td> <td align="center"> <%#WebUtinity.GetIsTop(Eval("IsTop").ToString())%> </td> <td align="center"> <%#Eval("AddDate")%> </td> <td align="center"> <a href='#'> <img src="../images/icon_xg.gif" alt="点击修改公告" /></a> </td> <td align="center"> <asp:ImageButton ID="imbDelete" runat="server" CommandName="delete" CommandArgument='<%#Eval("NoticeID") %>' ImageUrl="../images/icon_sc.gif" AlternateText="删除" OnClientClick="return confirm('确认删除此公告吗? 删除后不可恢复！');"> </asp:ImageButton> </td> </tr> </ItemTemplate> <AlternatingItemTemplate> <tr class="tr2" οnmοuseοver="this.style.backgroundColor='#fffff4';this.style.color='red'" οnmοuseοut="this.style.backgroundColor='#EEF7FF';this.style.color=''"> <td height="25"> <asp:CheckBox ID="CheckBox1" runat="server" /><asp:HiddenField ID="HiddenField1" runat="server" Value='<%#Eval("NoticeID") %>' /> </td> <td> <span style='color: <%#Eval("TitleColor") %>'> <%#PageValidate.CutString(Eval("NoticeTitle").ToString(),12)%> </span> </td> <td align="center"> <%#PageValidate.CutString(Eval("NoticeContent").ToString(),20)%> </td> <td align="center"> <%#WebUtinity.GetIsTop(Eval("IsTop").ToString())%> </td> <td align="center"> <%#Eval("AddDate")%> </td> <td align="center"> <a href='#'> <img src="../images/icon_xg.gif" alt="点击修改公告" /></a> </td> <td align="center"> <asp:ImageButton ID="imbDelete" runat="server" CommandName="delete" CommandArgument='<%#Eval("NoticeID") %>' ImageUrl="../images/icon_sc.gif" AlternateText="删除" OnClientClick="return confirm('确认删除此公告吗? 删除后不可恢复！');"> </asp:ImageButton> </td> </tr> </AlternatingItemTemplate> </asp:Repeater> <asp:Panel ID="Panel3" runat="server"> <tr class="tablehearder2"> <td align="center" colspan="10"> <asp:LinkButton ID="lbtDeleteAll" runat="server" OnClick="lbtDeleteAll_Click" OnClientClick="return check();" ForeColor="#0066cc" Font-Bold="true">删除选中</asp:LinkButton> <a href="javascript:quanxuan();" style="font-weight: bold; color: #0066cc">全选</a> <a href="javascript:fanxuan();" style="font-weight: bold; color: #0066cc">反选</a> </td> </tr> </asp:Panel> <asp:Panel ID="Panel1" runat="server" Visible="false"> <tr class="tablehearder2"> <td colspan="10" class="tablehearder3"> 没有查询到符合该查询条件的记录！<a href="#" style="color: Blue; font-weight: bold">点击此处</a>添加公告 </td> </tr> </asp:Panel> </table> 分页样式一：<asp:Label ID="Label2" runat="server" Text="Label"></asp:Label>     <asp:Label ID="Label3" runat="server" Text="Label"></asp:Label> 分页样式二：<asp:Label ID="Label4" runat="server" Text="Label"></asp:Label>     <asp:Label ID="Label5" runat="server" Text="Label"></asp:Label> </form> </body> </html>

 

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; public partial class Admin_noticeManage_Notice : System.Web.UI.Page { BLL.Notice notice = new BLL.Notice(); // 总记录 private int Record; // 每页显示记录数 private int PageSize = 10; // 当前页 private int CurrentPage = 1; private string strUrl = ""; protected void Page_Load(object sender, EventArgs e) { if (Request.QueryString["page"] != null) { if (!Int32.TryParse(Request.QueryString["page"].ToString(), out CurrentPage)) { ClientScript.RegisterStartupScript(GetType(), "", "alert('请输入整数页！');", true); return; } } if (!IsPostBack) { BindData(); BindSOSOPager(); BindPager(); if (Repeater1.Items.Count < 1) { Panel1.Visible = true; Panel3.Visible = false; } } } private void BindData() { try { int y = Convert.ToInt32(DateTime.Now.Year.ToString()); int m = Convert.ToInt32(DateTime.Now.Month.ToString()); int d = Convert.ToInt32(DateTime.Now.Day.ToString()); if (m == 1) txtStartTime.Text = Convert.ToString(y - 1) + "-" + "12" + "-" + d.ToString() + " " + DateTime.Now.ToLongTimeString(); else txtStartTime.Text = y.ToString() + "-" + Convert.ToInt32(m - 1) + "-" + d.ToString() + " " + DateTime.Now.ToLongTimeString(); txtEndTime.Text = DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToLongTimeString(); string strWhere = ""; if (Request.QueryString["key"] != null && Request.QueryString["istop"] != null && Request.QueryString["starttime"] != null && Request.QueryString["endtime"] != null) { txtKey.Text = Request.QueryString["key"]; Status.Value = Request.QueryString["istop"]; txtStartTime.Text = Request.QueryString["starttime"]; txtEndTime.Text = Request.QueryString["endtime"]; strUrl = string.Format("&key={0}&istop={1}&starttime={2}&endtime={3}", Request.QueryString["key"], Request.QueryString["istop"], Request.QueryString["starttime"], Request.QueryString["endtime"]); if (Status.SelectedIndex == 0) strWhere = "NoticeTitle like '%" + Request.QueryString["key"] + "%' and AddDate between '" + Request.QueryString["starttime"] + "' and '" + Request.QueryString["endtime"] + "'"; else strWhere = "NoticeTitle like '%" + Request.QueryString["key"] + "%' and IsTop=" + byte.Parse(Status.Value) + " and AddDate between '" + Request.QueryString["starttime"] + "' and '" + Request.QueryString["endtime"] + "'"; } else strWhere = "AddDate between '" + txtStartTime.Text + "' and '" + txtEndTime.Text + "'"; //这里用 notice.GetListByWhereDataTable(); 方法也可以，但IList效率要高 Repeater1.DataSource = notice.GetListByWhere(strWhere, "IsTop desc,AddDate desc", PageSize, CurrentPage, out Record); Repeater1.DataBind(); } catch { ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true); } } private void BindPager() { // 偏移量 int Step = 10; // 左页码 int LeftNum = 0; // 右页码 int RightNum = 0; // 获取当前请求的虚拟路径 string PageUrl = Request.FilePath; // 获取总的页数 int PageCount = (int)Math.Ceiling((double)(Record) / PageSize); // 如果当前页码小于等于偏移量，就把左页码设置为1 if (CurrentPage - Step < 1) LeftNum = 1; // 否则左页码=当前页码-偏移量 else LeftNum = CurrentPage - Step; // 如果总页码减去偏移量小于等于当前页码，就把右页码设置为总页码 if (CurrentPage + Step > PageCount) RightNum = PageCount; // 否则把右页码设置为当前页码+偏移量 else RightNum = CurrentPage + Step; System.Text.StringBuilder sb = new System.Text.StringBuilder(); // 循环生成页码 for (int i = LeftNum; i <= RightNum; i++) { // 如果是本页，字体变红，并且没有链接 if (i == CurrentPage) { // 注意这里的 margin-left 是让他们之间产生间隙，也可以用 代替 sb.Append("<span style='margin-left:3px;color:red'>"); sb.Append(i.ToString()); sb.Append("</span>"); } // 如果不是本页，字体颜色不变，链接到相应页码 else { sb.Append("<a style='margin-left:3px;' href='"); sb.Append(PageUrl); sb.Append("?page="); sb.Append(i.ToString()); // 查询条件 sb.Append(strUrl); sb.Append("'>"); sb.Append(i.ToString()); sb.Append("</a>"); } } // 如果当前页不是第一页，则左边出现“上一页”，注意这里是用 sb.Insert 方法在 sb 最前面插入 if (CurrentPage > 1) { sb.Insert(0, string.Format("<a href='{0}?page={1}{2}'>上一页 </a>", PageUrl, (CurrentPage - 1), strUrl)); } // 如果当前页不是最后一页，则出现“下一页” if (CurrentPage < PageCount) { sb.Append("<a href='"); sb.Append(PageUrl); sb.Append("?page="); sb.Append(CurrentPage + 1); sb.Append(strUrl); sb.Append("'> 下一页</a>"); } Label3.Text = string.Format("总记录数：<span style='color:red'>{0}</span> 总页数：<span style='color:red'>{1}</span> 当前页码：<span style='color:red'>{2}</span>", Record, PageCount, CurrentPage); Label4.Text = sb.ToString(); }

    // 此方法主要实现类似与搜搜网的分页 http://wenwen.soso.com/z/TopQuestion.e?sp=9 
    

private void BindSOSOPager() { // 获取当前请求的虚拟路径 string PageUrl = Request.FilePath; // 获取总的页数 int PageCount = (int)Math.Ceiling((double)(Record) / PageSize); System.Text.StringBuilder sb = new System.Text.StringBuilder(); if (Request.QueryString["page"] == null || Request.QueryString["page"] == "1") { if (PageCount > 1) { if (PageCount < 12) { for (int i = 1; i <= PageCount; i++) { if (i == 1) sb.Append("<span style='color:red; font-weight:bold'>　1</span>"); else sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); } } else { for (int i = 1; i <= PageCount; i++) { if (i < 11) { if (i == 1) sb.Append("<span style='color:red; font-weight:bold'>　1</span>"); else sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); } else if (i == PageCount) sb.Append(string.Format(" ... [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); } } sb.Append(string.Format(" <a href='{0}?page=2{1}'>下一页</a>", PageUrl, strUrl)); } } else if (Request.QueryString["page"] != null || Request.QueryString["page"] != "") { if (PageCount < 12) { if (CurrentPage == 1) { for (int i = 1; i <= PageCount; i++) { if (i == 1) sb.Append("<span style='color:red; font-weight:bold'>　1</span>"); else sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString())); } sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl)); } else if (CurrentPage == PageCount) { sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl)); for (int i = 1; i <= PageCount; i++) { if (i == PageCount) sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); else sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); } } else { sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl)); for (int i = 1; i <= PageCount; i++) { if (i == CurrentPage) sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); else sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); } sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl)); } } else { if (CurrentPage != 1) sb.Append(string.Format(" <a href='{0}?page={1}{2}'>上一页</a>", PageUrl, Convert.ToString(CurrentPage - 1), strUrl)); if (PageCount > 11 && CurrentPage > 6 && CurrentPage < PageCount - 5) { for (int i = 1; i <= PageCount; i++) { if (i == 1 && CurrentPage > 6 && PageCount > 11) sb.Append(string.Format(" <a href='{0}?page=1{1}'>[1]</a> ...", PageUrl, strUrl)); else if (i == PageCount && CurrentPage > 6 && PageCount > 11) sb.Append(string.Format(" ... <a href='{0}?page={1}{2}'>[{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString())); else if (i.ToString() == Request.QueryString["page"].ToString()) sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); else if (i < CurrentPage + 5 && i > CurrentPage - 5) sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); } } else if (PageCount > 11 && CurrentPage > 6 && CurrentPage >= PageCount - 5) { for (int i = 1; i <= PageCount; i++) { if (i == 1 && CurrentPage > 6 && PageCount > 11) sb.Append(string.Format(" <a href='{0}?page=1{1}'>[1]</a> ...", PageUrl, strUrl)); else if (CurrentPage == PageCount && i.ToString() == Request.QueryString["page"].ToString()) sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); else if (i.ToString() == Request.QueryString["page"].ToString()) sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); else if (CurrentPage > PageCount - 6 && i > PageCount - 10) sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); else if (i < CurrentPage + 5 && i > CurrentPage - 5) sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); } } else if (PageCount > 11 && CurrentPage <= 6 && CurrentPage < PageCount - 5) { for (int i = 1; i <= PageCount; i++) { if (i == 1 && CurrentPage == 1) sb.Append("<span style='color:red; font-weight:bold'>　1</span>"); else if (i.ToString() == Request.QueryString["page"].ToString()) sb.Append(string.Format(" <span style='color:red; font-weight:bold'>{0}</span>", i.ToString())); else if (i == PageCount) sb.Append(string.Format(" ... <a href='{0}?page={1}{2}'>[{3}]</a>", PageUrl, i.ToString(), strUrl, i.ToString())); else if (CurrentPage < 7 && i < 11) sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); else if (i < CurrentPage + 5 && i >= CurrentPage - 5) sb.Append(string.Format(" [<a href='{0}?page={1}{2}'>{3}</a>]", PageUrl, i.ToString(), strUrl, i.ToString())); } } if (CurrentPage != PageCount) sb.Append(string.Format(" <a href='{0}?page={1}{2}'>下一页</a>", PageUrl, Convert.ToString(CurrentPage + 1), strUrl)); } } Label1.Text = string.Format("总记录数：<span style='color:red'>{0}</span> 总页数：<span style='color:red'>{1}</span> 当前页码：<span style='color:red'>{2}</span>", Record, PageCount, CurrentPage); Label2.Text = sb.ToString(); } protected void btnSearch_Click(object sender, EventArgs e) { Response.Redirect("Notice.aspx?key=" + txtKey.Text + "&istop=" + Status.Value + "&starttime=" + txtStartTime.Text + "&endtime=" + txtEndTime.Text); } protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e) { try { notice.Delete(Convert.ToInt32(e.CommandArgument.ToString())); ClientScript.RegisterStartupScript(GetType(), "", "alert('删除成功！');", true); BindData(); BindSOSOPager(); BindPager(); } catch { ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true); } } protected void lbtDeleteAll_Click(object sender, EventArgs e) { try { for (int i = 0; i < Repeater1.Items.Count; i++) { if (((CheckBox)Repeater1.Items.FindControl("CheckBox1")).Checked == true) notice.Delete(Convert.ToInt32(((HiddenField)Repeater1.Items.FindControl("HiddenField1")).Value)); } ClientScript.RegisterStartupScript(GetType(), "", "alert('删除成功！');", true); BindData(); BindSOSOPager(); BindPager(); } catch { ClientScript.RegisterStartupScript(GetType(), "", "alert('出现错误！');", true); } } }

转载于:https://www.cnblogs.com/xiacy/archive/2011/09/05/2485718.html