MVC学习之路（1） EF 增删查改合集
首先再Model中创建一个类[WMBlogDB]
public class WMBlogDB : DbContext { //连接字符串。 public WMBlogDB() : base("name=WMBlogDB") { } //sysUserInfo.cs 是一个model里面有id name等等信息 public DbSet<sysUserInfo> sysUserInfos { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { //移除表明为复数 modelBuilder.Conventions.Remove<PluralizingTableNameConvention>(); // //自动添加实现EntityTypeConfiguration的类 modelBuilder.Configurations.AddFromAssembly(Assembly.GetExecutingAssembly()); base.OnModelCreating(modelBuilder); } //为您要在模型中包含的每种实体类型都添加 DbSet。有关配置和使用 Code First 模型 //的详细信息，请参阅 http://go.microsoft.com/fwlink/?LinkId=390109。 }
1】新增，网上有觉得十分良好的两种方式，先举例。
方式一 WMBlogDB db = new WMBlogDB(); sysUserInfo sos = new sysUserInfo() { Name = "Admin111", PWD = "123456", RealName = "超级管理员", }; //创建ef容器对象，并后去当前实体对象的状态管理对象 DbEntityEntry<sysUserInfo> entry = db.Entry<sysUserInfo>(sos); //添加 entry.State = EntityState.Added; //保存到数据库 db.SaveChanges() 方式二 using (WMBlogDB dbs = new WMBlogDB()) { sysUserInfo sys = new sysUserInfo(); Stopwatch st = new Stopwatch(); st.Start(); db.sysUserInfos.Add(new sysUserInfo { Name = "Admin111", PWD = "123456", RealName = "超级管理员", }); dbs.SaveChanges() } //其实这里可以不用using包裹，可以和方式一一样先new WMBlogDB，然后直接使用。 //这里使用using：只要离开了这个代码段就自动调用这个类实例的Dispose。 //要达到这样的目的，用try...catch来捕捉异常也是可以的，但用using也很方便
2】删除
1 #region 删除 2 //方式一 3 using (WMBlogDB db = new WMBlogDB()) 4 { 5 sysUserInfo us = new sysUserInfo() 6 { 7 8 uID = 68 9 }; 10 db.Entry<sysUserInfo>(us).State = EntityState.Deleted; 11 return Content(db.SaveChanges().ToString() + "ss"); 12 } 13 14 //方式二 15 using (WMBlogDB dbs = new WMBlogDB()) 16 { 17 List<sysUserInfo> list = dbs.sysUserInfos.Where(u => u.uLoginName == "Admin111" & u.uLoginPWD == "123456").ToList(); 18 //Any表示只要集合中有值就返回true 否者返回float 19 if (list != null && list.Any()) 20 { 21 foreach (sysUserInfo item in list) 22 { 23 dbs.sysUserInfos.Remove(item); 24 } 25 // dbs.SaveChanges(); 26 } 27 // int ss = dbs.SaveChanges(); 28 return Content(dbs.SaveChanges().ToString()); 29 } 30 //在执行代码期间，发现方式1只能通过id来删除，通过其他值则会报错。这点有人解决的话，感谢留言 31 #endregion
3】修改
#region 修改 //方法1 using (WMBlogDB dbs = new WMBlogDB()) { var model = dbs.sysUserInfos.FirstOrDefault(u => u.uLoginName == "Admin111"); model.uLoginName = "TS"; model.uLoginPWD = "123"; return Content(dbs.SaveChanges().ToString()); //这种方式一次只能修改一条数据 } //方法二 using (WMBlogDB dbs = new WMBlogDB()) { sysUserInfo model = new sysUserInfo() { uID =78, uRealName = "xiugai" }; //根据 uid寻找数据，然后uRealName是修改之后数据， //有点类似添加 //获取代理对象类为Detaceh //需要先引用 System.Data.Entity.Infrastructure DbEntityEntry entry = dbs.Entry(model); //1、将代理类的状态修改成 Unchanged //2、将代理类中的需要更新的字段的IsModified修改成true entry.State = EntityState.Unchanged; entry.Property("uRealName").IsModified = true; //解决对一个或多个实体验证失败 的方法：关闭EF的实体合法性检查 dbs.Configuration.ValidateOnSaveEnabled = false; return Content(dbs.SaveChanges().ToString() ); //这种方式目前也只能一次修改一条数据 } #endregion
4】查询，
ArrayList list = new ArrayList(); using (WMBlogDB dbs = new WMBlogDB()){ var ss = (from us in dbs.sysUserInfos orderby us.uID descending select us).ToList<sysUserInfo>(); string cw = ""; //通过foreach获取值， foreach (sysUserInfo sof in ss) { cw += sof.uLoginName + "|||"; list.Add(sof.uLoginName); } string name = list[1].ToString(); return Content(name);
5】：分页
using (thEntities db = new thEntities()) { if (zx == 0) { var ss = (from us in db.Users orderby us.ID descending select us).ToList<Users>(); uu = ss; } else { var ss = ((from us in db.Users orderby us.ID descending select us).Skip(num * (page - 1)).Take(num)).ToList<Users>(); uu = ss; } }
6】：join查询
方式一 using (thEntities1 db = new thEntities1()) { var sql = db.Users.Join(db.Study, u => u.LID, g => g.LIDs, (u, g) => new { name = u.Name, naem2 = g.Name, name3 = u.Password }); var list = sql.ToList(); return View(); } var sql = db.表1.Join(db.表2, u => u.表1匹配列名, g => g.表2匹配列名, (u, g) => new { name = u.表一列名, naem2 = g.表二列名, name3 = u.表一列名 }); 方式二 using (thEntities1 db = new thEntities1()) { var query = from d in db.Users join b in db.Study //注意，相连接的字段用equals连接 on d.LID equals b.LIDs select new { name1=b.Name, name2=b.LIDs, name3=b.Name }; var list = query.ToList(); return View(); }
7】：ef使用sql语句
方法一 using (thEntities1 db = new thEntities1()) { string SQLStr = string.Format("UPDATE Study SET Name=@name where IDs=@id "); SqlParameter[] p = new SqlParameter[]{ new SqlParameter("@id",5), new SqlParameter("@name","EGHTT") }; db.Database.ExecuteSqlCommand(SQLStr, p); } 方法二[方法1简化版本] using (thEntities1 db = new thEntities1()) { string value = "22"; string id = "22"; db.Database.ExecuteSqlCommand("UPDATE dbo.Storage SET Name ='" + value + "' WHERE ID='" + id + "'"); }
7.1】EF提高查询的方法AsNoTracking
using (thEntities1 db = new thEntities1()) { db.Users.AsNoTracking().Where(u => u.LID == 22).ToList(); }
7.2】
List<CodeV> SJO = db.Database.SqlQuery<CodeV>("SELECT * FROM [dbo].[CodeV] WHERE Name='" + txm + "'").ToList<CodeV>();
8】使用存储过程
首先是存储过程（事物） ALTER PROC [dbo].[AddItems] @txm VARCHAR(500), @num INT, @jsr VARCHAR(200), @people INT, @Msg VARCHAR(100) OUTPUT AS BEGIN BEGIN TRAN BEGIN TRY ---SQL操作语句 COMMIT END TRY BEGIN CATCH SET @Msg='失败！' ROLLBACK END CATCH END 然后是执行存储过程 List<SqlParameter> paramArry = new List<SqlParameter>(); paramArry.Add(new SqlParameter("@txm", txm)); paramArry.Add(new SqlParameter("@num", num)); paramArry.Add(new SqlParameter("@jsr", jsr)); paramArry.Add(new SqlParameter("@people", people)); SqlParameter param = new SqlParameter("@Msg", SqlDbType.VarChar, 100); param.Direction = ParameterDirection.Output; paramArry.Add(param); using (WHEntity db = new WHEntity()) { try { db.Database.ExecuteSqlCommand("EXEC [AddItems] @txm,@num,@jsr,@people,@Msg OUT", paramArry.ToArray()); Msg = paramArry[4].Value.ToString(); } catch (Exception ex) { string gg = ex.ToString(); string sss = ex.Message; Msg=sss +"22222"+ gg; } }
9】Other
聚合函数 Average、Count、Max、Min、Sum using(thEntities1 db=new thEntities1()){ var li=db.User.Max(p=>p.Num); }
10】补充
控制器中返回json格式
JsonConvert.SerializeObject(new { code = 0, msg = "", count = numss, data = dr });
// JsonConvert.SerializeObject(new { 参数1= 值1, 参数2= 值2});
之后遇到新的知识将会在此补充
