首页 > 代码库 > 三层架构下AspNetPager分页实现
三层架构下AspNetPager分页实现
首先是建一张表:
CREATE TABLE [dbo].[UserInfo] (
[uId] INT IDENTITY (1, 1) NOT NULL,
[uName] NVARCHAR (50) NULL,
[uPassWord] NVARCHAR (50) NULL,
[uEmail] NVARCHAR (50) NULL,
[uPic] NVARCHAR (50) NULL,
PRIMARY KEY CLUSTERED ([uId] ASC)
);
第一个是分页的存储过程:
create procedure [dbo].[GetPagedOrders]
@startIndex int,
@endIndex int
AS
with SClass as( //SClass 是一个临时表
select row_number() over(order by [uId]) as row,
[uId],uName,uPassWord,uEmail,uPic
from UserInfo )
select [uId],uName,uPassWord,uEmail,uPic
from SClass
where row between @startIndex and @endIndex
return 0
接下来写一个获取数据数量的存储过程:
create procedure [dbo].[GetOrderNumber]
AS
select count(*) from UserInfo
RETURN 0
好,接下来下DAL层的DBHelper.cs
/// <summary>
/// 执行带参数的查询SQL语句或存储过程
/// </summary>
/// <param name="cmdText">查询SQL语句或存储过程</param>
/// <param name="paras">参数集合</param>
/// <param name="ct">命令类型</param>
/// <returns></returns>
public DataTable ExecuteQuery(string cmdText, SqlParameter[] paras, CommandType ct)
{
DataTable dt = new DataTable();
cmd = new SqlCommand(cmdText, GetConn());
cmd.CommandType = ct;
cmd.Parameters.AddRange(paras);
using (sdr = cmd.ExecuteReader
(CommandBehavior.CloseConnection))
{
dt.Load(sdr);
}
return dt;
}
public object ExecuteScalar(string cmdText,CommandType ct)
{
cmd = new SqlCommand(cmdText, GetConn());
cmd.CommandType = ct;
object val = cmd.ExecuteScalar();
return val;
}
DAL层的UserInfoDao.cs:
private DBHelper db = null;
public UserInfoDao()
{
db = new DBHelper();
}
public DataTable Bind(string cmdText, int startIndex, int endIndex)
{
DataTable dt = new DataTable();
SqlParameter[] paras = new SqlParameter[]{
new SqlParameter("@startIndex",startIndex),
new SqlParameter("@endIndex",endIndex)
};
dt = db.ExecuteQuery(cmdText, paras, CommandType.StoredProcedure);
return dt;
}
public int getExecuteScalar(string cmdText)
{
int order = (int)db.ExecuteScalar(cmdText, CommandType.StoredProcedure);
return order;
}
接下来是BLL层的UserInfoManager.cs:
private UserInfoDao udao = null;
public UserInfoManager()
{
udao = new UserInfoDao();
}
public DataTable Bind(string cmdText, int startIndex, int endIndex)
{
return udao.Bind(cmdText, startIndex, endIndex);
}
public int getExecuteScalar(string cmdText)
{
return udao.getExecuteScalar(cmdText);
}
最后就是在页面调用了:
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Bind();
}
}
private void Bind()
{
int totalOrders = new UserInfoManager().getExecuteScalar("GetOrderNumber");
AspNetPager1.RecordCount = totalOrders;
Repeater1.DataSource = new UserInfoManager().Bind("GetPagedOrders", AspNetPager1.StartRecordIndex, AspNetPager1.EndRecordIndex);
Repeater1.DataBind();
}
protected void AspNetPager1_PageChanged(object sender, EventArgs e)
{
Bind();
}
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。