每一個擁有資料庫的專案，都會涉及到資料庫資料的操作，而很多時候都會用到相同的方法，但是只是涉及到的表不一樣，如果不對這些類似方法進行封裝，開發上就會造成時間上的浪費，
那么如何對這些方法進行封裝呢？
要會封裝方法，最基本的得先了解 泛型 是什么，什么是泛型，博客園上有很多對這個的講解，我也相信，科班的人對這個應該都有大概的了解，
其次得了解 反射，當然，很多人會說反射會影響性能，但是在如今設備都是很好的情況下，反射影響的性能微乎其微吧~
言歸正傳，說的再多不如實操，那么我們先新建資料庫表的基類，并讓資料庫表類繼承它，這樣來約束泛型的型別只能是資料表物件，不能是其它型別，以此來避免不必要的錯誤！
/// <summary> /// 資料庫基類 /// </summary> public abstract class EntityBase : IEntityBase { }
這里的 IEntityBase 是前面第二篇中用到的一個空的介面基類，在這個抽象基類中，可以添加欄位，這樣繼承該基類的資料庫表都會加上這些欄位，比如 創建時間、創建人等欄位
因為這里會涉及到分頁模型的因素，先新建泛型的分頁模型類 PageResponse
/// <summary> /// 分頁模型 /// </summary> /// <typeparam name="T"></typeparam> public class PageResponse<T> { private long _recordTotal; /// <summary> /// 當前頁碼 /// </summary> public int PageIndex { get; set; } /// <summary> /// 總頁數 /// </summary> public int PageTotal { get; set; } = 1; /// <summary> /// 每頁大小 /// </summary> public int PageSize { get; set; } /// <summary> /// 總記錄數 /// </summary> public long RecordTotal { get => _recordTotal; set { _recordTotal = value; if (PageSize <= 0) return; PageTotal = (int)Math.Ceiling(RecordTotal / (double)PageSize); } } public List<T> Data { get; set; } public PageResponse() { Data = new List<T>(); } public PageResponse(List<T> data, int pageIndex, int pageTotal) { Data = data; PageIndex = pageIndex; PageTotal = pageTotal; } }
接下來我們新建一個資料庫工廠類 來 進行 生產資料庫背景關系，代碼如下
/// <summary> /// 資料庫工廠 /// </summary> public class DbContextFactory { /// <summary> /// 資料庫背景關系 /// </summary> /// <returns></returns> public static DemoDbContext GetCurrentDbContext() { if (DemoWeb.HttpContext.Items["DbContext"] is DemoDbContext dbContext) return dbContext; dbContext = DemoWeb.IocManager.Resolve<DemoDbContext>();//從容器中得到資料庫背景關系 放置在 Items 中， 訪問結束自動銷毀 //dbContext = DemoWeb.HttpContext.RequestServices.GetService(typeof(DemoDbContext)) as DemoDbContext; DemoWeb.HttpContext.Items["DbContext"] = dbContext; return dbContext; } }
因為這里使用的是autofac模式，所以這樣獲取，至于為什么放到items中，也有簡單的原因講到，
再然后新建 IBaseDao 介面檔案，代碼如下：
public interface IBaseDao<T> { T Add(T entity); List<T> Add(List<T> entity); void Delete(params object[] keyValues); void Delete(object objectId); void Delete(Expression<Func<T, bool>> whereFun); void Update(T entity); void Update(Expression<Func<T, bool>> where, Dictionary<string, object> dic); bool Exist(Expression<Func<T, bool>> anyLambda); T Find(params object[] keyValues); IQueryable<T> Where(Expression<Func<T, bool>> whereLambda); T FirstOrDefault(Expression<Func<T, bool>> whereLambda); int Count(Expression<Func<T, bool>> countLambda); T First(Expression<Func<T, bool>> firstLambda); IQueryable<T> LoadEntities(Expression<Func<T, bool>> whereLambda = null); List<T> LoadPageEntities<TKey>(int pageIndex, int pageSize, out int totalCount, out int pageCount, Expression<Func<T, bool>> whereLambda, bool isAsc, Expression<Func<T, TKey>> orderBy); PageResponse<T> LoadPageEntities<TKey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, bool isAsc, Expression<Func<T, TKey>> orderBy); IQueryable<TQ> LoadPageEntities<TQ, TKey>(IQueryable<TQ> query, int pageIndex, int pageSize, out int totalCount, out int pageCount, bool isAsc, Expression<Func<TQ, TKey>> orderBy) where TQ : class, new(); PageResponse<TQ> LoadPageEntities<TQ, TKey>(IQueryable<TQ> query, int pageIndex, int pageSize, bool isAsc, Expression<Func<TQ, TKey>> orderBy) where TQ : class, new(); int SaveChanges(); }
實作介面的類，代碼如下： 代碼有點長~~所以就折疊了~~
/// <summary> /// 資料庫基類 /// </summary> /// <typeparam name="T"></typeparam> public class BaseDao<T> : IBaseDao<T> where T : EntityBase, new() { public DemoDbContext DbContext => DbContextFactory.GetCurrentDbContext(); public BaseDao() { //DbContext = DbContextFactory.GetCurrentDbContext(); } #region 增刪改的公共方法 public T Add(T entity) { DbContext.Set<T>().Add(entity); //DbContext.Entry(entity).State = EntityState.Added; return entity; } public List<T> Add(List<T> entitys) { DbContext.Set<T>().AddRange(entitys); //注釋掉下面的快許多 且不影響保存 //foreach (var model in entitys) //{ // DbContext.Entry(model).State = EntityState.Added; //} return entitys; } public void Delete(Expression<Func<T, bool>> whereFun) { IEnumerable<T> queryable = DbContext.Set<T>().Where(whereFun); //DbContext.Set<T>().RemoveRange(queryable); foreach (var model in queryable) { DbContext.Entry(model).State = EntityState.Deleted; } } public void Update(T entity) { DbContext.Entry(entity).State = EntityState.Modified; } public void Update(Expression<Func<T, bool>> @where, Dictionary<string, object> dic) { IEnumerable<T> queryable = DbContext.Set<T>().Where(@where).ToList(); Type type = typeof(T); List<PropertyInfo> propertyList = type.GetProperties(BindingFlags.Public | BindingFlags.Instance).ToList(); //遍歷結果集 foreach (T entity in queryable) { foreach (var propertyInfo in propertyList) { string propertyName = propertyInfo.Name; if (dic.ContainsKey(propertyName)) { //設定值 propertyInfo.SetValue(entity, dic[propertyName], null); } } Update(entity); } } public void Delete(params object[] keyValues) { var entity = DbContext.Set<T>().Find(keyValues); DbContext.Entry(entity).State = EntityState.Deleted; } public void Delete(object objectId) { var entity = DbContext.Set<T>().Find(objectId); DbContext.Entry(entity).State = EntityState.Deleted; } #endregion #region 查詢方法 /// <summary> /// 查看是否存在 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="anyLambda"></param> /// <returns></returns> public bool Exist(Expression<Func<T, bool>> anyLambda) { return DbContext.Set<T>().Any(anyLambda); } /// <summary> /// 根據主鍵得到資料 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="keyValues"></param> /// <returns></returns> public T Find(params object[] keyValues) { return DbContext.Set<T>().Find(keyValues); } /// <summary> /// 根據where條件查找 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="whereLambda"></param> /// <returns></returns> public IQueryable<T> Where(Expression<Func<T, bool>> whereLambda) { return DbContext.Set<T>().Where(whereLambda); } /// <summary> /// 獲取第一個或默認為空 /// </summary> /// <param name="whereLambda"></param> /// <returns></returns> public T FirstOrDefault(Expression<Func<T, bool>> whereLambda) { return DbContext.Set<T>().FirstOrDefault(whereLambda); } /// <summary> /// 得到條數 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="countLambda"></param> /// <returns></returns> public int Count(Expression<Func<T, bool>> countLambda) { return DbContext.Set<T>().AsNoTracking().Count(countLambda); } /// <summary> /// 獲取第一個或默認的 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="firstLambda"></param> /// <returns></returns> public T First(Expression<Func<T, bool>> firstLambda) { return DbContext.Set<T>().FirstOrDefault(firstLambda); } /// <summary> /// 得到IQueryable資料 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="whereLambda"></param> /// <returns></returns> public IQueryable<T> LoadEntities(Expression<Func<T, bool>> whereLambda = null) { if (whereLambda == null) { return DbContext.Set<T>().AsQueryable(); } return DbContext.Set<T>().Where(whereLambda).AsQueryable(); } /// <summary> /// 從某個表中獲取分頁資料 /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="TKey"></typeparam> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="totalCount"></param> /// <param name="pageCount"></param> /// <param name="whereLambda"></param> /// <param name="isAsc"></param> /// <param name="orderBy"></param> /// <returns></returns> public List<T> LoadPageEntities<TKey>(int pageIndex, int pageSize, out int totalCount, out int pageCount, Expression<Func<T, bool>> whereLambda, bool isAsc, Expression<Func<T, TKey>> orderBy) { var temp = DbContext.Set<T>().AsNoTracking().Where(whereLambda); //去掉.AsQueryable().AsNoTracking()，將下面改為 totalCount = temp.Count(); pageCount = (int)Math.Ceiling((double)totalCount / pageSize); if (isAsc) { return temp.OrderBy(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize).ToList(); //去掉.AsQueryable()，添加.select(t=>new Dto()).ToList() } return temp.OrderByDescending(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize).ToList(); //.select(t=>new Dto()).ToList() } /// <summary> /// 回傳分頁模型 /// </summary> /// <typeparam name="T"></typeparam> /// <typeparam name="TKey"></typeparam> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="whereLambda"></param> /// <param name="isAsc"></param> /// <param name="orderBy"></param> /// <returns></returns> public PageResponse<T> LoadPageEntities<TKey>(int pageIndex, int pageSize, Expression<Func<T, bool>> whereLambda, bool isAsc, Expression<Func<T, TKey>> orderBy) { var temp = DbContext.Set<T>().AsNoTracking().Where(whereLambda); var rest = new PageResponse<T>(); rest.PageIndex = pageIndex; rest.PageSize = pageSize; rest.RecordTotal = temp.Count();//記錄總條數時，自動設定了總頁數 if (isAsc) { rest.Data = temp.OrderBy(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize).ToList(); } rest.Data = temp.OrderByDescending(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize).ToList(); return rest; } /// <summary> /// 將查詢出來的資料 轉換成IQueryable，然后進行分頁 不跟蹤資料狀態 /// </summary> /// <typeparam name="TQ">回傳型別</typeparam> /// <typeparam name="TKey">根據哪個欄位排序（必須）</typeparam> /// <param name="query">資料集</param> /// <param name="pageIndex">頁數</param> /// <param name="pageSize">每頁條數</param> /// <param name="totalCount">總條數</param> /// <param name="pageCount">總頁數</param> /// <param name="isAsc">是否倒序</param> /// <param name="orderBy">排序欄位</param> /// <returns>IQueryable分頁結果</returns> public IQueryable<TQ> LoadPageEntities<TQ, TKey>(IQueryable<TQ> query, int pageIndex, int pageSize, out int totalCount, out int pageCount, bool isAsc, Expression<Func<TQ, TKey>> orderBy) where TQ : class, new() { IQueryable<TQ> temp = query.AsNoTracking(); totalCount = temp.Count(); pageCount = (int)Math.Ceiling((double)totalCount / pageSize); if (isAsc) { temp = temp.OrderBy(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize).AsQueryable(); } else { temp = temp.OrderByDescending(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize).AsQueryable(); } return temp; } /// <summary> /// 將查詢出來的資料 轉換成IQueryable，然后進行分頁 不跟蹤資料狀態 /// </summary> /// <typeparam name="TQ">回傳型別</typeparam> /// <typeparam name="TKey">根據哪個欄位排序（必須）</typeparam> /// <param name="query">資料集</param> /// <param name="pageIndex">頁數</param> /// <param name="pageSize">每頁條數</param> /// <param name="isAsc">是否倒序</param> /// <param name="orderBy">排序欄位</param> /// <returns>PageResponse分頁結果</returns> public PageResponse<TQ> LoadPageEntities<TQ, TKey>(IQueryable<TQ> query, int pageIndex, int pageSize, bool isAsc, Expression<Func<TQ, TKey>> orderBy) where TQ : class, new() { var rest = new PageResponse<TQ>(); IQueryable<TQ> temp = query.AsNoTracking(); rest.RecordTotal = temp.Count(); if (isAsc) { rest.Data = temp.OrderBy(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize).ToList(); } else { rest.Data = temp.OrderByDescending(orderBy) .Skip(pageSize * (pageIndex - 1)) .Take(pageSize).ToList(); } return rest; } #endregion /// <summary> /// 自帶事務，呼叫此方法保存 /// </summary> public int SaveChanges() { var res = -1; try { res = DbContext.SaveChanges(); //Dispose(); } catch (DbException ex) { throw new CustomSystemException($"資料庫保存失敗!{ex.Message}", 999); } catch (Exception ex) { throw new CustomSystemException($"資料庫保存失敗!{ex.Message}", 999); } return res; } public void Dispose() { this.DbContext.Dispose(); GC.SuppressFinalize(this); } }BaseDao
到這里，根據每個資料庫表建對應的 Dao 類，這樣一來開發效率就會明顯提升，示例代碼如下：
public class DemoModelDao : BaseDao<DemoModel> { private static object locker = new object(); private static DemoModelDao _demoModelDao; public static DemoModelDao Instance { get { if (_demoModelDao != null) return _demoModelDao; lock (locker) { if (_demoModelDao == null) { _demoModelDao = new DemoModelDao(); } } return _demoModelDao; } } /// <summary> /// 得到分頁資料 /// </summary> /// <param name="queryDemo"></param> /// <returns></returns> public PageResponse<DemoModel> DemoPageResponse(QueryDemoDto queryDemo) { var date = LoadPageEntities(queryDemo.Page, queryDemo.PageSize, c => c.CustomerName.Contains(queryDemo.Name), false, c => c.Id); return date; } }
然后添加測驗方法，添加 Biz 類，呼叫測驗
public class DemoModelBiz { private static object locker = new object(); private static DemoModelBiz _demoModelBiz; public static DemoModelBiz Instance { get { if (_demoModelBiz != null) return _demoModelBiz; lock (locker) { if (_demoModelBiz == null) { _demoModelBiz = new DemoModelBiz(); } } return _demoModelBiz; } } public string AddDemoModel(DemoModel demoModel) { DemoModelDao.Instance.Add(demoModel); var count = DemoModelDao.Instance.SaveChanges(); return count > 0 ? "success" : "save error"; } public string AddDemoModel(List<DemoModel> demoModels) { DemoModelDao.Instance.Add(demoModels); DemoModelDao.Instance.Delete(c=>c.Id == 1); DemoModelDao.Instance.Delete(c=>c.CustomerName.StartsWith("2")); TestModelDao.Instance.Add(new TestModel() { BlogName = "NET CORE", BlogPhone = 123, BlogUseDay = 90 }); var count = DemoModelDao.Instance.SaveChanges(); return count > 0 ? "success" : "save error"; } /// <summary> /// 得到分頁資料 /// </summary> /// <param name="queryDemo"></param> /// <returns></returns> public PageResponse<DemoModel> DemoModelList(QueryDemoDto queryDemo) { return DemoModelDao.Instance.DemoPageResponse(queryDemo); } }
再添加測驗的控制器類，示例代碼如下：
[Route("api/[controller]")] public class DemoModelController : BaseController { [Route("testadd"), HttpPost] public async Task<ActionResult> AddDemoModel() { var models = new List<DemoModel>(); for (int i = 0; i < 100; i++) { var testModel = new DemoModel() { CustomerName = i +"-Levy" + DateTime.Now.ToString("HH:mm:ss"), IdentityCardType = 1 }; models.Add(testModel); } for (int i = 0; i < 100; i++) { var testModel = new DemoModel() { CustomerName = i + "-zzzz" + DateTime.Now.ToString("HH:mm:ss"), IdentityCardType = 2 }; models.Add(testModel); } var res = await Task.FromResult(DemoModelBiz.Instance.AddDemoModel(models)); return Succeed(res); } [Route("demolist"), HttpPost] public async Task<ActionResult> DemoModelList([FromBody] QueryDemoDto queryDemo) { var res = await Task.FromResult(DemoModelBiz.Instance.DemoModelList(queryDemo)); return Succeed(res); } }
涉及到的類：
public class QueryDemoDto { public int Page { get; set; } public int PageSize { get; set; } public string Name { get; set; } }
接下來就運行程式除錯看結果吧~
這里資料保存成功之后我們進行資料的查詢，
可以看到查詢出結果，這里有兩千多條資料，是因為執行了多次且每次保存前都會洗掉以2開始的資料，
題外話，因為我們是將資料庫背景關系放在 HttpContext.Items 中的，可能有些人會擔心程式運行完后會不釋放，從而導致資料庫鏈接過多而出現崩潰，
首先呢，訪問結束時 HttpContext.Items 就會銷毀，也就意味著資料庫鏈接也就銷毀了，
如果還是不放心，可以在方法執行完成時，將資料庫鏈接手動釋放，
首先在工廠類中加上
/// <summary> /// 釋放DBContext物件 /// </summary> public static void DisposeDbContext() { if (DemoWeb.HttpContext.Items.ContainsKey("DbContext")) { DemoWeb.HttpContext.Items.Remove("DbContext"); } }
然后不管程式正常執行完成，還是遇到例外，都會走控制器的 OnActionExecuted 方法，因此可以多載這個方法，然后呼叫釋放方法 DbContextFactory.DisposeDbContext();
以上若有什么不對或可以改進的地方，望各位指出或提出意見，一起探討學習~
有需要原始碼的可通過此 GitHub 鏈接拉取 覺得還可以的給個 start 和點個 下方的推薦哦~~謝謝！
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/94936.html
標籤：.NET Core