8.17存储过程小练习

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 @sqlcount nvarchar(max) = ‘select @totalcount =  count(*) from UserInfo where 1=1‘
    if(@uname !=‘‘)
    begin
        set @sql+=‘ and uname like ‘‘%‘+@uname+‘%‘‘‘
        set @sqlcount +=‘ and uname like ‘‘%‘+@uname+‘%‘‘‘
    end

    set @sql+= ‘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 @sqlcount,N‘@totalcount int out‘,@totalcount out

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

end
declare @tc int,@tp int
exec P_Show 1,3,‘‘,@tc out,@tp out
select @tc,@tp

CommerceDBEntities db = new CommerceDBEntities();
        public List<UserInfo> Show(string uname, int pageindex, int pagesize, out int totalcount, out int totalpage)
        {
            var sqlcount = $"select count(*) from UserInfo";
            totalcount = db.Database.SqlQuery<int>(sqlcount).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();

            SqlParameter[] sqls =
            {
                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)
            };
            sqls[3].Direction = System.Data.ParameterDirection.Output;
            sqls[4].Direction = System.Data.ParameterDirection.Output;
            var list = db.Database.SqlQuery<UserInfo>("exec P_Show @pageindex,@pagesize,@uname,@totalcount out,@totalpage out", sqls).ToList();
            totalcount = (int)sqls[3].Value;
            totalpage = (int)sqls[4].Value;
            return list;
        }