修正过的通用分页存储过程
http://www.cnblogs.com/maiqi/archive/2006/05/04/391349.html
修改了一下源码和存储过程,可同时返回分页所需数据的同时,返回所有数据的个数.
数据层也相应的做了些修改:
修改了一下源码和存储过程,可同时返回分页所需数据的同时,返回所有数据的个数.
------------------------------------
--用途:分页存储过程(对有主键的表效率极高)
--说明:
------------------------------------
ALTER PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@RowCount int output, -- 返回记录总数
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
declare @sql NVARCHAR(400)
set @sql = N'select @RowCount=count(*) from '+@tblName+' where '+ @strWhere
EXEC sp_executesql @sql,N'@RowCount int OUT',@RowCount OUT
exec (@strSQL)
--用途:分页存储过程(对有主键的表效率极高)
--说明:
------------------------------------
ALTER PROCEDURE UP_GetRecordByPage
@tblName varchar(255), -- 表名
@fldName varchar(255), -- 主键字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@RowCount int output, -- 返回记录总数
@OrderType bit = 0, -- 设置排序类型, 非 0 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(6000) -- 主语句
declare @strTmp varchar(100) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @OrderType != 0
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @fldName +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @fldName +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
if @PageIndex = 1
begin
set @strTmp =''
if @strWhere != ''
set @strTmp = ' where ' + @strWhere
set @strSQL = 'select top ' + str(@PageSize) + ' * from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
declare @sql NVARCHAR(400)
set @sql = N'select @RowCount=count(*) from '+@tblName+' where '+ @strWhere
EXEC sp_executesql @sql,N'@RowCount int OUT',@RowCount OUT
exec (@strSQL)
数据层也相应的做了些修改:
/// <summary>
/// 分页获取数据列表
/// </summary>
public DataSet GetList(int PageSize,int PageIndex,string strWhere,ref int Output)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@RowCount", SqlDbType.Int),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "TM_TeachInfo";
parameters[1].Value = "InfoID";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Direction = System.Data.ParameterDirection.Output;
parameters[5].Value = 1;
parameters[6].Value = strWhere;
DataSet ds = DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
if (parameters[4].Value != DBNull.Value && parameters[4].Value.ToString() != string.Empty)
{
Output=Convert.ToInt32(parameters[4].Value);
}
return ds;
}
/// 分页获取数据列表
/// </summary>
public DataSet GetList(int PageSize,int PageIndex,string strWhere,ref int Output)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@RowCount", SqlDbType.Int),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "TM_TeachInfo";
parameters[1].Value = "InfoID";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Direction = System.Data.ParameterDirection.Output;
parameters[5].Value = 1;
parameters[6].Value = strWhere;
DataSet ds = DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
if (parameters[4].Value != DBNull.Value && parameters[4].Value.ToString() != string.Empty)
{
Output=Convert.ToInt32(parameters[4].Value);
}
return ds;
}