asp.net中如何调用sql存储过程实现分页

2024-03-28 06:22:05 243

首先看下面的代码创建存储过程

1、创建存储过程，语句如下：

CREATEPROCP_viewPage
@TableNameVARCHAR(200),--表名
@FieldListVARCHAR(2000),--显示列名，如果是全部字段则为*
@PrimaryKeyVARCHAR(100),--单一主键或唯一值键
@WhereVARCHAR(2000),--查询条件不含'where'字符，如id>10andlen(userid)>9
@OrderVARCHAR(1000),--排序不含'orderby'字符，如idasc,useriddesc，必须指定asc或desc
--注意当@SortType=3时生效，记住一定要在最后加上主键，否则会让你比较郁闷
@SortTypeINT,--排序规则1:正序asc2:倒序desc3:多列排序方法
@RecorderCountINT,--记录总数0:会返回总记录
@PageSizeINT,--每页输出的记录数
@PageIndexINT,--当前页数
@TotalCountINTOUTPUT,--记返回总记录
@TotalPageCountINTOUTPUT--返回总页数
AS
SETNOCOUNTON

IFISNULL(@TotalCount,'')=''SET@TotalCount=0
SET@Order=RTRIM(LTRIM(@Order))
SET@PrimaryKey=RTRIM(LTRIM(@PrimaryKey))
SET@FieldList=REPLACE(RTRIM(LTRIM(@FieldList)),'','')

WHILECHARINDEX(',',@Order)>0ORCHARINDEX(',',@Order)>0
BEGIN
SET@Order=REPLACE(@Order,',',',')
SET@Order=REPLACE(@Order,',',',')
END

IFISNULL(@TableName,'')=''ORISNULL(@FieldList,'')=''
ORISNULL(@PrimaryKey,'')=''
OR@SortType<1OR@SortType>3
OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0
BEGIN
PRINT('ERR_00')
RETURN
END

IF@SortType=3
BEGIN
IF(UPPER(RIGHT(@Order,4))!='ASC'ANDUPPER(RIGHT(@Order,5))!='DESC')
BEGINPRINT('ERR_02')RETURNEND
END

DECLARE@new_where1VARCHAR(1000)
DECLARE@new_where2VARCHAR(1000)
DECLARE@new_order1VARCHAR(1000)
DECLARE@new_order2VARCHAR(1000)
DECLARE@new_order3VARCHAR(1000)
DECLARE@SqlVARCHAR(8000)
DECLARE@SqlCountNVARCHAR(4000)

IFISNULL(@where,'')=''
BEGIN
SET@new_where1=''
SET@new_where2='WHERE'
END
ELSE
BEGIN
SET@new_where1='WHERE'+@where
SET@new_where2='WHERE'+@where+'AND'
END

IFISNULL(@order,'')=''OR@SortType=1OR@SortType=2
BEGIN
IF@SortType=1
BEGIN
SET@new_order1='ORDERBY'+@PrimaryKey+'ASC'
SET@new_order2='ORDERBY'+@PrimaryKey+'DESC'
END
IF@SortType=2
BEGIN
SET@new_order1='ORDERBY'+@PrimaryKey+'DESC'
SET@new_order2='ORDERBY'+@PrimaryKey+'ASC'
END
END
ELSE
BEGIN
SET@new_order1='ORDERBY'+@Order
END

IF@SortType=3ANDCHARINDEX(','+@PrimaryKey+'',','+@Order)>0
BEGIN
SET@new_order1='ORDERBY'+@Order
SET@new_order2=@Order+','
SET@new_order2=REPLACE(REPLACE(@new_order2,'ASC,','{ASC},'),'DESC,','{DESC},')
SET@new_order2=REPLACE(REPLACE(@new_order2,'{ASC},','DESC,'),'{DESC},','ASC,')
SET@new_order2='ORDERBY'+SUBSTRING(@new_order2,1,LEN(@new_order2)-1)
IF@FieldList<>'*'
BEGIN
SET@new_order3=REPLACE(REPLACE(@Order+',','ASC,',','),'DESC,',',')
SET@FieldList=','+@FieldList
WHILECHARINDEX(',',@new_order3)>0
BEGIN
IFCHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0
BEGIN
SET@FieldList=
@FieldList+','+SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))
END
SET@new_order3=
SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))
END
SET@FieldList=SUBSTRING(@FieldList,2,LEN(@FieldList))
END
END

SET@SqlCount='SELECT@TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'
+CAST(@PageSizeASVARCHAR)+')FROM'+@TableName+@new_where1
IF@RecorderCount=0
BEGIN
EXECSP_EXECUTESQL@SqlCount,N'@TotalCountINTOUTPUT,@TotalPageCountINTOUTPUT',
@TotalCountOUTPUT,@TotalPageCountOUTPUT
END
ELSE
BEGIN
SELECT@TotalCount=@RecorderCount
END

IF@PageIndex>CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
SET@PageIndex=CEILING((@TotalCount+0.0)/@PageSize)
END

IF@PageIndex=1OR@PageIndex>=CEILING((@TotalCount+0.0)/@PageSize)
BEGIN
IF@PageIndex=1--返回第一页数据
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+@new_where1+@new_order1
END
IF@PageIndex>=CEILING((@TotalCount+0.0)/@PageSize)--返回最后一页数据
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM('
+'SELECTTOP'+STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))
+''+@FieldList+'FROM'
+@TableName+@new_where1+@new_order2+')ASTMP'
+@new_order1
END
END
ELSE
BEGIN
IF@SortType=1--仅主键正序排序
BEGIN
IF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+@new_where2+@PrimaryKey+'>'
+'(SELECTMAX('+@PrimaryKey+')FROM(SELECTTOP'
+STR(@PageSize*(@PageIndex-1))+''+@PrimaryKey
+'FROM'+@TableName
+@new_where1+@new_order1+')ASTMP)'+@new_order1
END
ELSE--反向检索
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM('
+'SELECTTOP'+STR(@PageSize)+''
+@FieldList+'FROM'
+@TableName+@new_where2+@PrimaryKey+'<'
+'(SELECTMIN('+@PrimaryKey+')FROM(SELECTTOP'
+STR(@TotalCount-@PageSize*@PageIndex)+''+@PrimaryKey
+'FROM'+@TableName
+@new_where1+@new_order2+')ASTMP)'+@new_order2
+')ASTMP'+@new_order1
END
END
IF@SortType=2--仅主键反序排序
BEGIN
IF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'
+@TableName+@new_where2+@PrimaryKey+'<'
+'(SELECTMIN('+@PrimaryKey+')FROM(SELECTTOP'
+STR(@PageSize*(@PageIndex-1))+''+@PrimaryKey
+'FROM'+@TableName
+@new_where1+@new_order1+')ASTMP)'+@new_order1
END
ELSE--反向检索
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM('
+'SELECTTOP'+STR(@PageSize)+''
+@FieldList+'FROM'
+@TableName+@new_where2+@PrimaryKey+'>'
+'(SELECTMAX('+@PrimaryKey+')FROM(SELECTTOP'
+STR(@TotalCount-@PageSize*@PageIndex)+''+@PrimaryKey
+'FROM'+@TableName
+@new_where1+@new_order2+')ASTMP)'+@new_order2
+')ASTMP'+@new_order1
END
END
IF@SortType=3--多列排序，必须包含主键，且放置最后，否则不处理
BEGIN
IFCHARINDEX(','+@PrimaryKey+'',','+@Order)=0
BEGINPRINT('ERR_02')RETURNEND
IF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM('
+'SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM('
+'SELECTTOP'+STR(@PageSize*@PageIndex)+''+@FieldList
+'FROM'+@TableName+@new_where1+@new_order1+')ASTMP'
+@new_order2+')ASTMP'+@new_order1
END
ELSE--反向检索
BEGIN
SET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM('
+'SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM('
+'SELECTTOP'+STR(@TotalCount-@PageSize*@PageIndex+@PageSize)+''+@FieldList
+'FROM'+@TableName+@new_where1+@new_order2+')ASTMP'
+@new_order1+')ASTMP'+@new_order1
END
END
END
PRINT(@Sql)
EXEC(@Sql)
GO

2、SQLServer中调用测试代码

--执行存储过程

declare@TotalCountint,
@TotalPageCountint
execP_viewPage'T_Module','*','ModuleID','','',1,0,10,1,@TotalCountoutput,@TotalPageCountoutput
Select@TotalCount,@TotalPageCount;

asp.net代码实现：

#region===========通用分页存储过程===========
publicstaticDataSetRunProcedureDS(stringconnectionString,stringstoredProcName,IDataParameter[]parameters,stringtableName)
{
using(SqlConnectionconnection=newSqlConnection(connectionString))
{
DataSetdataSet=newDataSet();
connection.Open();
SqlDataAdaptersqlDA=newSqlDataAdapter();
sqlDA.SelectCommand=BuildQueryCommand(connection,storedProcName,parameters);
sqlDA.Fill(dataSet,tableName);
connection.Close();
returndataSet;
}
}
///<summary>
///通用分页存储过程
///</summary>
///<paramname="connectionString"></param>
///<paramname="tblName"></param>
///<paramname="strGetFields"></param>
///<paramname="primaryKey"></param>
///<paramname="strWhere"></param>
///<paramname="strOrder"></param>
///<paramname="sortType"></param>
///<paramname="recordCount"></param>
///<paramname="PageSize"></param>
///<paramname="PageIndex"></param>
///<paramname="totalCount"></param>
///<paramname="totalPageCount"></param>
///<returns></returns>
publicstaticDataSetPageList(stringconnectionString,stringtblName,stringstrGetFields,stringprimaryKey,stringstrWhere,stringstrOrder,intsortType,intrecordCount,
intPageSize,intPageIndex,refinttotalCount,refinttotalPageCount)
{
SqlParameter[]parameters={newSqlParameter("@TableName",SqlDbType.VarChar,200),
newSqlParameter("@FieldList",SqlDbType.VarChar,2000),
newSqlParameter("@PrimaryKey",SqlDbType.VarChar,100),
newSqlParameter("@Where",SqlDbType.VarChar,2000),
newSqlParameter("@Order",SqlDbType.VarChar,1000),
newSqlParameter("@SortType",SqlDbType.Int),
newSqlParameter("@RecorderCount",SqlDbType.Int),
newSqlParameter("@PageSize",SqlDbType.Int),
newSqlParameter("@PageIndex",SqlDbType.Int),
newSqlParameter("@TotalCount",SqlDbType.Int),
newSqlParameter("@TotalPageCount",SqlDbType.Int)};

parameters[0].Value=tblName;
parameters[1].Value=strGetFields;
parameters[2].Value=primaryKey;
parameters[3].Value=strWhere;
parameters[4].Value=strOrder;
parameters[5].Value=sortType;
parameters[6].Value=recordCount;
parameters[7].Value=PageSize;
parameters[8].Value=PageIndex;
parameters[9].Value=totalCount;
parameters[9].Direction=ParameterDirection.Output;
parameters[10].Value=totalPageCount;
parameters[10].Direction=ParameterDirection.Output;

DataSetds=RunProcedureDS(connectionString,"P_viewPage",parameters,"PageListTable");
totalCount=int.Parse(parameters[9].Value.ToString());
totalPageCount=int.Parse(parameters[10].Value.ToString());
returnds;
}
#endregion
DataSetds=SqlHelper.PageList(SqlHelper.LocalSqlServer,"T_User","*","UserID","","",1,0,pageSize,1,reftotalCount,reftotalPageCount);
this.RptData.DataSource=ds;
this.RptData.DataBind();

以上内容就是本文介绍asp.net中如何调用sql存储过程实现分页的全部内容，希望对大家今后的学习有所帮助，当然方法不止本文所述，欢迎与大家分享好的方案。

asp.net中如何调用sql存储过程实现分页

热门推荐

随机推荐