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