//int PageCount=0;
//int RecordCount=0;
AssetQr_bll bll = new AssetQr_bll();
//DataSet ds = bll.GetDatasetPage("eam_assetin","*","","","id",AspNetPager1.PageSize,AspNetPager1.CurrentPageIndex,out PageCount, out RecordCount);
//DataSet ds = bll.GetDatasetPage("dbo.eam_assetin a INNER JOIN dbo.eam_asset b ON a.assetid = b.ID", "a.id,a.assetid,a.assetnum,a.POnumber,b.assetcode,b.assetmodelid,b.SN,a.city,a.place,a.reason,a.remark", "", "", "a.id", AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, out PageCount, out RecordCount);
string sql = "SELECT a.id,a.assetnum,a.POnumber,b.assetcode,b.assetmodelid,b.SN,a.city,a.place,a.reason,a.remark FROM dbo.eam_assetin a INNER JOIN dbo.eam_asset b ON a.assetid = b.ID";
DataSet ds = bll.GetDatasetPage(sql, AspNetPager1.PageSize, AspNetPager1.CurrentPageIndex, out PageCount, out RecordCount);
AspNetPager1.RecordCount = RecordCount;
RptList.DataSource = ds.Tables[1];
RptList.DataBind();
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="strsql"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="PageCount"></param>
/// <param name="RecordCount"></param>
/// <returns></returns>
public DataSet GetDatasetPage(string strsql, int pageSize, int pageIndex, out int PageCount, out int RecordCount)
{
Database db = DatabaseFactory.CreateDatabase("SqlConnectionString");
DataSet ds = new DataSet();
DbCommand dbCommand = db.GetStoredProcCommand("PageDataSql");
db.AddInParameter(dbCommand, "@Sql", DbType.AnsiString, strsql);
db.AddInParameter(dbCommand, "@PageSize", DbType.AnsiString, pageSize);
db.AddInParameter(dbCommand, "@PageIndex", DbType.AnsiString, pageIndex);
db.AddOutParameter(dbCommand, "@PageCount", DbType.Int32, 4);
db.AddOutParameter(dbCommand, "@RecordCount", DbType.Int32, 4);
ds = db.ExecuteDataSet(dbCommand);
PageCount = Convert.ToInt32(db.GetParameterValue(dbCommand, "@PageCount"));
RecordCount = Convert.ToInt32(db.GetParameterValue(dbCommand, "@RecordCount"));
return ds;
}
/// <summary>
/// 获取分页数据
/// </summary>
/// <param name="tablename"></param>
/// <param name="returnfield"></param>
/// <param name="strWhere"></param>
/// <param name="groupBy"></param>
/// <param name="orderBy"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="totalPage"></param>
/// <param name="totalRows"></param>
/// <returns></returns>
public DataSet GetDatasetPage(string tablename, string returnfield, string strWhere, string groupBy, string orderBy, int pageSize, int pageIndex, out int totalPage, out int totalRows)
{
Database db = DatabaseFactory.CreateDatabase("SqlConnectionString");
DataSet ds = new DataSet();
DbCommand dbCommand = db.GetStoredProcCommand("PageData");
db.AddInParameter(dbCommand, "@TableNames", DbType.AnsiString, tablename);
db.AddInParameter(dbCommand, "@FieldStr", DbType.AnsiString, returnfield);
db.AddInParameter(dbCommand, "@SqlWhere", DbType.AnsiString, strWhere);
db.AddInParameter(dbCommand, "@GroupBy", DbType.AnsiString, groupBy);
db.AddInParameter(dbCommand, "@OrderBy", DbType.AnsiString, orderBy);
db.AddInParameter(dbCommand, "@PageSize", DbType.AnsiString, pageSize);
db.AddInParameter(dbCommand, "@PageIndex", DbType.AnsiString, pageIndex);
db.AddOutParameter(dbCommand, "@TotalPage", DbType.Int32, 4);
db.AddOutParameter(dbCommand, "@TotalRecord", DbType.Int32, 4);
ds = db.ExecuteDataSet(dbCommand);
totalPage = Convert.ToInt32(db.GetParameterValue(dbCommand, "@TotalPage"));
totalRows = Convert.ToInt32(db.GetParameterValue(dbCommand, "@TotalRecord"));
return ds;
}
通用A:
USE [AlipayAMS]
GO
/****** Object: StoredProcedure [dbo].[PageData] Script Date: 01/12/2012 17:42:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PageData]
/*
****************************************************************************************************
*** 用于SqlServer2005(及以上)的高效分页存储过程(支持多字段任意排序,不要求排序字段唯一) ***
****************************************************************************************************
*/
@TableNames varchar(200), --表名(支持多表)
@FieldStr varchar(4000), --字段名(全部字段为*)
@SqlWhere varchar(4000), --条件语句(不用加where)
@GroupBy varchar(4000), --Group语句(不用加Group By)
@OrderBy varchar(4000), --排序字段(必须!支持多字段,不用加Order By)
@PageSize int, --每页多少条记录
@PageIndex int, --指定当前为第几页
@TotalPage int output, --返回总页数
@TotalRecord int output --返回总条数
--with encryption --加密时使用
As
Begin
-- Begin Transaction
If @SqlWhere = ''
set @SqlWhere = null
If @GroupBy = ''
set @GroupBy = null
Declare @Sql nvarchar(4000)
--计算总记录数
set @Sql = 'select @TotalRecord = count(*) from ' + @TableNames
If (@SqlWhere !='' or @SqlWhere is not NULL)
set @Sql = @Sql + ' where ' + @SqlWhere
Exec sp_executesql @Sql,N'@TotalRecord int output',@TotalRecord output --计算总记录数
--计算总页数
set @TotalPage=CEILING((@TotalRecord+0.0)/@PageSize)
--处理页数超出范围情况
if @PageIndex <= 0
Set @PageIndex = 1
if @PageIndex > @TotalPage
Set @PageIndex = @TotalPage
--处理开始点和结束点
Declare @StartRecord int
Declare @EndRecord int
set @StartRecord = (@pageIndex-1)*@PageSize + 1
set @EndRecord = @StartRecord + @PageSize - 1
--合成sql条件
Declare @TempStr varchar(4000)
If (@SqlWhere != '' or @SqlWhere is not NULL)
set @TempStr = ' where ' + @SqlWhere
If (@GroupBy != '' or @GroupBy is not NULL)
set @TempStr = @TempStr + ' Group By ' + @GroupBy
--如果是第一页
If (@PageIndex = 1)
Begin
set @Sql = 'select top ' + Convert(varchar(50),@PageSize) + ' row_number() over(order by ' + @OrderBy + ') as rowId,' + @FieldStr + ' from ' + @TableNames
If (@TempStr !='' or @TempStr is not NULL)
set @Sql = @Sql + ' ' + @TempStr
End
Else
Begin
set @Sql = 'select row_number() over(order by ' + @OrderBy + ') as rowId,' + @FieldStr + ' from ' + @TableNames
If (@TempStr !='' or @TempStr is not NULL)
set @Sql = @Sql + ' ' + @TempStr
set @Sql = 'Select * from (' + @Sql + ') as TempTable where rowId between ' + Convert(varchar(50),@StartRecord) + ' and ' + Convert(varchar(50),@EndRecord)
End
--执行查询
Exec(@Sql)
-- If @@Error <> 0
-- Begin
-- RollBack Transaction
-- Return -1
-- End
-- Else
-- Commit Transaction
End
二通用B:
USE [AlipayAMS]
GO
/****** Object: StoredProcedure [dbo].[PageDataSql] Script Date: 01/12/2012 17:53:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PageDataSql]
@Sql
ntext, --要执行的sql语句
@PageIndex int=1, --要显示的页码
@PageSize int=10, --每页的大小
@PageCount int OUTPUT, --总页数
@RecordCount int OUTPUT
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT
--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @RecordCount = @PageCount
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageIndex,0)<1 OR ISNULL(@PageIndex,0)>@PageCount
SET @PageIndex=1
ELSE
SET @PageIndex=(@PageIndex-1)*@PageSize+1
--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageIndex,@PageSize
--关闭分页游标
EXEC sp_cursorclose @p1