公共分页

最新推荐文章于 2021-07-27 17:24:42 发布

ZLLZB

最新推荐文章于 2021-07-27 17:24:42 发布

阅读量147

点赞数

本文链接：https://blog.csdn.net/qq_43080624/article/details/82185249

版权

数据库：公共分页存储过程
create PROCEDURE [dbo].[proc_ProcGetPageData]

( @TableName VARCHAR(1000), –表名,多表是请使用 tA a inner join tB b On a.AID = b.AID

@PrimaryKey NVARCHAR(100), –主键，可以带表头 a.AID

@Fields NVARCHAR(4000) = ‘*’,–读取字段

@Condition NVARCHAR(3000) = ”,–Where条件

@CurrentPage INT = 1, –开始页码

@PageSize INT = 10, –页大小

@Sort NVARCHAR(200) = ”, –排序字段

@RecordCount INT = 0 OUT

)

DECLARE @strWhere VARCHAR(2000)

DECLARE @strsql NVARCHAR(3900)

IF @Condition IS NOT NULL AND len(ltrim(rtrim(@Condition)))>0

BEGIN

SET @strWhere = ’ WHERE ’ + @Condition + ’ ‘

END

ELSE

BEGIN

SET @strWhere = ”

END

IF (charindex(ltrim(rtrim(@PrimaryKey)),@Sort)=0)

BEGIN

IF(@Sort='')

    SET @Sort = @PrimaryKey + ' ASC '

ELSE

    SET @Sort = @Sort+ ' , '+@PrimaryKey + ' ASC '

END

SET @strsql = ‘SELECT @RecordCount = Count(1) FROM ’ + @TableName + @strWhere

EXECUTE sp_executesql @strsql ,N’@RecordCount INT output’,@RecordCount OUTPUT

IF @CurrentPage = 1 –第一页提高性能

BEGIN

SET @strsql = ‘SELECT TOP ’ + str(@PageSize) +’ ‘+@Fields

          + '  FROM ' + @TableName + ' ' + @strWhere + ' ORDER BY  '+ @Sort

END

ELSE

BEGIN

/* Execute dynamic query */    

DECLARE @START_ID NVARCHAR(50)

DECLARE @END_ID NVARCHAR(50)

SET @START_ID = CONVERT(NVARCHAR(50),(@CurrentPage - 1) * @PageSize + 1)

SET @END_ID = CONVERT(NVARCHAR(50),@CurrentPage * @PageSize)

SET @strsql =  ' SELECT *

FROM (SELECT ROW_NUMBER() OVER(ORDER BY ‘+@PrimaryKey+’) AS rownum,

 '+@Fields+ '

  FROM '+@TableName + @strWhere +') AS XX

WHERE rownum BETWEEN ‘+@START_ID+’ AND ’ +@END_ID

END
PRINT @strsql
EXEC(@strsql)

RETURN

///
/// 分页：公共类
///
/// 表名

这里写代码片aram>
/// 主键
/// 读取字段
/// where条件
/// 开始页码
/// 页行数
/// 排序字段
/// 总行数
///
public static DataTable ProcGetPageData(string Tablename, string PrimaryKey, string Fields, string Condition, int CurrentPage, int PageSize, string Sort, out int RecordCount)
{
RecordCount = 0;
string sql = “proc_ProcGetPageData”;
SqlParameter[] param =
{
new SqlParameter(“@TableName”,SqlDbType.VarChar,1000),//表名
new SqlParameter(“@PrimaryKey”,SqlDbType.VarChar,100),//主键
new SqlParameter(“@Fields”,SqlDbType.VarChar,4000),//读取字段 *
new SqlParameter(“@Condition”,SqlDbType.VarChar,3000),//where条件‘’
new SqlParameter(“@CurrentPage”,SqlDbType.Int),//开始页码 1
new SqlParameter(“@PageSize”,SqlDbType.Int),//页行数
new SqlParameter(“@Sort”,SqlDbType.VarChar,200),//排序字段
new SqlParameter(“@RecordCount”,SqlDbType.Int),//总行数
};
param[0].Value = Tablename;
param[2].Value = Fields;
param[1].Value = PrimaryKey;
param[3].Value = Condition;
param[4].Value = CurrentPage;
param[5].Value = PageSize;
param[6].Value = Sort;
param[7].Direction = ParameterDirection.Output;
DataTable sdr = DBHelper.GetTable(sql, param);
RecordCount = Convert.ToInt32(param[7].Value);
return sdr;
}