从零开始搭建前后端分离的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 和点个 下方的推荐哦~~谢谢！