<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>无标题页</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table style="border:1px soild #336699;">
<thead>
<tr>
<th>项目编号</th>
<th>项目名称</th>
<th>发布时间</th>
</tr>
</thead>
<tbody>
<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
<tr>
<td><%# Eval("ItemId") %></td>
<td><%# Eval("ItemName") %></td>
<td><%# Eval("Time") %></td>
</tr>
</ItemTemplate>
<AlternatingItemTemplate>
<tr>
<td><%# Eval("ItemId") %></td>
<td><%# Eval("ItemName") %></td>
<td><%# Eval("Time") %></td>
</tr>
</AlternatingItemTemplate>
</asp:Repeater>
</tbody>
</table>
<br />
<webdiyer:AspNetPager ID="AspNetPager1" runat="server" FirstPageText="首页" LastPageText="尾页"
NextPageText="下一页" PrevPageText="上一页" AlwaysShow="True" OnPageChanging="AspNetPager1_PageChanging">
</webdiyer:AspNetPager>
</div>
</form>
</body>
</html>
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bindRepeater1();
}
}
public void bindRepeater1()
{
int count = 0;
DataTable tb = this.getDatable(this.AspNetPager1.CurrentPageIndex, this.AspNetPager1.PageSize, string.Empty,ref count);
this.Repeater1.DataSource = tb;
this.Repeater1.DataBind();
this.AspNetPager1.RecordCount = count;
}
public DataTable getDatable(int pageIndex,int pageSize,string filter,ref int Count)
{
SqlParameter p = new SqlParameter("@rowCount",SqlDbType.Int);
p.Direction = ParameterDirection.Output;
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@tblName","Items"),
new SqlParameter("@strGetFields","ItemId,ItemName,Time"),
new SqlParameter("@fldName","ItemId"),
new SqlParameter("@PageSize",pageSize),
new SqlParameter("@PageIndex",pageIndex),
new SqlParameter("@OrderType","asc"),
new SqlParameter("@strWhere",filter),
p
};
DataTable tb = DBHelper.GetTable("pagination", CommandType.StoredProcedure, param);
Count = Convert.ToInt32(p.Value.ToString());
return tb;
}
protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
{
this.AspNetPager1.CurrentPageIndex = e.NewPageIndex;
bindRepeater1();
}
存储过程的代码如下：
create PROCEDURE [dbo].[pagination] @tblName varchar(255), -- 表名 @strGetFields varchar(1000), -- 需要返回的列 @fldName varchar(255), -- 排序的字段名 @PageSize int, -- 页尺寸 @PageIndex int, -- 页码 @OrderType varchar(4), -- 设置排序类型, 非 0 值则降序 @strWhere varchar(1500), -- 查询条件 (注意: 不要加 where) @rowCount int output --查询到的记录数 AS declare @strSQL varchar(5000) -- 主语句 declare @strTmp varchar(110) -- 临时变量 declare @strOrder varchar(400) -- 排序类型 declare @strCountTmp nvarchar(100) --记录数 declare @timediff datetime --耗时 begin select @timediff = Getdate() if @strWhere !='' set @strCountTmp = 'select @rowCount = count(*) from [' + @tblName + '] where '+@strWhere else set @strCountTmp = 'select @rowCount = count(*) from [' + @tblName + ']' exec sp_executesql @strCountTmp,N'@rowCount int out',@rowCount out --输出总记录数 if @OrderType != 'asc' begin set @strTmp = '<(select min' set @strOrder = ' order by ' + @fldName +' desc' --如果@OrderType不是0，就执行降序，这句很重要！ end else begin set @strTmp = '>(select max' set @strOrder = ' order by ' + @fldName +' asc' end if @PageIndex = 1 begin if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder else set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder --如果是第一页就执行以上代码，这样会加快执行速度 end else begin --以下代码赋予了@strSQL以真正执行的SQL代码 set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '(['+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'+ @strOrder if @strWhere != '' set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where [' + @fldName + ']' + @strTmp + '([' + @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' [' + @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder end end exec (@strSQL) select datediff(ms,@timediff,GetDate()) as runtime
