asp.net中如何调用sql存储过程实现分页
首先看下面的代码创建存储过程
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存储过程实现分页的全部内容,希望对大家今后的学习有所帮助,当然方法不止本文所述,欢迎与大家分享好的方案。