Entity Framework Code Firest 连接Sqlserver数据库（增删改查）
一、添加Nuget包：EntityFramework
二、在App.config文件添加链接Sqlserver字符串
<connectionStrings>
<add name="MyStrConn" connectionString="server=Localhost;uid=sa;pwd=123456;database=WareHouseDemo;" providerName="System.Data.SqlClient"/>
</connectionStrings>
三、添加模型类
新建LocationInfo类并写入下面代码
[Table("LocationInfo")] class LocationInfo { [Key] public int LocationId { get; set; }//库位号 public string Type { get; set; }//种类 public int Number { get; set; }//数量 public string Material { get; set; }//材质 public string RFIDNumber { get; set; }//料盘的RFID号 //定义无参数的构造函数主要是因为在通过DbSet获取对象进行linq查询时会报错 //The class 'EFCodeFirstModels.Student' has no parameterless constructor. public LocationInfo() { } public LocationInfo(int locationId, string type, int number, string material, string rfidNumber) { this.LocationId = locationId; this.Type = type; this.Number = number; this.Material = material; this.RFIDNumber = rfidNumber; } }
四、添加数据库上下文
新建EFCodeFirstDbContext类并写入下面代码
class EFCodeFirstDbContext : DbContext { public EFCodeFirstDbContext() : base("name=MyStrConn") { } protected override void OnModelCreating(DbModelBuilder modelBuilder) { } public DbSet<LocationInfo> LocationInfos { get; set; } }
五、进行增删改查操作
在Program类中写入下面代码
class Program { static void Main(string[] args) { //using能及时释放资源,例如数据库连接异常，可以即使将上下文释放 using (var db = new EFCodeFirstDbContext()) { //增------------------------------------------------------------------------------ LocationInfo locationInfo1 = new LocationInfo(1, "empty", 0, "empty", "00010000"); LocationInfo locationInfo2 = new LocationInfo(2, "empty", 0, "empty", "00020000"); LocationInfo locationInfo3 = new LocationInfo(3, "empty", 0, "empty", "00030000"); LocationInfo locationInfo4 = new LocationInfo(4, "empty", 0, "empty", "00040000"); LocationInfo locationInfo5 = new LocationInfo(5, "empty", 0, "empty", "00050000"); LocationInfo locationInfo6 = new LocationInfo(6, "empty", 0, "empty", "00060000"); LocationInfo locationInfo7 = new LocationInfo(7, "empty", 0, "empty", "00070000"); LocationInfo locationInfo8 = new LocationInfo(8, "empty", 0, "empty", "00080000"); LocationInfo locationInfo9 = new LocationInfo(9, "empty", 0, "empty", "00090000"); LocationInfo locationInfo10 = new LocationInfo(10, "empty", 0, "empty", "000A0000"); db.LocationInfos.Add(locationInfo1); db.LocationInfos.Add(locationInfo2); db.LocationInfos.Add(locationInfo3); db.LocationInfos.Add(locationInfo4); db.LocationInfos.Add(locationInfo5); db.LocationInfos.Add(locationInfo6); db.LocationInfos.Add(locationInfo7); db.LocationInfos.Add(locationInfo8); db.LocationInfos.Add(locationInfo9); db.LocationInfos.Add(locationInfo10); db.SaveChanges(); //查------------------------------------------------------------------------------ //StringBuilder stringBuilder = new StringBuilder(); //foreach (var item in db.LocationInfos) //{ // stringBuilder.AppendFormat( // "库位号：{0}\t物料类型：{1}\t物料数量：{2}\t物料材质：{3}\tRFID号：{4}\n", // item.LocationId, item.Type, item.Number, item.Material, item.RFIDNumber); //} //Console.WriteLine(stringBuilder); //查某条数据(1) /* var query = from q in db.LocationInfos where q.LocationId == 5 select q; StringBuilder stringBuilder = new StringBuilder(); foreach (var item in query) { stringBuilder.AppendFormat( "库位号：{0}\t物料类型：{1}\t物料数量：{2}\t物料材质：{3}\tRFID号：{4}\n", item.LocationId, item.Type, item.Number, item.Material, item.RFIDNumber); } Console.WriteLine(stringBuilder); */ //查某条数据(2) /* var query = db.LocationInfos.Where<LocationInfo>(c => c.LocationId == 5); StringBuilder stringBuilder = new StringBuilder(); foreach (var item in query) { stringBuilder.AppendFormat( "库位号：{0}\t物料类型：{1}\t物料数量：{2}\t物料材质：{3}\tRFID号：{4}\n", item.LocationId, item.Type, item.Number, item.Material, item.RFIDNumber); } Console.WriteLine(stringBuilder); */ //改------------------------------------------------------------------------------ /* LocationInfo locationInfo = new LocationInfo() { LocationId = 5 }; locationInfo = db.LocationInfos.Attach(locationInfo); locationInfo.Type = "bottle"; locationInfo.Number = 4; locationInfo.Material = "Sn"; locationInfo.RFIDNumber = "00050101"; db.SaveChanges(); */ //删------------------------------------------------------------------------------ /* LocationInfo locationInfo = db.LocationInfos.Where(p => p.LocationId == 9).FirstOrDefault(); db.LocationInfos.Remove(locationInfo); db.SaveChanges(); */ } Console.ReadKey(); } }
六、补充Tools
public class EFCodeFirstDbContext : DbContext { public EFCodeFirstDbContext() : base("name=MyStrConn") { } public DbSet<UserInfo> UserInfo { get; set; } #region 实例化数据库上下文 /// <summary> /// 实例化数据库上下文 /// </summary> /// <returns></returns> private EFCodeFirstDbContext GetEFContxt() { try { return new EFCodeFirstDbContext(); } catch (Exception) { throw; } } #endregion #region 查 /// <summary> /// 获取所有数据 /// </summary> /// <typeparam name="T">类</typeparam> /// <returns>泛型类</returns> public List<T> GetAllEneity<T>() where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); return context.Set<T>().ToList(); } catch (Exception) { throw; } } /// <summary> /// 获取所有数据(Lambda表达式根据某个字段倒叙) /// </summary> /// <typeparam name="T">类</typeparam> /// <typeparam name="Tkey">排序字段的类型</typeparam> /// <param name="orderbyExp">排序字段</param> /// <returns>泛型类</returns> public List<T> GetAllEneity<T, Tkey>(Expression<Func<T, Tkey>> orderbyExp) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); return context.Set<T>().OrderByDescending(orderbyExp).ToList(); } catch (Exception) { throw; } } /// <summary> /// 按条件查询（Lambda表达式） /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="whereExp">条件</param> /// <returns>泛型类</returns> public List<T> GetEntityByWhere<T>(Expression<Func<T, bool>> whereExp) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); List<T> list = context.Set<T>().Where(whereExp).ToList(); return list; } catch (Exception) { return null; } } /// <summary> /// 按条件查询（Lambda表达式） /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="whereExp">条件</param> /// <returns>类</returns> public T GetSingleEntity<T>(Expression<Func<T, bool>> whereExp) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); // 第一种 //T t = db.Set<T>().SingleOrDefault(whereExp); //第二种 //T t = db.Set<T>().Where(whereExp).SingleOrDefault(); //第三种 // db.Set<T>().FirstOrDefault(whereExp); //第四种 T t = context.Set<T>().Where(whereExp).FirstOrDefault(); return t; } catch (Exception) { throw; } } /// <summary> /// 分页查询 /// </summary> /// <typeparam name="T">要查询的实体</typeparam> /// <typeparam name="Tkey">排序字段的类型</typeparam> /// <param name="pageIndex">当前要查询的页标</param> /// <param name="pageCount">每页显示的行数</param> /// <param name="orderbyExp">排序字段</param> /// <param name="whereExp">条件</param> /// <returns></returns> public List<T> GetEntityByPage<T, Tkey>(int pageIndex, int pageCount, Expression<Func<T, Tkey>> orderbyExp, Expression<Func<T, bool>> whereExp) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); if (whereExp == null) { return context.Set<T>().OrderBy(orderbyExp).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList(); } else { return context.Set<T>().OrderBy(orderbyExp).Where(whereExp).Skip((pageIndex - 1) * pageCount).Take(pageCount).ToList(); } } catch (Exception) { throw; } } /// <summary> /// 分页总数据量 /// </summary> /// <returns></returns> public int GetEntityByPage<T>(Expression<Func<T, bool>> whereExp) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); if (whereExp == null) { return context.Set<T>().ToList().Count; } else { return context.Set<T>().Where(whereExp).ToList().Count; } } catch (Exception) { return 0; } } #endregion #region 增 /// <summary> /// 添加数据（类） /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="t">新类</param> /// <returns>bool</returns> public bool AddEntity<T>(T t) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); context.Set<T>().Add(t); return context.SaveChanges() > 0; } catch (Exception) { return false; } } /// <summary> /// 添加数据（泛型类） /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="t">泛型类</param> /// <returns>bool</returns> public bool AddEntity<T>(List<T> tList) where T : class { try { if (tList.Count == 0) { return true; } EFCodeFirstDbContext context = GetEFContxt(); tList.ForEach(t => { context.Set<T>().Add(t); }); return context.SaveChanges() > 0; } catch (Exception) { return false; } } #endregion #region 删 /// <summary> /// 删除单个对象（类） /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="t">新类</param> /// <returns></returns> public bool Reomve<T>(T t) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); context.Set<T>().Attach(t); context.Set<T>().Remove(t); return context.SaveChanges() > 0; } catch (Exception) { return false; } } /// <summary> /// 删除单个对象（泛型类） /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="tList">泛型类</param> /// <returns>bool</returns> public bool Reomve<T>(List<T> tList) where T : class { try { if (tList.Count == 0) { return true; } EFCodeFirstDbContext context = GetEFContxt(); tList.ForEach(t => { context.Set<T>().Attach(t); context.Set<T>().Remove(t); }); return context.SaveChanges() > 0; } catch (Exception) { return false; } } /// <summary> /// 按条件删除（Lambda表达式） /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="whereExp">条件</param> /// <returns>bool</returns> public bool Reomve<T>(Expression<Func<T, bool>> whereExp) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); T t = context.Set<T>().Where(whereExp).FirstOrDefault(); context.Set<T>().Remove(t); return context.SaveChanges() > 0; } catch (Exception) { return false; } } #endregion #region 改 /// <summary> /// 修改数据（类） /// </summary> /// <typeparam name="T">类</typeparam> /// <param name="t">新类</param> /// <returns>bool</returns> public bool UpdateEntity<T>(T t) where T : class { try { EFCodeFirstDbContext context = GetEFContxt(); context.Set<T>().Attach(t); context.Entry(t).State = EntityState.Modified; return context.SaveChanges() > 0; } catch (Exception) { return false; } } /// <summary> /// 修改数据（泛型类） /// </summary> /// <typeparam name="T"></typeparam> /// <param name="tList">泛型类</param> /// <returns>bool</returns> public bool UpdateEntity<T>(List<T> tList) where T : class { try { if (tList.Count == 0) { return true; } EFCodeFirstDbContext context = GetEFContxt(); tList.ForEach(t => { context.Set<T>().Attach(t); context.Entry(t).State = EntityState.Modified; }); return context.SaveChanges() > 0; } catch (Exception) { return false; } } #endregion
源代码：
链接：https://pan.baidu.com/s/1zj9lvwgVxmgh2mbskCZdZA
提取码：kszh
后续会陆续更新其他资料,喜欢请关注哦！
