小白开学Asp.Net Core《二》_.net

小白开学asp.net core《二》

　　——数据仓储层（repositroy）

一、历史现象

　　在后端开发中，数据库操作是最频繁的，每一个开发人员都会接触，甚至不少开发人员每天的工作就是与数据库打交道。所以可见数据库操作是多长重要。

　　在现在的开发过程中，大多数开发人员只是以编写sql语句的方式操作数据库，这种方式是操作数据库最原始的方式，简单高效，但是在编写sql语句的过程中，极容易因粗心大意写出bug，这样就会出现一种现象，开发人员面对一堆sql语句的debug，而且每次都需要开发人员自己去手写sql语句，其开发效率极低。不同的数据库所需的sql语句也还是有差异的，这就需要开发人员学习不同的数据库sql语法。而且在项目开发中难免会遇到更换数据库的情况，这时还需要花费大量的精力去修改sql语句。

二、数据仓储层

　　由于以上缺点，所以仓储层因此而生。

　　数据仓储层主要是对数据库操作crud的封装。使开发人员进行crud只需要极为简单的代码即可完成。本层还提供了数据库事务的支持，为数据库操作提供必备的保障。使用本层提供的接口，无需关心具体的业务逻辑实现，哪怕更换数据库，也无需更改业务逻辑代码，只需要更改简单的数据库配置即可。总之，本层为开发人员对数据库的操作提供了简单高效的操作接口。

三、继承关系图

　　（继承图）

　　（接口定义）

四、代码实现

 1 #region async
 2         /// <summary>
 3         /// 查询所有数据(无分页,请慎用)
 4         /// </summary>
 5         /// <returns></returns>
 6         task<ienumerable<t>> findallasync();
 7 
 8         /// <summary>
 9         /// 根据条件查询数据
10         /// </summary>
11         /// <param name="predicate">条件表达式树</param>
12         /// <param name="orderby">排序</param>
13         /// <returns>泛型实体集合</returns>
14         task<ienumerable<t>> findlistbyclauseasync(expression<func<t, bool>> predicate, string orderby = "");
15 
16         task<ienumerable<t>> pagequeryasync(expression<func<t, bool>> predicate, int skip = 0, int pagesize = 15, string orderby = "");
17         task<t> findbyclauseasync(expression<func<t, bool>> predicate);
18         /// <summary>
19         /// 插入实体数据
20         /// </summary>
21         /// <param name="entity"></param>
22         /// <returns></returns>
23         task<int> insertasync(t entity);
24         /// <summary>
25         /// 更新实体数据
26         /// </summary>
27         /// <param name="entity"></param>
28         /// <returns></returns>
29         task<bool> updateasync(t entity);
30         /// <summary>
31         /// deleteasync
32         /// </summary>
33         /// <param name="entity"></param>
34         /// <returns></returns>
35         task<bool> deleteasync(t entity);
36         /// <summary>
37         /// deleteasync
38         /// </summary>
39         /// <param name="where">条件表达式</param>
40         /// <returns></returns>
41         task<bool> deleteasync(expression<func<t, bool>> @where);
42         /// <summary>
43         /// deletebyidasync
44         /// </summary>
45         /// <param name="id"></param>
46         /// <returns></returns>
47         task<bool> deletebyidasync(object id);
48         /// <summary>
49         /// deletebyidsasync
50         /// </summary>
51         /// <param name="ids">ids</param>
52         /// <returns></returns>
53         task<bool> deletebyidsasync(object[] ids);
54         /// <summary>
55         /// insertasync
56         /// </summary>
57         /// <param name="t"></param>
58         /// <returns></returns>
59         task<dbresult<task<int>>> insertasync(list<t> t);
60         /// <summary>
61         /// deletebyclauseasync
62         /// </summary>
63         /// <param name="predicate"></param>
64         /// <returns></returns>
65         task<int> deletebyclauseasync(expression<func<t, bool>> predicate);
66 
67         /// <summary>
68         /// 事务
69         /// </summary>
70         /// <param name="func"></param>
71         /// <returns></returns>
72         task<dbresult<t>> usertranasync(func<t> func);
73 
74         /// <summary>
75         /// 事务
76         /// </summary>
77         /// <param name="action"></param>
78         task<dbresult<bool>> usertranasync(action action);
79         #endregion

五、实现　

/// <summary>
    /// genericrepositorybase
    /// </summary>
    /// <typeparam name="t"></typeparam>
    public class genericsqlsugarrepositorybase<t> : isqlsugarrepository<t> where t : class, new()
    {
        #region sync
        /// <summary>
        /// 根据主键查询
        /// </summary>
        /// <param name="pkvalue">主键</param>
        /// <returns></returns>
        public t findbyid(object pkvalue)
        {
            using (var db = dbfactory.db)
            {
                return db.queryable<t>().insingle(pkvalue);
            }
        }

        /// <summary>
        /// 查询所有数据(无分页,请慎用)
        /// </summary>
        /// <returns></returns>
        public ienumerable<t> findall()
        {
            using (var db = dbfactory.db)
            {
                return db.queryable<t>().tolist();
            }
        }

        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        /// <param name="predicate">条件表达式树</param>
        /// <param name="orderby">排序</param>
        /// <returns>泛型实体集合</returns>
        public ienumerable<t> findlistbyclause(expression<func<t, bool>> predicate, string orderby = "")
        {
            using (var db = dbfactory.db)
            {
                var query = db.queryable<t>().where(predicate);
                if (!string.isnullorempty(orderby))
                    query.orderby(orderby);
                return query.tolist();
            }
        }

        /// <summary>
        /// 根据条件分页查询
        /// </summary>
        /// <param name="predicate">条件表达式树</param>
        /// <param name="skip"></param>
        /// <param name="pagesize"></param>
        /// <param name="orderby"></param>
        /// <returns></returns>
        public ienumerable<t> pagequery(expression<func<t, bool>> predicate, int skip, int pagesize = 15,
            string orderby = "")
        {
            using (var db = dbfactory.db)
            {
                if (skip > 0)
                    skip = pagesize * skip;
                var query = db.queryable<t>().where(predicate).skip(skip).take(pagesize);
                if (!string.isnullorempty(orderby))
                    query = query.orderby(orderby);
                return query.tolist();
            }
        }

        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        /// <param name="predicate">条件表达式树</param>
        /// <returns></returns>
        public t findbyclause(expression<func<t, bool>> predicate)
        {
            using (var db = dbfactory.db)
            {
                return db.queryable<t>().first(predicate);
            }
        }

        /// <summary>
        /// 写入实体数据
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns></returns>
        public int insert(t entity)
        {
            using (var db = dbfactory.db)
            {
                return db.insertable(entity).executecommand();
            }
        }

        /// <summary>
        /// 更新实体数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public bool update(t entity)
        {
            using (var db = dbfactory.db)
            {
                return db.updateable(entity).executecommand() > 0;
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="entity">实体类</param>
        /// <returns></returns>
        public bool delete(t entity)
        {
            using (var db = dbfactory.db)
            {
                return db.deleteable(entity).executecommand() > 0;
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="where">过滤条件</param>
        /// <returns></returns>
        public bool delete(expression<func<t, bool>> @where)
        {
            using (var db = dbfactory.db)
            {
                return db.deleteable<t>(@where).executecommand() > 0;
            }
        }

        /// <summary>
        /// 删除指定id的数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool deletebyid(object id)
        {
            using (var db = dbfactory.db)
            {
                return db.deleteable<t>(id).executecommand() > 0;
            }
        }

        /// <summary>
        /// 删除指定id集合的数据(批量删除)
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public bool deletebyids(object[] ids)
        {
            using (var db = dbfactory.db)
            {
                return db.deleteable<t>().in(ids).executecommand() > 0;
            }
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql">sql 语句</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public ienumerable<t> findlistbysql(string sql, object dynamic)
        {
            using (var db = dbfactory.db)
            {
                return db.ado.sqlquery<t>(sql, dynamic);
            }
        }

        /// <summary>
        /// 批量插入 插入失败时 事务会自动回退
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public int insert(list<t> t)
        {
            using (var db = dbfactory.db)
            {
                return db.ado.usetran(() => db.insertable(t.toarray()).executecommand()).data;
            }
        }

        /// <summary>
        /// 事务
        /// </summary>
        /// <param name="func"></param>
        /// <returns></returns>
        public dbresult<t> usertran(func<t> func)
        {
            using (var db = dbfactory.db)
            {
                return db.ado.usetran(func.invoke);
            }
        }

        /// <summary>
        /// 事务
        /// </summary>
        /// <param name="action"></param>
        public dbresult<bool> usertran(action action)
        {
            using (var db = dbfactory.db)
            {
                return db.ado.usetran(action.invoke);
            }
        }

        /// <summary>
        /// 根据条件批量删除
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public int deletebyclause(expression<func<t, bool>> predicate)
        {
            using (var db = dbfactory.db)
            {
                return db.deleteable<t>().where(predicate).executecommand();
            }
        }

        // void shadowcopy(object a, object b);

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="pagination"></param>
        /// <returns></returns>
        public list<t> findlist(expression<func<t, bool>> predicate, pagination pagination)
        {
            var isasc = pagination.sord.tolower() == "asc";
            string[] _order = pagination.sidx.split(',');
            methodcallexpression resultexp = null;
            using (var db = dbfactory.db)
            {
                var tempdata = db.queryable<t>().where(predicate).tolist().asqueryable();
                foreach (string item in _order)
                {
                    string _orderpart = item;
                    _orderpart = regex.replace(_orderpart, @"\s+", " ");
                    string[] _orderarry = _orderpart.split(' ');
                    string _orderfield = _orderarry[0];
                    bool sort = isasc;
                    if (_orderarry.length == 2)
                    {
                        isasc = _orderarry[1].toupper() == "asc";
                    }
                    var parameter = expression.parameter(typeof(t), "t");
                    var property = typeof(t).getproperty(_orderfield);
                    var propertyaccess = expression.makememberaccess(parameter, property);
                    var orderbyexp = expression.lambda(propertyaccess, parameter);
                    resultexp = expression.call(typeof(queryable), isasc ? "orderby" : "orderbydescending", new type[] { typeof(t), property.propertytype }, tempdata.expression, expression.quote(orderbyexp));
                }
                tempdata = tempdata.provider.createquery<t>(resultexp);
                pagination.records = tempdata.count();
                tempdata = tempdata.skip<t>(pagination.rows * (pagination.page - 1)).take<t>(pagination.rows).asqueryable();
                return tempdata.tolist();
            }
        }
        #endregion

        #region async
        /// <summary>
        /// 查询所有数据(无分页,请慎用)
        /// </summary>
        /// <returns></returns>
        public async task<ienumerable<t>> findallasync()
        {
            using (var db = dbfactory.db)
            {
                return await db.queryable<t>().tolistasync();
            }
        }

        /// <summary>
        /// 根据条件查询数据
        /// </summary>
        /// <param name="predicate">条件表达式树</param>
        /// <param name="orderby">排序</param>
        /// <returns>泛型实体集合</returns>
        public async task<ienumerable<t>> findlistbyclauseasync(expression<func<t, bool>> predicate, string orderby = "")
        {
            using (var db = dbfactory.db)
            {
                var query = db.queryable<t>().where(predicate);
                if (!string.isnullorempty(orderby))
                {
                    query = query.orderby(orderby);
                }
                return await query.tolistasync();
            }
        }

        public async task<ienumerable<t>> pagequeryasync(expression<func<t, bool>> predicate, int skip = 0,
            int pagesize = 15, string orderby = "")
        {
            using (var db = dbfactory.db)
            {
                if (skip > 0)
                    skip = pagesize * skip;
                var query = db.queryable<t>().where(predicate).skip(skip).take(pagesize);
                if (!string.isnullorempty(orderby))
                {
                    query = query.orderby(orderby);
                }
                return await query.tolistasync();
            }
        }

        public async task<t> findbyclauseasync(expression<func<t, bool>> predicate)
        {
            using (var db = dbfactory.db)
            {
                return await db.queryable<t>().firstasync(predicate);
            }
        }

        /// <summary>
        /// 插入实体数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async task<int> insertasync(t entity)
        {
            using (var db = dbfactory.db)
            {
                return await db.insertable(entity).executecommandasync();
            }
        }

        /// <summary>
        /// 更新实体数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async task<bool> updateasync(t entity)
        {
            using (var db = dbfactory.db)
            {
                return await db.updateable(entity).executecommandasync() > 0;
            }
        }

        /// <summary>
        /// deleteasync
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async task<bool> deleteasync(t entity)
        {
            using (var db = dbfactory.db)
            {
                return await db.deleteable(entity).executecommandasync() > 0;
            }
        }

        /// <summary>
        /// deleteasync
        /// </summary>
        /// <param name="where">条件表达式</param>
        /// <returns></returns>
        public async task<bool> deleteasync(expression<func<t, bool>> @where)
        {
            using (var db = dbfactory.db)
            {
                return await db.deleteable(@where).executecommandasync() > 0;
            }
        }

        /// <summary>
        /// deletebyidasync
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async task<bool> deletebyidasync(object id)
        {
            using (var db = dbfactory.db)
            {
                return await db.deleteable<t>(id).executecommandasync() > 0;
            }
        }

        /// <summary>
        /// deletebyidsasync
        /// </summary>
        /// <param name="ids">ids</param>
        /// <returns></returns>
        public async task<bool> deletebyidsasync(object[] ids)
        {
            using (var db = dbfactory.db)
            {
                return await db.deleteable<t>().in(ids).executecommandasync() > 0;
            }
        }

        /// <summary>
        /// insertasync
        /// </summary>
        /// <param name="t"></param>
        /// <returns></returns>
        public async task<dbresult<task<int>>> insertasync(list<t> t)
        {
            using (var db = dbfactory.db)
            {
                return await db.ado.usetranasync(async () => await db.insertable(t.toarray()).executecommandasync());
            }
        }

        /// <summary>
        /// deletebyclauseasync
        /// </summary>
        /// <param name="predicate"></param>
        /// <returns></returns>
        public async task<int> deletebyclauseasync(expression<func<t, bool>> predicate)
        {
            using (var db = dbfactory.db)
            {
                return await db.deleteable<t>().where(predicate).executecommandasync();
            }
        }

        /// <summary>
        /// 事务
        /// </summary>
        /// <param name="func"></param>
        /// <returns></returns>
        public async task<dbresult<t>> usertranasync(func<t> func)
        {
            using (var db = dbfactory.db)
            {
                return await db.ado.usetranasync(func.invoke);
            }
        }

        /// <summary>
        /// 事务
        /// </summary>
        /// <param name="action"></param>
        public async task<dbresult<bool>> usertranasync(action action)
        {
            using (var db = dbfactory.db)
            {
                return await db.ado.usetranasync(action.invoke);
            }
        }
        #endregion
    }

本篇就到这里，下篇将介绍业务逻辑层与仓储库之间的关系与实现

（本人坚信：学习是由浅到深的过程，先打基础）

　　不喜勿喷！谢谢！

github地址：

　　https://github.com/ajuprince/aju.carefree