问题描述
我调的一个分页的存储过程，代码如下：ALTERPROCEDURE[dbo].[sp_Page]/****************************************************************参数说明:1.Tables:表名称,视图,多表查询可用视图做2.PrimaryKey:主关键字3.Sort:排序语句，不带OrderBy比如：NewsIDDesc,OrderRowsAsc4.CurrentPage:当前页码5.PageSize:分页尺寸6.Fields:查询字段,默认为*6.Filter:过滤语句，不带Where7.Group:Group语句,不带GroupBy***************************************************************/(@Tablesvarchar(1000),@PrimaryKeyvarchar(100),@Sortvarchar(200)=NULL,@CurrentPageint=1,@PageSizeint=10,@Fieldsvarchar(1000)='*',@Filtervarchar(1000)=NULL,@Groupvarchar(1000)=NULL--@TotalRecordCountintoutput)AS/*默认排序*/IF@SortISNULLOR@Sort=''SET@Sort=@PrimaryKeyDECLARE@SortTablevarchar(100)DECLARE@SortNamevarchar(100)DECLARE@strSortColumnvarchar(200)DECLARE@operatorchar(2)DECLARE@typevarchar(100)DECLARE@precintDECLARE@sqlnvarchar(1000)DECLARE@paramDefinenvarchar(100)DECLARE@TotalRecordCountint/********************数据表中总记录数***********************/SET@sql='SELECT@TotalRecordCount=COUNT(*)FROM'+@Tables+'WHERE1=1'IF@FilterISNOTNULLAND@Filter<>''SET@sql=@sql+'AND'+@FilterSET@paramDefine='@TotalRecordCountINTOUTPUT'EXECSP_EXECUTESQL@sql,@paramDefine,@TotalRecordCountOUTPUT/*************************************************************//*设定排序语句.*/IFCHARINDEX('DESC',@Sort)>0BEGINSET@strSortColumn=REPLACE(@Sort,'DESC','')SET@operator='<='ENDELSEBEGINIFCHARINDEX('ASC',@Sort)=0SET@strSortColumn=REPLACE(@Sort,'ASC','')SET@operator='>='ENDIFCHARINDEX('.',@strSortColumn)>0BEGINSET@SortTable=SUBSTRING(@strSortColumn,0,CHARINDEX('.',@strSortColumn))SET@SortName=SUBSTRING(@strSortColumn,CHARINDEX('.',@strSortColumn)+1,LEN(@strSortColumn))ENDELSEBEGINSET@SortTable=@TablesSET@SortName=@strSortColumnENDSELECT@type=t.name,@prec=c.precFROMsysobjectsoJOINsyscolumnscono.id=c.idJOINsystypestonc.xusertype=t.xusertypeWHEREo.name=@SortTableANDc.name=@SortNameIFCHARINDEX('char',@type)>0SET@type=@type+'('+CAST(@precASvarchar)+')'DECLARE@strPageSizevarchar(50)DECLARE@strStartRowvarchar(50)DECLARE@strFiltervarchar(1000)DECLARE@strSimpleFiltervarchar(1000)DECLARE@strGroupvarchar(1000)/*默认当前页*/IF@CurrentPage<1SET@CurrentPage=1/*设置分页参数.*/SET@strPageSize=CAST(@PageSizeASvarchar(50))SET@strStartRow=CAST(((@CurrentPage-1)*@PageSize+1)ASvarchar(50))/*筛选以及分组语句.*/IF@FilterISNOTNULLAND@Filter!=''BEGINSET@strFilter='WHERE'+@Filter+''SET@strSimpleFilter='AND'+@Filter+''ENDELSEBEGINSET@strSimpleFilter=''SET@strFilter=''ENDIF@GroupISNOTNULLAND@Group!=''SET@strGroup='GROUPBY'+@Group+''ELSESET@strGroup=''/*执行查询语句*/EXEC('DECLARE@SortColumn'+@type+'SETROWCOUNT'+@strStartRow+'SELECT@SortColumn='+@strSortColumn+'FROM'+@Tables+@strFilter+''+@strGroup+'ORDERBY'+@Sort+'SETROWCOUNT'+@strPageSize+'SELECT'+@Fields+'FROM'+@Tables+'WHERE'+@strSortColumn+@operator+'@SortColumn'+@strSimpleFilter+''+@strGroup+'ORDERBY'+@Sort+'')return@TotalRecordCountGO在CS文件里调用代码如下：DBConndb=newDBConn();SqlParameter[]uiPage=newSqlParameter[8];uiPage[0]=newSqlParameter("@Tables","userInfo");uiPage[1]=newSqlParameter("@PrimaryKey","userID");uiPage[2]=newSqlParameter("@Sort","userID");uiPage[3]=newSqlParameter("@CurrentPage",page);uiPage[4]=newSqlParameter("@PageSize",columnsMax);uiPage[5]=newSqlParameter("@Fields","*");uiPage[6]=newSqlParameter("@Filter","delFlag=0");uiPage[7]=newSqlParameter("@TotalRecordCount",0);uiPage[7].Direction=System.Data.ParameterDirection.ReturnValue;varUlist=db.userInfo.SqlQuery("execsp_Page@Tables,@PrimaryKey,@Sort,@CurrentPage,@PageSize,@Fields,@Filter,@Group",uiPage).ToList();totalcolumns=int.Parse(uiPage[8].Value.ToString());rpList.DataSource=Ulist;rpList.DataBind();数据列表获取和显示都正常，但是记录总数取不到，哪位大神帮忙看看，感谢。
解决方案
解决方案二：
param=newSqlParameter("@RETURN",SqlDbType.Int);param.Direction=ParameterDirection.ReturnValue;foreach(SqlParameterparamincmd.Parameters){if(param.Direction==ParameterDirection.ReturnValue){r=param.Value;}}这样肯定能获取到吧
解决方案三：
按照你的习惯、为什么不用ADO呢？如果用EFDBFirst、存储过程直接就可以'点'出来、代码生成器帮你做好了、甚至返回值的类型都给你定义好了
解决方案四：
直接执行存储过程，然后返回输出参数值