Linq To SQL 统一增删改查方法
/// <summary> /// 统一增删改查//2019-8-30 sjh Add /// </summary> public class BaseService //<T> where T : class, new() { private readonly SiteCmsDataContext dbContext = ContextFactory.GetCurrentDbContext(); /// <summary> /// 保存实体类 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <returns></returns> public bool SaveEntity<T>(T t) where T : class { ValidateType<T>(); dbContext.GetTable(typeof(T)).InsertOnSubmit(t); return SubmitToDB(); } /// <summary> /// 保存实体类集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <returns></returns> public bool SaveEntities<T>(IList<T> list) where T : class { ValidateType<T>(); dbContext.GetTable(typeof(T)).InsertAllOnSubmit(list); return SubmitToDB(); } /// <summary> /// 更新实体类 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <returns></returns> public bool UpdateEntity<T>(T t) where T : class { ValidateType<T>(); return SubmitToDB(); } /// <summary> /// 删除实体类,注意只能用于ProjectManagement库中的表实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="t"></param> /// <returns></returns> public bool DeleteEntity<T>(T t) where T : class { ValidateType<T>(); dbContext.GetTable(typeof(T)).DeleteOnSubmit(t); return SubmitToDB(); } /// <summary> /// 删除实体类集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="list"></param> /// <returns></returns> public bool DeleteEntities<T>(IList<T> list) where T : class { ValidateType<T>(); foreach (T t in list) dbContext.GetTable(typeof(T)).DeleteOnSubmit(t); return SubmitToDB(); } /// <summary> /// 根据实体Id获取实体对象 /// </summary> /// <typeparam name="T">实体类型</typeparam> /// <param name="id">实体Id</param> /// <returns>实体对象</returns> public T GetEntityById<T>(int id) where T : class { ValidateType<T>(); MetaTable table = dbContext.Mapping.GetTable(typeof(T)); var key = table.RowType.DataMembers.Single<MetaDataMember>(c => c.IsPrimaryKey).MappedName; if (string.IsNullOrEmpty(key)) return default(T); //这里也可以换成return dbContext.GetTable(typeof(T)).Where(key+"="+id).SingleOrDefault(); string sql = "select * from " + table.TableName + " where " + key + " = " + "{0}"; Object[] param = new Object[1] { id }; return dbContext.ExecuteQuery<T>(sql, param).SingleOrDefault(); } /// <summary> /// 根据实体以及拉姆达表达式获取单个实体 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate">lambda表达式</param> /// <returns>实体列表</returns> public T GetEntitiesByAll<T>(Expression<Func<T, bool>> predicate) where T : class { ValidateType<T>(); return dbContext.GetTable<T>().Where(predicate).FirstOrDefault(); } /// <summary> /// 根据实体以及拉姆达表达式获取数据列表 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="predicate">lambda表达式</param> /// <returns>实体列表</returns> public IList<T> GetEntities<T>(Expression<Func<T, bool>> predicate) where T : class { ValidateType<T>(); return dbContext.GetTable<T>().Where(predicate).ToList(); } /// <summary> /// 验证泛型实体的类型 /// </summary> /// <typeparam name="T">泛型类型</typeparam> private void ValidateType<T>() { Type type = typeof(T); if (type.FullName != dbContext.GetTable(type).ElementType.FullName) throw new ArgumentException("传入的泛型对象类型不属于Data实体"); } /// <summary> /// 将添加、删除、修改的命令提交至数据库执行 /// </summary> /// <returns>执行是否成功</returns> private bool SubmitToDB() { bool success = false; try { dbContext.SubmitChanges(ConflictMode.FailOnFirstConflict); success = true; } catch (Exception err) { //做异常日志记录,暂时先不用 //Log.Error(err.Message + err.StackTrace); success = false; } return success; } /// <summary> /// linq to sql以事务的方式执行,需要引用.NETFramework里面的System.Transactions.dll /// </summary> /// <returns>TransactionScope 创造一个事务块</returns> //public bool Transaction() //{ // using (TransactionScope ts = new TransactionScope()) // { // try // { // Product prod1 = db.Products.First(p => p.ProductID == 4); // Product prod2 = db.Products.First(p => p.ProductID == 5); // prod1.UnitsInStock -= 3; // prod2.UnitsInStock -= 5; // db.SubmitChanges(); // ts.Complete(); // } // catch (Exception e) // { // Console.WriteLine(e.Message); // } // } //}
ContextFactory.GetCurrentDbContext()
/// <summary> /// 当前线程内的数据库上下文 线程内DbContext实例唯一 /// </summary> /// <returns></returns> public static SiteCmsDataContext GetCurrentDbContext() { var dbContext = CallContext.GetData("SiteCmsDataContext") as SiteCmsDataContext; if (dbContext == null) { dbContext = new SiteCmsDataContext(); CallContext.SetData("SiteCmsDataContext", dbContext); //保证线程内唯一 } return dbContext; }
另外注释说明一下,
我们用Reflector打开DataContext,可以看到在其Dispose方法中调用了SqlProvider的Dispose方法。而SqlProvider.Dispose方法主要任务是关闭数据库连接(调用SqlConnectionManager.DisposeConnection方法,再跟下去可以发现实际上最终是调用了SqlConnection.Close方法),剩下的无论是在DataContext还是SqlProvider的Dispose方法中,都只是将一些对象置为null。
而DataContext其实已经将数据连接的打开和关闭管理得井井有条了。在DataContext.SubmitChanges方法中可以看到,在finally块中调用了SqlConnection.Close方法。而负责执行查询的DataContext.ExecuteQuery方法跟踪到最后也会在SqlProvider.Execute方法的finally块中调用SqlConnectionManager的ReleaseConnection方法。也就是说在我们手动Dispose DataContext的时候,其实主要工作早已经执行完了,剩下的只是清空一些对象所占用的内存,使它们尽早被GC回收。除此之外,DataContext.Dispose再没有其他好处了。并且我们会发现SqlConnectionManager的ReleaseConnection方法所执行的内容甚至比CloseConnection还要多。
因此我们得出结论,在使用LINQ to SQL时,完全没有必要手动执行DataContext的Dispose方法,更没有必要使用using语句。
参考文章地址:https://www.cnblogs.com/kirinboy/archive/2010/01/04/dispose-datacontext-or-not.html