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--返回总页数ASSETNOCOUNTONIFISNULL(@TotalCount,'')=''SET@TotalCount=0SET@Order=RTRIM(LTRIM(@Order))SET@PrimaryKey=RTRIM(LTRIM(@PrimaryKey))SET@FieldList=REPLACE(RTRIM(LTRIM(@FieldList)),'','')WHILECHARINDEX(',',@Order)>0ORCHARINDEX(',',@Order)>0BEGINSET@Order=REPLACE(@Order,',',',')SET@Order=REPLACE(@Order,',',',')ENDIFISNULL(@TableName,'')=''ORISNULL(@FieldList,'')=''ORISNULL(@PrimaryKey,'')=''OR@SortType<1OR@SortType>3OR@RecorderCount<0OR@PageSize<0OR@PageIndex<0BEGINPRINT('ERR_00')RETURNENDIF@SortType=3BEGINIF(UPPER(RIGHT(@Order,4))!='ASC'ANDUPPER(RIGHT(@Order,5))!='DESC')BEGINPRINT('ERR_02')RETURNENDENDDECLARE@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,'')=''BEGINSET@new_where1=''SET@new_where2='WHERE'ENDELSEBEGINSET@new_where1='WHERE'+@whereSET@new_where2='WHERE'+@where+'AND'ENDIFISNULL(@order,'')=''OR@SortType=1OR@SortType=2BEGINIF@SortType=1BEGINSET@new_order1='ORDERBY'+@PrimaryKey+'ASC'SET@new_order2='ORDERBY'+@PrimaryKey+'DESC'ENDIF@SortType=2BEGINSET@new_order1='ORDERBY'+@PrimaryKey+'DESC'SET@new_order2='ORDERBY'+@PrimaryKey+'ASC'ENDENDELSEBEGINSET@new_order1='ORDERBY'+@OrderENDIF@SortType=3ANDCHARINDEX(','+@PrimaryKey+'',','+@Order)>0BEGINSET@new_order1='ORDERBY'+@OrderSET@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<>'*'BEGINSET@new_order3=REPLACE(REPLACE(@Order+',','ASC,',','),'DESC,',',')SET@FieldList=','+@FieldListWHILECHARINDEX(',',@new_order3)>0BEGINIFCHARINDEX(SUBSTRING(','+@new_order3,1,CHARINDEX(',',@new_order3)),','+@FieldList+',')>0BEGINSET@FieldList=@FieldList+','+SUBSTRING(@new_order3,1,CHARINDEX(',',@new_order3))ENDSET@new_order3=SUBSTRING(@new_order3,CHARINDEX(',',@new_order3)+1,LEN(@new_order3))ENDSET@FieldList=SUBSTRING(@FieldList,2,LEN(@FieldList))ENDENDSET@SqlCount='SELECT@TotalCount=COUNT(*),@TotalPageCount=CEILING((COUNT(*)+0.0)/'+CAST(@PageSizeASVARCHAR)+')FROM'+@TableName+@new_where1IF@RecorderCount=0BEGINEXECSP_EXECUTESQL@SqlCount,N'@TotalCountINTOUTPUT,@TotalPageCountINTOUTPUT',@TotalCountOUTPUT,@TotalPageCountOUTPUTENDELSEBEGINSELECT@TotalCount=@RecorderCountENDIF@PageIndex>CEILING((@TotalCount+0.0)/@PageSize)BEGINSET@PageIndex=CEILING((@TotalCount+0.0)/@PageSize)ENDIF@PageIndex=1OR@PageIndex>=CEILING((@TotalCount+0.0)/@PageSize)BEGINIF@PageIndex=1--返回第一页数据BEGINSET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM'+@TableName+@new_where1+@new_order1ENDIF@PageIndex>=CEILING((@TotalCount+0.0)/@PageSize)--返回最后一页数据BEGINSET@Sql='SELECTTOP'+STR(@PageSize)+''+@FieldList+'FROM('+'SELECTTOP'+STR(ABS(@PageSize*@PageIndex-@TotalCount-@PageSize))+''+@FieldList+'FROM'+@TableName+@new_where1+@new_order2+')ASTMP'+@new_order1ENDENDELSEBEGINIF@SortType=1--仅主键正序排序BEGINIF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索BEGINSET@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_order1ENDELSE--反向检索BEGINSET@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_order1ENDENDIF@SortType=2--仅主键反序排序BEGINIF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索BEGINSET@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_order1ENDELSE--反向检索BEGINSET@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_order1ENDENDIF@SortType=3--多列排序，必须包含主键，且放置最后，否则不处理BEGINIFCHARINDEX(','+@PrimaryKey+'',','+@Order)=0BEGINPRINT('ERR_02')RETURNENDIF@PageIndex<=CEILING((@TotalCount+0.0)/@PageSize)/2--正向检索BEGINSET@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_order1ENDELSE--反向检索BEGINSET@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_order1ENDENDENDPRINT(@Sql)EXEC(@Sql)GO

2、SQL Server 中调用测试代码

--执行存储过程declare@TotalCountint,@TotalPageCountintexecP_viewPage'T_Module','*','ModuleID','','',1,0,10,1,@TotalCountoutput,@TotalPageCountoutputSelect@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;}#endregionDataSetds=SqlHelper.PageList(SqlHelper.LocalSqlServer,"T_User","*","UserID","","",1,0,pageSize,1,reftotalCount,reftotalPageCount);this.RptData.DataSource=ds;this.RptData.DataBind();

关于asp.net中怎么调用sql存储过程问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。