c# 配合存储过程读取数据库分布列表

CREATE PROCEDURE [dbo].[GetList]
 @where nvarchar(200), @totalRecord int OUTPUT, @TotalPage int OUTPUT, @pageSize int, @pageIndex int,@tablename nvarchar(200),@orderField nvarchar(200),@AscOrDesc nvarchar(200)
WITH EXEC AS CALLER
AS
begin
--set @where='';

declare @sql nvarchar(max)
set @sql='select    @OuttotalRecord=count(*) from  '+ @tablename +  @where 
exec sp_executesql 	@sql,	N'@OuttotalRecord as int output',@OuttotalRecord=@totalRecord output
--print @totalRecord;

--计算总页数
select @TotalPage=CEILING((@totalRecord+0.0)/@pageSize)
--处理页数超出范围情况
    if @pageIndex<=0
        Set @pageIndex = 1;

    if @pageIndex>@TotalPage
        Set @pageIndex = @TotalPage;

     --处理开始点和结束点
    Declare @StartRecord int;
    Declare @EndRecord int;

    set @StartRecord = (@pageIndex-1)*@PageSize + 1
    set @EndRecord = @StartRecord + @pageSize - 1
set @sql='
select * from( 
select * ,ROW_NUMBER() over(order by '+@orderField + '  ' +@AscOrDesc +') as "No."  from   '  +  @tablename  + @where --后接其它条件
+')  as t  
where t."No." between '+ convert(nvarchar,@StartRecord) +' and '+convert(nvarchar,@EndRecord)  ;

exec( 	@sql)
end

        public List<Users> GetByFilter(string keyword, int agentId, int pageIndex, int pageSize, ref int count, ref int totalPage)
        {
            string where = " where 1=1 ";
            if(!string.IsNullOrEmpty(keyword))
            {
                where += " and   ( NickName ='" + keyword + "' or NickName='"+keyword+"%00' or GameID="+keyword+")";
            }
            if(agentId!=0)
            {
                where += " and  SpreaderID=" + agentId;
            }
            //if(!string.IsNullOrEmpty(where))
            //{
            //    where = where.Trim();
            //    where=where+ where.TrimStart(new char[]{ 'a','n','d'});
            //}
            using (HuiYouWebDBContext db=new HuiYouWebDBContext())
            {
                var whereP=new  System.Data.SqlClient.SqlParameter
                {
                    ParameterName="@where",
                    SqlDbType=SqlDbType.NVarChar,
                    Size=200,
                    Value=where
                };
                var totalRecordP=new System.Data.SqlClient.SqlParameter
                {
                    ParameterName="@totalRecord",
                    Value = count,
                    Direction=ParameterDirection.Output
                };
                var totalPageP=new System.Data.SqlClient.SqlParameter
                {
                    ParameterName="@TotalPage",
                    Value=totalPage,
                    Direction=ParameterDirection.Output
                };
                var pageSizeP=new System.Data.SqlClient.SqlParameter
                {
                    ParameterName="@pageSize",
                    Value=pageSize
                };
                var pageIndexP=new System.Data.SqlClient.SqlParameter
                {
                    ParameterName="@pageIndex",
                    Value=pageIndex
                };
                var tableNameP=new System.Data.SqlClient.SqlParameter
                {
                    ParameterName="@tablename",
                    SqlDbType = SqlDbType.NVarChar,
                    Size = 200,
                    Value="[QPAccountsDB].[dbo].[AccountsInfo]"
                };
                  var orderFieldP=new System.Data.SqlClient.SqlParameter
                {
                    ParameterName="@orderField",
                    SqlDbType = SqlDbType.NVarChar,
                    Size = 200,
                    Value="UserID"
                };
                  var AscOrDescP=new System.Data.SqlClient.SqlParameter
                {
                    ParameterName="@AscOrDesc",
                    SqlDbType = SqlDbType.NVarChar,
                    Size = 200,
                    Value="desc"
                };

                System.Data.SqlClient.SqlParameter[] param={whereP,totalRecordP,totalPageP,pageSizeP,pageIndexP,tableNameP,orderFieldP,AscOrDescP};
                var results = db.Database.SqlQuery<Users>("exec dbo.GetList  @where, @totalRecord OUTPUT, @TotalPage OUTPUT, @pageSize, @pageIndex,@tablename,@orderField,@AscOrDesc", param);
                List<Users> list=results.ToList();
                count = Convert.ToInt32(totalRecordP.Value);
                totalPage = Convert.ToInt32(totalPageP.Value);
                return list;
            }