EF调用Sql操作

一、数据库操作

    1、在Sql数据库中进行分页的操作

    

declare @pageindex int=1
declare @pagesize int =3

select * from UserInfo order by uid 
offset(@pageindex-1)*@pagesize rows fetch  next @pagesize rows only


drop proc P_Show
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

 

 

 

二、后台操作

     1、创建MVC项目

     2、新建一个dal文件夹或者搭建三层

     3、在dal文件夹中右键=》添加=》新建项=》数据=》ADO.NET 实体数据模型=》

      

   4、选择来自数据库的EF设计器

      

    5、新建连接

      

     6、选择数据源   服务器名称和数据库名称

      

 

   三、选择要使用的表

      1、创建一个dal类

      2、在类中实例化迁移

       3、定义一个方法

public List<UserInfo> PageShow(out int? totalcount,out int? totalpage,string uname,int pageindex=1,int pagesize=3)
        {
            //二、执行sql语句
            //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),
                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;

        }

 

      待续......