通用分页存储过程(sqlServer)

发布时间：2020-12-12 15:40:36 所属栏目：MsSql教程来源：网络整理

导读：参照网上的一些别人写的存储过程，并修改了一下以方便使用。 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[pagination] @tblName varchar(255),-- 表名 @strGetFields varchar(1000) = '*',-- 需要返回的列 @fldName varchar(2

参照网上的一些别人写的存储过程，并修改了一下以方便使用。

SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pagination]
@tblName varchar(255),-- 表名
@strGetFields varchar(1000) = '*',-- 需要返回的列
@fldName varchar(255)='',-- 排序的字段名
@PageSize int = 10,-- 页尺寸
@PageIndex int = 1,-- 页码
@RecordCount int out,-- 返回记录总数
@OrderType bit = 0,-- 设置排序类型,非 0 值则降序
@strWhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(5000) -- 主语句
declare @strTmp varchar(110) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
declare @tmpSQLCount nvarchar(2000)

if @strWhere !=''
?? ?set @tmpSQLCount = 'select @spIntRecordCount=count(*) from [' + @tblName + '] where '+@strWhere
else
?? ?set @tmpSQLCount = 'select @spIntRecordCount=count(*) from [' + @tblName + ']'
EXECUTE SP_EXECUTESQL
??????????? @tmpSQLCount,
??????????? N'@spIntRecordCount int OUTPUT',
??????????? @spIntRecordCount = @RecordCount OUTPUT　

if @OrderType != 0
?? ?begin
?? ?set @strTmp = '<(select min'
?? ?set @strOrder = ' order by [' + @fldName +'] desc'
?? ?--如果@OrderType不是0，就执行降序，这句很重要！
?? ?end
else
?? ?begin
?? ?set @strTmp = '>(select max'
?? ?set @strOrder = ' order by [' + @fldName +'] asc'
?? ?end

if @PageIndex = 1
?? ?begin
?? ?if @strWhere != ''
?? ?set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from [' + @tblName + '] where ' + @strWhere + ' ' + @strOrder
?? ?else
?? ?set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' from ['+ @tblName + '] '+ @strOrder
?? ?--如果是第一页就执行以上代码，这样会加快执行速度
?? ?end
else
?? ?begin
?? ?--以下代码赋予了@strSQL以真正执行的SQL代码
?? ?set @strSQL = 'select top ' + str(@PageSize) +' '+@strGetFields+ ' 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) +' '+@strGetFields+ ' from ['
?? ??? ?+ @tblName + '] where [' + @fldName + ']' + @strTmp + '(['
?? ??? ?+ @fldName + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
?? ??? ?+ @fldName + '] from [' + @tblName + '] where ' + @strWhere + ' '
?? ??? ?+ @strOrder + ') as tblTmp) and ' + @strWhere + ' ' + @strOrder
?? ?end
?? ?

exec (@strSQL)

C#代码：

????? public PageDataTable GetList(string fields,string fldName,int pageSize,int pageIndex,bool orderType,string where)
??????? {
??????????? PageDataTable pageTable = new PageDataTable();
??????????? string procedurName = "pagination";
??????????? SqlParameter[] param = new SqlParameter[8];
??????????? param[0] = new SqlParameter("@tblName",SqlDbType.VarChar,255);
??????????? param[1] = new SqlParameter("@strGetFields",1000);
??????????? param[2] = new SqlParameter("@fldName",255);
??????????? param[3] = new SqlParameter("@PageSize",SqlDbType.Int);
??????????? param[4] = new SqlParameter("@PageIndex",SqlDbType.Int);
??????????? param[5] = new SqlParameter("@RecordCount",SqlDbType.Int);
??????????? param[6] = new SqlParameter("@OrderType",SqlDbType.Bit);
??????????? param[7] = new SqlParameter("@strWhere",1500);
??????????? param[0].Value = "product";
??????????? param[1].Value = fields;
??????????? param[2].Value = fldName;
??????????? param[3].Value = pageSize;
??????????? param[4].Value = pageIndex;
??????????? param[5].Direction = ParameterDirection.Output;
??????????? param[6].Value = orderType;
??????????? param[7].Value = where;
??????????? using (SqlConnection conn = new SqlConnection(connstr))
??????????? {
??????????????? conn.Open();
??????????????? SqlCommand cmd = conn.CreateCommand();
??????????????? cmd.CommandType = CommandType.StoredProcedure;
??????????????? cmd.CommandText = procedurName;
??????????????? foreach (SqlParameter p in param)
??????????????????? cmd.Parameters.Add(p);
?????????????? ?
??????????????? SqlDataReader reader = cmd.ExecuteReader();
??????????????? DataTable table = new DataTable();
??????????????? table.Load(reader);

??????????????? int recordCount = int.Parse(cmd.Parameters["@RecordCount"].Value.ToString());
??????????????? pageTable.dt = table;
??????????????? pageTable.recordCount = recordCount;

??????????? }
??????????? return pageTable;
??????? }

PageDataTable类

????? public class PageDataTable
??? {
??????? public int recordCount { get; set; }
??????? public DataTable dt { get; set; }
??? }

页面使用：

??????????? string strGetFields = "productId,productName,productName_en";
??????????? string fldName = "productId";
??????????? bool orderType = true;
??????????? string strWhere = "productName like '%凱沙%'";
??????????? PageDataTable table = new ProductDAO().GetList(strGetFields,fldName,AspNetPager1.PageSize,AspNetPager1.CurrentPageIndex,orderType,strWhere);
??????????? rProductList.DataSource = table.dt;
??????????? rProductList.DataBind();
??????????? AspNetPager1.RecordCount = table.recordCount;

基本AspNetPager1为AspNetPager控件

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!