EF框架的增删查改

构建EF框架

创建一个控制台应用程序，添加model实体类，实体类名称RbacDBEntities
数据库表名Roles
里面有编号，姓名，备注三个字段

新增的4种方法

RbacDBEntities db = new RbacDBEntities();
            //方式1 Add
            Role r1 = new Role()
            {
                Name = "方式1",
                Remark = "备注1"
            };
            db.Roles.Add(r1);
            db.SaveChanges();
            //方式2
            Role r2 = new Role()
            {
                Name = "方式2",
                Remark = "备注2"
            };
            db.Entry(r2).State = System.Data.Entity.EntityState.Added;
            db.SaveChanges();
            //方式3调用方sql
            string sql = @"insert into roles values('方式3','备注3')";
            db.Database.ExecuteSqlCommand(sql);
            db.SaveChanges();
            //方式4，调用存储过程
            db.cp_insert_role("方法4", "备注4");
            Console.Read();

新增存储过程

go
create proc cp_insert_role
(
   --定义变量，接收传进来的姓名和备注
   @Name varchar(50),
   @Remark varchar(200)
)
as 
   --新增语句
   insert Roles values(@Name,@Remark)

修改的4种方法

RbacDBEntities db = new RbacDBEntities();
            //方式1 Add
            Role r1 = db.Roles.Find(1074);
            r1.Name = "修改1";
            r1.Remark = "修改备注1";
            db.SaveChanges();
            //方式2
            Role r2 = new Role()
            {
                ID = 1075,
                Name = "修改2",
                Remark = "修改备注2"
            };
            db.Entry(r2).State = System.Data.Entity.EntityState.Modified;
            db.SaveChanges();
            //方式3调用方sql
            string sql = @"update roles set name='修改3',remark='修改备注3' where id=1076";
            db.Database.ExecuteSqlCommand(sql);
            db.SaveChanges();
            //方式4，调用存储过程
            db.cp_update_role(2, "修改4", "修改备注4");
            Console.Read();

修改的存储过程

go
create proc cp_update_role
(
    @ID int,
 @Name varchar(50),
 @Remark varchar(200)
)
as 
    update Roles set Name=@Name,Remark=@Remark where ID=@ID

删除的4种方法

RbacDBEntities db = new RbacDBEntities();
            //方式1 Add
            Role r1 = db.Roles.Find(1074);
            db.Roles.Remove(r1);
            db.SaveChanges();
            //方式2
            Role r2 = new Role()
            {
                ID = 1075
            };
            db.Entry(r2).State = System.Data.Entity.EntityState.Deleted;
            db.SaveChanges();
            //方式3调用方sql
            string sql = @"delete roles  where id=1076";
            db.Database.ExecuteSqlCommand(sql);
            db.SaveChanges();
            //方式4，调用存储过程
            db.delete_cp(1077);
            Console.Read();

删除的存储过程

go
create proc delete_cp
(
    @ID int 
)
as 
    delete Roles where ID=@ID

查询的4种方法

RbacDBEntities db = new RbacDBEntities();
            //方式1 Add
            var roles1 = from r in db.Roles
                         where r.Name.Contains("主任")
                         select r;
            //方式2
            var roles2 = db.Roles
                .Where(p => p.Name.Contains("主任"))
                .Select(p => p).ToList();
            //方式3调用方sql
            string sql = @"select * from roles  where name like '%主任%'";
            db.Database.SqlQuery<Role>(sql);
            db.SaveChanges();
            //方式4，调用存储过程
            List<cp_select_role_Result> roles11 = db.cp_select_role("主任").ToList();
            Console.Read();
            db.delete_cp(11);
            db.SaveChanges();

查询的存储过程

go 
create proc cp_select_role_Result
(
    @Name varchar(50)
)
as 
    select * from Roles where Name like '%'+@Name+'%'