AspNetPager 高效分页

java-mans

浏览: 11386510 次

最近访客更多访客>>

morelily

devcang

serisboy

chjinniu

博主相关

博客

微博

相册

留言

关于我

文章分类

全部博客 (15568)

社区版块

存档分类

2012-08 ( 1467)
2012-07 ( 1872)
2012-06 ( 1125)
更多存档...

今晚手痒，写了点代码，温习了一下AspNetPager 这个免费的分页控件

aspx页面部分代码：

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="WebForm1.aspx.cs" Inherits="LearningAspNet.无刷新分页.WebForm1" %> <%@ Register assembly="AspNetPager" namespace="Wuqi.Webdiyer" tagprefix="webdiyer" %> <!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 runat="server"> <title>AspNetPager+存储过程完成分页</title> </head> <body> <form id="form1" runat="server"> <div> <asp:Repeater ID="Repeater1" runat="server" onitemcommand="Repeater1_ItemCommand" onitemdatabound="Repeater1_ItemDataBound" > <HeaderTemplate> <table><tr> <td>学生编号</td> <td>学生姓名</td> <td>学生性别</td> <td align="center">操作</td> </tr> </HeaderTemplate> <ItemTemplate> <tr> <td><%#Eval("sid") %></td> <td><%#Eval("sname") %></td> <td><%#Eval("ssex") %></td> <td><asp:Button ID="Button1" runat="server" Text="Button" CommandName="btn" CommandArgument='<%#Eval("sid") %>' /> </td></tr> </ItemTemplate> <AlternatingItemTemplate > <tr style="background-color:Aqua" mce_style="background-color:Aqua"> <td><%#Eval("sid") %></td> <td><%#Eval("sname") %></td> <td><%#Eval("ssex") %></td> <td> <asp:Button ID="Button1" runat="server" Text="Button" CommandName="btn" CommandArgument='<%#Eval("sid") %>'/> </td> </tr> </AlternatingItemTemplate> <FooterTemplate> </table> </FooterTemplate> </asp:Repeater> <asp:Label ID="Label1" runat="server" Text=""></asp:Label> </div> <webdiyer:AspNetPager ID="AspNetPager1" runat="server" OnPageChanged="AspNetPager1_PageChanged"> </webdiyer:AspNetPager> </form> </body> </html>

.cs后台部分代码：

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Configuration; namespace LearningAspNet.无刷新分页 { public partial class WebForm1 : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["studentConnectionString"].ToString(); protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { bind(); } } /// <summary> /// 绑定数据 /// </summary> private void bind() { this.AspNetPager1.RecordCount = getRecordCount();//总记录数 int curPage = this.AspNetPager1.CurrentPageIndex;//当前页的索引 int count = this.AspNetPager1.PageSize=3;//每页显示条数 Repeater1.DataSource = getData(curPage, count, "stuinfo", "sid"); Repeater1.DataBind(); } /// <summary> /// 获取数据源 /// </summary> /// <param name="curPage">当前页</param> /// <param name="count">每页显示的条数</param> /// <param name="tblName">表名</param> /// <param name="colum">主键（标识）</param> /// <returns></returns> private DataSet getData(int curPage,int count,string tblName,string colum) { SqlConnection conn = new SqlConnection(connStr); try { conn.Open(); DataSet ds = new DataSet(); SqlCommand cmd = new SqlCommand(); SqlDataAdapter da = new SqlDataAdapter(cmd); cmd.Connection = conn; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = "page"; //给存储过程添加参数 cmd.Parameters.Add(new SqlParameter("@currentPage", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@count", SqlDbType.Int)); cmd.Parameters.Add(new SqlParameter("@tableName", SqlDbType.VarChar)); cmd.Parameters.Add(new SqlParameter("@column", SqlDbType.VarChar)); //给参数赋值 da.SelectCommand.Parameters["@currentPage"].Value = curPage; da.SelectCommand.Parameters["@count"].Value = count; da.SelectCommand.Parameters["@tableName"].Value = tblName; da.SelectCommand.Parameters["@column"].Value = colum; // da.SelectCommand = cmd; da.Fill(ds); return ds; } catch (Exception) { throw; } finally { conn.Close(); } } /// <summary> /// 查询总记录数 /// </summary> /// <returns></returns> private int getRecordCount() { int i = 0; SqlConnection conn = new SqlConnection(connStr); conn.Open(); string sql = "select count(*) from stuinfo";//查询处总记录数 SqlDataAdapter da = new SqlDataAdapter(sql,conn); object obj = da.SelectCommand.ExecuteScalar();//查询结果的第一行第一列 --总记录条数 i = int.Parse(obj.ToString());//总记录条数 conn.Close(); return i; } /// <summary> /// 翻页事件 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void AspNetPager1_PageChanged(object sender, EventArgs e) { bind(); } /// <summary> /// DataList控件中点击任意一列的按钮会发生 /// </summary> /// <param name="source"></param> /// <param name="e"></param> protected void Repeater1_ItemCommand(object source, RepeaterCommandEventArgs e) { if (e.CommandName=="btn") { Label1.Text ="我的学号是"+ e.CommandArgument.ToString(); Label1.Style["color"] = "red"; } } /// <summary> /// 每一项绑定的时候 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> protected void Repeater1_ItemDataBound(object sender, RepeaterItemEventArgs e) { if (e.Item.ItemType==ListItemType.Item) { Button btn = (Button)e.Item.FindControl("Button1");//找到按钮 btn.Text = "汇报"; }else if(e.Item.ItemType==ListItemType.AlternatingItem) { Button btn = (Button)e.Item.FindControl("Button1"); btn.Text = "汇报"; } } } }

数据库部分：分页的存储过程：

--存储过程实现灵活的分页
if object_id('page','p') is not null
drop proc page
go
create proc page
@currentPage int=1,--要查询第几页
@count int=10,--每页显示几条
@tableName varchar(20),--表名
@column varchar(20)--列名
as
declare @sql nvarchar(1000)
set @sql = N'select top '+ cast(@count as nvarchar(3))
set @sql=@sql+N' * from '+@tableName+' where '+@column+' not in('
set @sql=@sql+N'select top '+cast(((@currentPage-1)*@count) as nvarchar(3))
set @sql=@sql+@column+N' from '+@tableName+')'