從零開始搭建前後端分離的NetCore2.2（EF Core CodeFirst+Autofac）+Vue的項目框架之十資料庫基礎方法的封裝

　　每一個擁有資料庫的項目，都會涉及到資料庫數據的操作，而很多時候都會用到相同的方法，但是只是涉及到的表不一樣，如果不對這些類似方法進行封裝，開發上就會造成時間上的浪費。

　　那麼如何對這些方法進行封裝呢？

　　要會封裝方法，最基本的得先瞭解  泛型 是什麼，什麼是泛型，博客園上有很多對這個的講解，我也相信，科班的人對這個應該都有大概的瞭解，

　　其次得瞭解 反射，當然，很多人會說反射會影響性能，但是在如今設備都是很好的情況下，反射影響的性能微乎其微吧~

　　言歸正傳，說的再多不如實操，那麼我們先新建資料庫表的基類，並讓資料庫表類繼承它，這樣來約束泛型的類型只能是數據表對象，不能是其它類型，以此來避免不必要的錯誤！

    /// <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);
        }
    }

　　到這裡，根據每個資料庫表建對應的 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 和點個 下方的推薦哦~~謝謝！