create proc P_show
(
    @pageindex int ,
    @pagesize int,
    @Uname nvarchar(20),
    @totalcount int out,
    @totalpage int out
)
as
begin
  declare @sql nvarchar(max)=‘select * from UserInfo where 1=1 ‘
  declare @sqls nvarchar(max)=‘select @totalcount=count(*) from UserInfo where 1=1 ‘
  if(@Uname!=null)
  begin
    set @sql +=‘ and Uname like ‘‘%‘+@Uname+‘%‘‘‘
    set @sqls +=‘ and Uname like ‘‘%‘+@Uname+‘%‘‘‘
  end

  set @sql+=‘select * from UserInfo order by Uid offset (@pageindex-1)*@pagesize rows fetch next @pagesize rows only‘
  exec sp_executesql @sql,N‘@pageindex int,@pagesize int‘,@pageindex,@pagesize
    exec sp_executesql @sqls,N‘@totalcount int out‘,@totalcount out

    set @totalpage=CEILING(@totalcount/1.0/@pagesize)
end


declare @c int ,@p int 
exec P_show 1,3,‘‘,@c out,@p out
select @c,@p

 public List<UserInfo>Pageshow(out int totalcount,out int totalpage,string Uname,int pageindex=1,int pagesize=3)
        {
            #region MyRegion
            ////var sql = db.UserInfo.AsEnumerable();
            ////if(!string.IsNullOrWhiteSpace(Uname))
            ////{
            ////    sql = sql.Where(a => a.Uname.Contains(Uname));

            ////}
            ////totalcount = sql.Count();
            ////totalpage = Convert.ToInt32(Math.Ceiling(totalcount / 1.0 / pagesize));
            ////return sql.OrderBy(a => a.UId).Skip((pageindex - 1) * pagesize).Take(pagesize).ToList();


            ////var sqls = $"select count(*) from UserInfo ";
            ////totalcount = db.Database.SqlQuery<int>(sqls).FirstOrDefault();
            ////totalpage= Convert.ToInt32(Math.Ceiling(totalcount / 1.0 / pagesize));
            ////var sql = $"select * from UserInfo order by Uid offset ({pageindex}-1)*{pagesize} rows fetch next {pagesize} rows only";
            ////return db.Database.SqlQuery<UserInfo>(sql).ToList(); 
            #endregion


            SqlParameter[] sqlss =
            {
                new SqlParameter("@pageindex",pageindex),
                new SqlParameter("@pagesize",pagesize),
                new SqlParameter("@Uname",Uname==null?"":Uname),
                new SqlParameter("@totalcount",System.Data.SqlDbType.Int),
                new SqlParameter("@totalpage",System.Data.SqlDbType.Int),

            };
            sqlss[3].Direction = System.Data.ParameterDirection.Output;
            sqlss[4].Direction = System.Data.ParameterDirection.Output;

            //执行存储过程
         var list=   db.Database.SqlQuery<UserInfo>("exec P_show @pageindex,@pagesize,@Uname,@totalcount out, @totalpage out", sqlss).ToList();
            totalcount = (int)sqlss[3].Value;
            totalpage = (int)sqlss[4].Value;
            return list;
        }

 public ActionResult Index()
        {
            int totalcount = 0;
            int totalpage = 0;
            var list = dal.Pageshow(out totalcount, out totalpage,"");
            return View();
        }