EF调用存储过程

一、SQL Server代码

declare @pageindex int = 1
declare @pagesize int = 3
​
select * from UserInfo order by uid
offset (@pageindex-1)*@pagesize rows fetch next @pagesize rows only
​
select count(*) from UserInfo
​
​
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

二、Vs代码

1. DAl层--新建项--数据--ADO.NET实体数据模型--来自数据库的EF设计器--新建连接--Microsoft SQL Server--服务器名--选择或输入数据库名称--测试连接--确定--把表勾选--完成

   

    

    

    

    

    

    

2. 在DAL层引用上下文

3. DAL层代码

public List<UserInfo> PageShow(out int totalcount,out int totalpage,string uname,int pageindex= 1,int pagesize= 3)
        {
            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[3].Value;
​
            return list;
        }