62,047
社区成员
发帖
与我相关
我的任务
分享
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[PF_PageControl]
@tableName varchar(500), -- 表名
@tableFields varchar(5000) = '*', -- 字段名(全部字段为*)
@sqlWhere varchar(5000) = NULL, -- 条件语句(不加 where,可带 group by 分组条件,分组条件需要带 group by 关键字)
@orderFields varchar(5000), -- 排序字段(必须,支持多字段,不加 order by)
@pageSize int, -- 页大小(每页多少条记录)
@pageIndex int = 1 , -- 指定当前为第几页
@totalPage int output -- 返回总页数
AS
BEGIN
Begin Tran -- 开始事务
Declare @sql nvarchar(4000);
Declare @totalRecord int;
-- 计算总记录数
if (@sqlWhere = '' or @sqlWhere = NULL)
set @sql = 'select @totalRecord = count(0) from ' + @tableName
else
BEGIN
if(CHARINDEX('group by', LOWER(@sqlWhere)) > 0)
set @sql = 'select @totalRecord = count(0) from (select ' + @tableFields + ' from ' + @tableName + ' where ' + @sqlWhere + ') as Tab_GroupTable'
else
set @sql = 'select @totalRecord = count(0) from ' + @tableName + ' where ' + @sqlWhere
END
--print @Sql
EXEC sp_executesql @sql,N'@totalRecord int OUTPUT',@totalRecord OUTPUT
-- 计算总页数
select @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语句
if (@sqlWhere = '' or @sqlWhere = NULL)
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderFields + ') as rowId,' + @tableFields + ' from ' + @tableName
else
set @sql = 'Select * FROM (select ROW_NUMBER() Over(order by ' + @orderFields + ') as rowId,' + @tableFields + ' from ' + @tableName + ' where ' + @sqlWhere
set @Sql = @Sql + ') as Tab_TotalTable where rowId between ' + Convert(varchar(50),@startRecord) + ' and ' + Convert(varchar(50),@endRecord)
print @Sql
Exec(@Sql)
if @@Error <> 0
BEGIN
RollBack Tran
Return -1
END
else
BEGIN
Commit Tran
Return @totalRecord --- 返回记录总数
END
END
#region 读取产品数据分页
/// <summary>
/// 读取产品数据分页
/// </summary>
/// <param name="getpage"></param>
/// <param name="sql"></param>
/// <returns></returns>
public SqlDataReader GetProductList(Model.S_page getpage,string sql) {
return SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, sql,
new SqlParameter("@tableName", getpage.tableName),
new SqlParameter("@tableFields", getpage.strGetFields),
new SqlParameter("@sqlWhere", getpage.strWhere),
new SqlParameter("@orderFields", getpage.orderType),
new SqlParameter("@pageSize", getpage.pageSize),
new SqlParameter("@pageIndex", getpage.pageIndex),
new SqlParameter("@totalPage",ParameterDirection.Output)
);
}
#endregion
public SqlDataReader GetProductlist(S_page spage, string sql)
{
return db.GetProductList(spage, sql);
}
//读取数据
GetPage.tableName = "S_Product";
GetPage.fldname = "sid";
GetPage.orderType = "sid desc";
GetPage.pageIndex = IntPage;
GetPage.pageSize = pagesize;
GetPage.strGetFields = " sid,s_sort,s_sort2,s_title,s_price,s_link,s_content,s_pic";
GetPage.doCount = 0;
GetPage.strWhere = " 1=1 " + strwhere;
this.ProductList.DataSource = db.GetProductlist(GetPage, "PF_PageControl");
this.ProductList.DataBind();
public SqlDataReader GetProductList(Model.S_page getpage,string sql) {
return SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, sql,
new SqlParameter("@tableName", getpage.tableName),
new SqlParameter("@tableFields", getpage.strGetFields),
new SqlParameter("@sqlWhere", getpage.strWhere),
new SqlParameter("@orderFields", getpage.orderType),
new SqlParameter("@pageSize", getpage.pageSize),
new SqlParameter("@pageIndex", getpage.pageIndex),
new SqlParameter("@totalPage",ParameterDirection.Output)
);
}