存储过程：
-- ============================================= -- Author: -- Create date: -- Description: 分页 --Update Date: --增加了默认排序规则，根据主键升序（防止在视图查询中乱号） -- ============================================= ALTER PROCEDURE [dbo].[getdatabyPageIndex] @tablename nvarchar(200), @columns nvarchar(500)='*', @condition nvarchar(200)='', @pagesize int=10, @pageindex int=0, @pk nvarchar(30), @total int output, --统计总共的条数 @orderculumn nvarchar(50)=@pk, @isasc nvarchar(10)='desc' AS BEGIN DECLARE @sql nvarchar(2000) SET @sql='select top '+cast(@pagesize AS nvarchar(10))+' '+@columns+' from '+@tablename+' where '+ @pk+' not in (select top '+cast((@pagesize*@pageindex) AS nvarchar(10))+ ' '+@pk+' from '+@tablename +' where 1=1 '+@condition+' order by '+@orderculumn+' '+@isasc+')'+@condition +' order by '+@orderculumn+' '+@isasc PRINT @sql EXEC(@sql) DECLARE @sql2 nvarchar(2000) SET @sql2='SELECT @total1 = count(*) FROM '+ @tablename+' WHERE 1=1 '+ @condition EXEC sp_executesql @sql2,N'@total1 int output',@total output END
.cs：
/// <summary> /// 分页功能 /// </summary> /// <param name="tablename">表名</param> /// <param name="columns">列名</param> /// <param name="condition">条件,不需要带where</param> /// <param name="pagesize">每页显示条数</param> /// <param name="pageindex">页码</param> /// <param name="pk">主键</param> /// <returns>DataTable</returns> public DataTable getdatabyPageIndex(string tablename, string columns, string condition, int pagesize, int pageindex, string pk,out int totalcount,string ordercolumn,string isasc) { string order = ""; if (ordercolumn == null) { order = pk; } string asc = ""; if (isasc == null) { isasc = "desc"; } SqlParameter[] pars = new SqlParameter[]{ new SqlParameter("@tablename",tablename), new SqlParameter("@columns",columns), new SqlParameter("@condition",condition), new SqlParameter("@pagesize",pagesize), new SqlParameter("@pageindex",pageindex), new SqlParameter("@pk",pk), new SqlParameter("@total",SqlDbType.Int), new SqlParameter("@orderculumn",ordercolumn), new SqlParameter("@isasc",isasc) }; pars[6].Direction = ParameterDirection.Output; DataTable dt= db.ExcuteSelectReturnDataTable("sp_getdatabyPageIndex", CommandType.StoredProcedure, pars); totalcount= int.Parse(pars[6].Value.ToString()); return dt; } /// <summary> /// 执行一个Select语句或者相应的存储过程实现返回数据集合DataSet /// </summary> /// <param name="SelectStr">执行一个Select语句或者相应的存储过程</param> /// <param name="type">指定命令类型</param> /// <param name="pars">相应参数集合</param> /// <returns>DataSet</returns> public DataSet ExcuteSelectReturnDataSet(string SelectStr, CommandType type, SqlParameter[] pars) { DataSet ds = new DataSet(); SqlConnection conn = new SqlConnection(ConnString); SqlDataAdapter sda = new SqlDataAdapter(SelectStr, conn); if (pars != null && pars.Length > 0) { foreach (SqlParameter p in pars) { sda.SelectCommand.Parameters.Add(p); } } sda.SelectCommand.CommandType = type; sda.Fill(ds); return ds; }
.aspx：
<style type="text/css"> .pages { color: #666; 100%; height:25px;} .pages a, .pages .cpb { text-decoration:none; padding: 0 5px; border: 1px solid #ddd; background: #ffff;margin:0 2px; font-size:12px; color:#000; height:20px} .pages a:hover { background-color: #2F7EAE; color:#fff;border:1px solid #2F7EAE; text-decoration:none;} .pages .cpb { font-weight: bold; color: #fff; background: #2F7EAE; border:1px solid #2F7EAE; height:20px} </style> <asp:HiddenField ID="hidden" runat="server" Value=" " /> <asp:Repeater ID="rptProlist" runat="server" onitemdatabound="rptProlist_ItemDataBound"> <HeaderTemplate> <table border="0" class="infolist" cellpadding="0" cellspacing="0"> <thead> <tr> <th width="4%">序号</th> <th>项目名称</th> <th width="10%">开始日期</th> <th width="10%">预计结束日期</th> <th width="10%">项目状态</th> <th width="7%">创建人</th> <th width="7%">负责人</th> <th width="20%">参与人</th> <th style="border-right:1px solid #999999;" width="10%">实际结束日期</th> </tr> </thead> <tbody> </HeaderTemplate> <ItemTemplate> <tr> <td><%#Eval("ProjectID") %></td> <td><a href="projectdetails.aspx?id=<%#Eval("ProjectID") %>"><%#Eval("ProjectName") %></a></td> <td><%#Eval("StartDate","{0:yyyy-MM-dd}")%></td> <td><%#Eval("ExpectedEndDate","{0:yyyy-MM-dd}")%></td> <td><%#Eval("ProjectState")%></td> <td><%#returnUserRealName(Eval("ProjectCreater").ToString())%></td> <td><%#returnUserRealName(Eval("ProjectPrincipal").ToString())%></td> <td><asp:Label ID="lbPlayers" runat="server" Text='<%#Eval("ProjectPlayers") %>'></asp:Label></td> <td style="border-right:1px solid #999999;"> <%#Eval("EndDate","{0:yyyy-MM-dd}")%> </td> </tr> </ItemTemplate> <FooterTemplate> </tbody> </table> </FooterTemplate> </asp:Repeater> <webdiyer:AspNetPager ID="AspNetPager1" runat="server" firstpagetext="首页" lastpagetext="尾页" nextpagetext="下一页" numericbuttoncount="5" pageindexboxtype="DropDownList" prevpagetext="上一页" showcustominfosection="Left" showpageindexbox="Always" submitbuttontext="Go" textafterpageindexbox="页" textbeforepageindexbox="转到" AlwaysShow="True" CustomInfoHTML="第%CurrentPageIndex%/%PageCount%页，每页%PageSize%条，共%RecordCount%条信息" LayoutType="Table" CssClass="pages" CustomInfoClass="paginator" CurrentPageButtonClass="cpb" Height="30px" CustomInfoSectionWidth="" Wrap="False" OnPageChanging="AspNetPager1_PageChanging" Width="660px" onpagechanged="AspNetPager1_PageChanged"> </webdiyer:AspNetPager>
.aspx.cs：
//string condition = " "; //每页条数 int pagesize = 15; //总共条数 int recordCount = 0; //第几页 int pageindex = 0; public void getProlist() { string condition = hidden.Value; rptProlist.DataSource = new PM.BLL.tb_Project().getdatabyPageIndex("tb_Project", "*", condition, pagesize, pageindex, "ProjectID", out recordCount, null, null); rptProlist.DataBind(); AspNetPager1.RecordCount = recordCount; AspNetPager1.PageSize = pagesize; } /// <summary> /// 搜索 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void btnSearch_Click(object sender, EventArgs e) { string proName = txtProName.Text.Trim(); string proState = ddlState.SelectedItem.Text; int proPrincipal = Convert.ToInt32(ddlPrincipal.SelectedValue); int proCreater = Convert.ToInt32(ddlCreater.SelectedValue); string proNotes = txtNotes.Text; StringBuilder sb = new StringBuilder(); if (!string.IsNullOrEmpty(proName)) { sb.Append(" and ProjectName like '%" + proName + "%'"); } if (ddlState.SelectedValue != "-1") { sb.Append(" and ProjectState='" + proState + "'"); } if (proPrincipal != -1) { sb.Append(" and ProjectPrincipal='" + proPrincipal + "'"); } if (proCreater != -1) { sb.Append(" and ProjectCreater='" + proCreater + "'"); } if (!string.IsNullOrEmpty(txtStartDate.Text.Trim()) && !string.IsNullOrEmpty(txtEndDate.Text.Trim())) { sb.Append(" and (StartDate >= '" + Convert.ToDateTime(txtStartDate.Text).ToString("yyyy-MM-dd") + "' and StartDate <= '" + Convert.ToDateTime(txtEndDate.Text).ToString("yyyy-MM-dd") + "')"); } if (!string.IsNullOrEmpty(proNotes)) { sb.Append(" and ProjectNotes like '%" + proNotes + "%' "); } hidden.Value = Convert.ToString(sb); string condition = Convert.ToString(sb); pageindex = 0; getProlist(); } protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e) { AspNetPager1.CurrentPageIndex = e.NewPageIndex; getProlist(); } protected void AspNetPager1_PageChanged(object sender, EventArgs e) { pageindex = this.AspNetPager1.CurrentPageIndex - 1; getProlist(); }
把搜索条件存储在隐藏域中