分页功能

[b]分页存储过程：[/b]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
--==============================================
-- Author: 白玉水堂
-- Create date: 2013-10-22 16：15
-- Description: 分页，用到了ROW_NUMBER()
-- =============================================
ALTER PROCEDURE [dbo].[proc_FengYe]
@tblName varchar(255), -- 表名
@strFields varchar(1000) = '*', -- 需要返回的列，默认*
@strOrder varchar(255)='', -- 排序的字段名，必填
@strOrderType varchar(10)='ASC', -- 排序的方式,默认ASC
@PageSize int = 10, -- 页尺寸,默认10
@PageIndex int = 1, -- 页码,默认1
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS

declare @strSQL varchar(5000)

if @strWhere !=''
set @strWhere=' where '+@strWhere

set @strSQL=
'SELECT * FROM ('+
'SELECT ROW_NUMBER() OVER (ORDER BY '+@strOrder+' '+@strOrderType+') AS pos,'+@strFields+' '+
'FROM '+@tblName+' '+@strWhere+
') AS sp WHERE pos BETWEEN '+str((@PageIndex-1)*@PageSize+1)+' AND '+str(@PageIndex*@PageSize)

exec (@strSQL)


[b]DAL层[/b]
#region 分页选择新闻
/// <summary>
/// 分页选择新闻
/// </summary>
/// <param name="pagesize">页大小</param>
/// <param name="pageindex">页索引</param>
/// <param name="cond">条件，不用加where</param>
/// <returns></returns>
public DataTable
Select(int pagesize,int pageindex,string cond)
{
DataTable dt = new DataTable();
string cmdText = "proc_FengYe";
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter("@tblName","news" as object),
new SqlParameter("@strFields","id,title,createTime,traffic " as object),
new SqlParameter("@strOrder","id" as object),
new SqlParameter("@strOrderType","desc" as object),
new SqlParameter("@PageSize",pagesize as object),
new SqlParameter("@PageIndex",pageindex as object),
new SqlParameter("@strWhere",cond as object)
};
dt = sqlhelper.ExecuteQuery(cmdText, paras, CommandType.StoredProcedure);
return dt;
}
#endregion

#region 根据条件计算新闻记录数
/// <summary>
/// 根据条件计算新闻记录数
/// </summary>
/// <param name="cond">条件，不用加where</param>
/// <returns></returns>
public int SelectAllNewsCount(string cond)
{
string cmdText = "select count(*) from news ";
if (!string.IsNullOrEmpty(cond))
{
cmdText =string.Format(cmdText+ " where " + cond);
}
return int.Parse(sqlhelper.ExecuteScalar(cmdText));
}
#endregion


Web层：
Load事件
//计算所属板块为新闻的总记录数
string cond = "caId=1";
newslistAspNetPager.RecordCount = nm.SelectAllNewsCount(cond);
BindNews();

//绑定所属板块为新闻的列表
private void BindNews()
{
int pagesize = newslistAspNetPager.PageSize;
int pageindex = newslistAspNetPager.CurrentPageIndex;
string cond = "caId=1";
repnewslist.DataSource = nm.Select(pagesize, pageindex, cond);
repnewslist.DataBind();
}

//分页事件
protected void newslistAspNetPager_PageChanged(object sender, EventArgs e)
{
BindNews();
}

web前端：
<asp:Repeater ID="repnewslist" runat="server">
<ItemTemplate>
<img src="images/dian.jpg" alt=""/> <a href='News.aspx?newsid=<%# Eval("id") %>' title='<%# Eval("title") %>' target="_blank"><%# StringTruncat(Eval("title").ToString(), 30, "...")%></a>  <span id="futi">发布时间(<%# Eval("createTime") %>) 浏览(<%#Eval("traffic")%>) </span><br/>
</ItemTemplate>
</asp:Repeater>
<p> </p>
<webdiyer:AspNetPager ID="newslistAspNetPager" runat="server"
onpagechanged="newslistAspNetPager_PageChanged" PageSize="13"
CssClass="anpager" CurrentPageButtonClass="cpb"
CustomInfoHTML="共%PageCount%页，每页%PageSize%条" FirstPageText="首页" LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页"
ShowCustomInfoSection="Left" SubmitButtonText="跳转" CustomInfoSectionWidth="20%" AlwaysShow="True">
</webdiyer:AspNetPager>