当一个项目repeater分页多的时候使用公共的存储过程实现分页，是不错的选择
ALTER PROC [dbo].[P_Common_proc] -- 通用分页存储过程
@TableName varchar (5000), --表名
@Fields varchar (5000), --字段名 (全部字段为*)
@OrderField varchar (5000), --排序字段( 必须!支持多字段 )
@SqlWhere varchar (5000), --条件语句( 不用加where)
@PageSize int , --每页多少条记录
@PageIndex int = 1 , --指定当前为第几页
@RecordCount int output --返回总条数
as
begin
Begin Tran --开始事务
Declare @sql nvarchar(4000);
Declare @PageCount int;
--计算总记录数
if (@SqlWhere <>'' AND @sqlWhere IS NOT NULL)
BEGIN
set @sql = 'select @totalRecord = count(*) from ' + @TableName +' where 1=1 ' + @sqlWhere
END
ELSE
set @sql = 'select @totalRecord = count(*) from ' + @TableName
EXEC sp_executesql @sql, N'@totalRecord int OUTPUT',@RecordCount OUTPUT--计算总记录数
PRINT @sql;
--计算总页数
select @PageCount =CEILING(CONVERT(FLOAT,@RecordCount)/CONVERT(FLOAT,@PageSize) );
if (@SqlWhere <>'' AND @sqlWhere IS NOT NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName +' where 1=1 ' + @SqlWhere
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @OrderField + ') as rowId,' + @Fields + ' from ' + @TableName
--处理页数超出范围情况
if @PageIndex <=0
Set @pageIndex = 1
if @pageIndex >@RecordCount
Set @pageIndex = @RecordCount
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex- 1)*@PageSize
set @EndRecord = @pageIndex* @pageSize
--继续合成sql 语句
set @Sql = @Sql + ') as a where rowId > ' + Convert(varchar (50), @StartRecord) + ' and rowId <=' + Convert(varchar (50), @EndRecord)
EXEC sp_executesql @Sql
PRINT @sql;
---------------------------------------------------
If @@Error <> 0
Begin
RollBack Tran
Return - 1
End
Else
Begin
Commit Tran
Return @RecordCount ---返回记录总数
End
END这是一个公共的存储过程的分页，在.net开发中，Dal是这样调用的。。
/// <summary>
/// 查询承运单
/// </summary>
/// <param name="Carriers"></param>
/// <returns></returns>
public List<CarriersModel> GetCarriersList(CarriersModel Carriers, int PageSize, int PageIndex, out int RecordCount)
{
//存储过程名字
string ProcName = "P_Common_proc";
//表名
string TableName = "dbo.Carriers";
//字段
string Fields = "*";
//排序字段
string OrderField = "CarriersID";
//条件
string SqlWhere = "";
SqlParameter[] param =
{
new SqlParameter("@TableName",SqlDbType.VarChar,5000),
new SqlParameter("@Fields",SqlDbType.VarChar,5000),
new SqlParameter("@OrderField",SqlDbType.VarChar,5000),
new SqlParameter("@SqlWhere",SqlDbType.VarChar,5000),
new SqlParameter("@PageSize",SqlDbType.Int),
new SqlParameter("@PageIndex",SqlDbType.Int),
new SqlParameter("@RecordCount",SqlDbType.Int)
};
List<CarriersModel> CarriersList = new List<CarriersModel>();
if (Carriers.CarriersID != 0)
{
SqlWhere += $" and CarriersID={Carriers.CarriersID}";
}
if (!string.IsNullOrWhiteSpace(Carriers.ReceiveLinkman))
{
SqlWhere += $" and ReceiveLinkman like '%{Carriers.ReceiveLinkman}%'";
}
if (!string.IsNullOrWhiteSpace(Carriers.SendLinkman))
{
SqlWhere += $" and SendLinkman like '%{Carriers.SendLinkman}%'";
}
if (!string.IsNullOrWhiteSpace(Carriers.LeaverDate))
{
SqlWhere += $" and LeaverDate>{Carriers.LeaverDate}";
}
if (!string.IsNullOrWhiteSpace(Carriers.LeaverDateEnd))
{
SqlWhere += $" and LeaverDate>{Carriers.LeaverDateEnd}";
}
param[0].Value = TableName;
param[1].Value = Fields;
param[2].Value = OrderField;
param[3].Value = SqlWhere;
param[4].Value = PageSize;
param[5].Value = PageIndex;
param[6].Direction = ParameterDirection.Output;
using (SqlDataReader sdr = DBHelper.ExecuteReaderProc(ProcName, param))
{
if (sdr.HasRows)
{
while (sdr.Read())
{
CarriersModel Carrier = new CarriersModel();
Carrier.CarriersID = Convert.ToInt32(sdr["CarriersID"]);
Carrier.SendLinkman = sdr["SendLinkman"].ToString();
Carrier.SendCompany = sdr["SendCompany"].ToString();
Carrier.ReceiveLinkman = sdr["ReceiveLinkman"].ToString();
Carrier.ReceiveCompany = sdr["ReceiveCompany"].ToString();
Carrier.LeaverDate = sdr["LeaverDate"].ToString();
Carrier.CTotalCost = Convert.ToDouble(sdr["TotalCost"]);
Carrier.FinishedState = Convert.ToInt32(sdr["FinishedState"]);
CarriersList.Add(Carrier);
}
sdr.Close();
}
}
RecordCount = Convert.ToInt32(param[6].Value);
return CarriersList;
}然后只需要在UI层里面传入
@TableName varchar (5000), --表名
@Fields varchar (5000), --字段名 (全部字段为*)
@OrderField varchar (5000), --排序字段( 必须!支持多字段 )
@SqlWhere varchar (5000), --条件语句( 不用加where)
@PageSize int , --每页多少条记录
@PageIndex int = 1 , --指定当前为第几页
@RecordCount int output --返回总条数参数即可。
需要注意的是：在UI层调用的时候
private void BindRptCarrierList()
{
CarriersModel carrier = new CarriersModel();
int cid;
int.TryParse(txtCarrierId.Text, out cid);
carrier.CarriersID = cid;
carrier.LeaverDate = txtLeaverDate.Text;
carrier.LeaverDateEnd = txtLeaverDateEnd.Text;
carrier.ReceiveLinkman = txtReceiveLinkman.Text;
carrier.SendLinkman = txtSendLinkman.Text;
CarriersBLL carrierbll = new CarriersBLL();
int PageSize = AspNetPagerTool.PageSize;
int PageIndex = AspNetPagerTool.CurrentPageIndex;
int RecordCount = 0;//注意接收总页数的返回值
rptCarriersList.DataSource = carrierbll.GetCarriersList(carrier, PageSize, PageIndex, out RecordCount);//out
AspNetPagerTool.RecordCount = RecordCount;
rptCarriersList.DataBind();
}