这个类是采用工厂模式创建DB连接的封装-ASP.NET-解决方案网 yabo亚博体育下载,亚博app怎么下载「唯一」登录入口,yabo亚博官网

搭建一套自己实用的.net架构(3)【ORM-Dapper+DapperExtensions】

现在成熟的ORM比比皆是，这里只介绍Dapper的使用（最起码我在使用它，已经运用到项目中，小伙伴们反馈还可以）。

????

优点：

????

1、开源、轻量、小巧、上手容易。

????

2、支持的数据库还蛮多的， Mysql,SqlLite,Sqlserver,Oracle等一系列的数据库。

????

3、Dapper原理通过Emit反射IDataReader的序列队列来快速的得到和产生对象。性能貌似很牛逼的样子

????

缺点：

????

作为一款ORM太过于轻量级了，根据对象自动生成sql的功能还是空白，需要自己来扩展，

????

当然这也是优点，? 好声音的导师们经常说某人就是张白纸……??

????

因此针对Dapper已经有很多成熟的扩展项目了，Dapper.Rainbow、Dapper.Contrib，DapperExtensions。

????

我们这里介绍的是DapperExtensions。

????

dapper-dot-net源码：https://github.com/StackExchange/dapper-dot-net?? （更新频率快，项目包含了各种除了Dapper-Extensions的?扩展项目）

????

Dapper-Extensions?源码：https://github.com/tmsmith/Dapper-Extensions?

????

Dapper-Extensions的优点：

????

1、开源

????

2、针对Dapper封装了常用的CRUD方法，有独立的查询语法。

????

3、需要映射的实体类本身0配置，无需加特性什么的。是通过独立的映射类来处理，可以设置类映射到DB的别名，字段的别名等等。

????

Dapper-Extensions的缺点：

????

1、好几年没更新了

????

2、不支持oracle（木有oracle的方言，已经搞定）??

????

3、不能同时支持多种数据库（已经搞定）

????

4、部分代码有些bug（发现的都搞定了）

????

下面先简单介绍一下Dapper的基本语法。

????

Dapper就一个.cs文件，可以放到项目代码中直接编译，也可以直接引用DLL文件。

????

Dapper对DB的操作依赖于Connection，为了支持多库，咱们用?IDbConnection conn

????

using (IDbConnection conn = GetConnection())
????    {
????        const string query = "select * from XO order by id desc";
????        return conn.Query(query,null);
????    }

????

下面是带参数的语法

????

int xoID=666; //变量主键
 using (IDbConnection conn = GetConnection())
????    {
????        const string query = "select * from XO where Id=@MyID";
????        return conn.Query(query, new { MyID = xoID});
????                        
????    }

????

各种方法都重载了事务的操作，一般的数据库操作都支持。但是每次执行都需要传递sql，而且每次都要使用Using，看着不爽啊，这……?

????

好吧下面简单介绍下使用Dapper-Extensions的基本语法（在Dapper-Extensions ?的基础上用了Repository模式，代码效果如下）。

????

            //实体类
????            DemoEntity entity = new DemoEntity();
????            //根据实体主键删除
????            this.Delete(entity);
????            //根据主键ID删除
????            this.Delete(1);
????            //增加
????            this.Insert(entity);
????            //更新
????            bool result = this.Update(entity);
????            //根据主键返回实体
????            entity = this.GetById(1);
????            //返回 行数
????            this.Count(new { ID = 1 });
????            //查询所有
????            IEnumerable list = this.GetAll();
????            IList sort = new List();
????            sort.Add(new Sort { PropertyName = "ID", Ascending = false });
????            //条件查询
????            list = this.GetList(new { ID = 1, Name = "123" }, sort);
????            //orm 拼接条件 查询
????            IList predList = new List();
????            predList.Add(Predicates.Field(p => p.Name, Operator.Like, "不知道%"));
????            predList.Add(Predicates.Field(p => p.ID, Operator.Eq, 1));
????            IPredicateGroup predGroup = Predicates.Group(GroupOperator.And, predList.ToArray());
????            
????            list = this.GetList(predGroup);
????            //分页查询
????            long allRowsCount = 0;
????            this.GetPageList(1, 10, out allRowsCount, new { ID = 1 }, sort);

????

在说ORM之前，还是要说一下HY.DataAccess这个模块

????

? 这个类是采用工厂模式创建DB连接的封装

????

这个模块是对数据访问提供的一个Helper的功能，里面包含了各种DB的SqlHelper，分页。

????

SqlConnectionFactory.cs 这个类是采用工厂模式创建DB连接的封装，代码如下：

????

using System;
????using System.Collections.Generic;
????using System.Configuration;
????using System.Data;
????namespace HY.DataAccess
????{
????    public enum DatabaseType
????    {    
????        SqlServer,
????        MySql,
????        Oracle,
????        DB2
????    }
????    public class SqlConnectionFactory
????    {
????        public static IDbConnection CreateSqlConnection(DatabaseType dbType, string strKey)
????        {
????            IDbConnection connection = null;
????            string strConn = ConfigurationManager.ConnectionStrings[strKey].ConnectionString;
????            switch (dbType)
????            {
????                case DatabaseType.SqlServer:
????                    connection = new System.Data.SqlClient.SqlConnection(strConn);
????                    break;
????                case DatabaseType.MySql:
????                    //connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
????                    //break;
????                case DatabaseType.Oracle:
????                    //connection = new Oracle.DataAccess.Client.OracleConnection(strConn);
????                    connection = new System.Data.OracleClient.OracleConnection(strConn);
????                    break;
????                case DatabaseType.DB2:
????                    connection = new System.Data.OleDb.OleDbConnection(strConn);
????                    break;
????            }
????            return connection;
????        }
????    }
????}

????

????View Code

????

?ORM也不是万能的，比如做大数据的批量插入什么的，还是需要SqlHelper，加上有的人就喜欢DataTable或者DataSet。

????

所以SqlHelper作为根基，ORM作为辅助，万无一失啊。

????

下面说说ORM这块的实现方式。见下截图

????

这个类是采用工厂模式创建DB连接的封装

????

IDataServiceRepository.cs（提供业务层使用，里面的方法不支持传递sql，包含sql的语句最好还是放在数据层操作的好）

????

using System.Collections.Generic;
????using System.Data;
????using DapperExtensions;
????using HY.DataAccess;
????namespace HY.ORM
????{
????    public interface IDataServiceRepository
????    {
????        IDBSession DBSession { get; }
????        T GetById(dynamic primaryId) where T : class;
????        IEnumerable GetByIds(IList ids) where T : class;
????        IEnumerable GetAll() where T : class;
????        int Count(object predicate, bool buffered = false) where T : class;
????        //lsit
????        IEnumerable GetList(object predicate = null, IList sort = null, bool buffered = false) where T : class;
????        IEnumerable GetPageList(int pageIndex, int pageSize, out long allRowsCount, object predicate = null, IList sort = null, bool buffered = true) where T : class;
????        dynamic Insert(T entity, IDbTransaction transaction = null) where T : class;
????        bool InsertBatch(IEnumerable entityList, IDbTransaction transaction = null) where T : class;
????        bool Update(T entity, IDbTransaction transaction = null) where T : class;
????        bool UpdateBatch(IEnumerable entityList, IDbTransaction transaction = null) where T : class;
????        int Delete(dynamic primaryId, IDbTransaction transaction = null) where T : class;
????        int DeleteList(object predicate, IDbTransaction transaction = null) where T : class;
????        bool DeleteBatch(IEnumerable ids, IDbTransaction transaction = null) where T : class;
????    }
????}

????

????View Code

????

?IDataRepository.cs（提供数据层使用，继承了上面的IDataServiceRepository，支持传入sql）

????

using System;
????using System.Collections.Generic;
????using System.Data;
????using Dapper;
????using HY.DataAccess;
????namespace HY.ORM
????{
????    public interface IDataRepository : IDataServiceRepository
????    {
????        IDBSession DBSession { get; }
????            
????        IEnumerable Get(string sql, dynamic param = null, bool buffered = true) where T : class;
????        IEnumerable Get(string sql, dynamic param = null, bool buffered = true);
????        IEnumerable Get(string sql, Func map,
????            dynamic param = null, IDbTransaction transaction = null, bool buffered = true,
????            string splitOn = "Id", int? commandTimeout = null);
????         IEnumerable Get(string sql, Func map,
????            dynamic param = null, IDbTransaction transaction = null, bool buffered = true,
????            string splitOn = "Id", int? commandTimeout = null);
????        SqlMapper.GridReader GetMultiple(string sql, dynamic param = null, IDbTransaction transaction = null,
????            int? commandTimeout = null, CommandType? commandType = null);
???? 
????        IEnumerable GetPage(int pageIndex, int pageSize, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql=null,  dynamic allRowsCountParam = null, bool buffered = true) where T : class;
????      
????        Int32 Execute(string sql, dynamic param = null, IDbTransaction transaction = null);
????    }
????}

????

????View Code

????

?RepositoryServiceBase.cs（IDataServiceRepository的实现类）

????

using System.Collections.Generic;
????using System.Data;
????using System.Linq;
????using Dapper;
????using DapperExtensions;
????using HY.DataAccess;
????namespace HY.ORM
????{
????    public class RepositoryServiceBase : IDataServiceRepository
????    {
????        public RepositoryServiceBase()
????        {
????        }
????        public RepositoryServiceBase(IDBSession dbSession)
????        {
????            DBSession = dbSession;
????        }
????        public IDBSession DBSession { get; private set; }
????        public void SetDBSession(IDBSession dbSession)
????        {
????            DBSession = dbSession;
????        }
????        /// 
????        /// 根据Id获取实体
????        /// 
????        /// 
????        /// 
????        /// 
????        public T GetById(dynamic primaryId) where T : class
????        {
????            return DBSession.Connection.Get(primaryId as object, databaseType: DBSession.DatabaseType);
????        }
????        /// 
????        /// 根据多个Id获取多个实体
????        /// 
????        /// 
????        /// 
????        /// 
????        public IEnumerable GetByIds(IList ids) where T : class
????        {
????            var tblName = string.Format("dbo.{0}", typeof(T).Name);
????            var idsin = string.Join(",", ids.ToArray());
????            var sql = "SELECT * FROM @table WHERE Id in (@ids)";
????            IEnumerable dataList = DBSession.Connection.Query(sql, new { table = tblName, ids = idsin });
????            return dataList;
????        }
????        /// 
????        /// 获取全部数据集合
????        /// 
????        /// 
????        /// 
????        public IEnumerable GetAll() where T : class
????        {
????            return DBSession.Connection.GetList(databaseType: DBSession.DatabaseType);
????        }
????        /// 
????        /// 统计记录总数
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public int Count(object predicate, bool buffered = false) where T : class
????        {
????            return DBSession.Connection.Count(predicate, databaseType: DBSession.DatabaseType);
????        }
????        /// 
????        /// 查询列表数据
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public IEnumerable GetList(object predicate = null, IList sort = null,
????            bool buffered = false) where T : class
????        {
????            return DBSession.Connection.GetList(predicate, sort, null, null, buffered, databaseType: DBSession.DatabaseType);
????        }
????        /// 
????        /// 分页
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public IEnumerable GetPageList(int pageIndex, int pageSize, out long allRowsCount,
????            object predicate = null, IList sort = null, bool buffered = true) where T : class
????        {
????            if (sort == null)
????            {
????                sort = new List();
????            }
????            IEnumerable entityList = DBSession.Connection.GetPage(predicate, sort, pageIndex, pageSize, null, null, buffered, databaseType: DBSession.DatabaseType);
????            allRowsCount = DBSession.Connection.Count(predicate, databaseType: DBSession.DatabaseType);
????            return entityList;
????        }
????        /// 
????        /// 插入单条记录
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public dynamic Insert(T entity, IDbTransaction transaction = null) where T : class
????        {
????            dynamic result = DBSession.Connection.Insert(entity, transaction, databaseType: DBSession.DatabaseType);
????            return result;
????        }
????        /// 
????        /// 更新单条记录
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public bool Update(T entity, IDbTransaction transaction = null) where T : class
????        {
????            bool isOk = DBSession.Connection.Update(entity, transaction, databaseType: DBSession.DatabaseType);
????            return isOk;
????        }
????        /// 
????        /// 删除单条记录
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public int Delete(dynamic primaryId, IDbTransaction transaction = null) where T : class
????        {
????            var entity = GetById(primaryId);
????            var obj = entity as T;
????            int isOk = DBSession.Connection.Delete(obj, databaseType: DBSession.DatabaseType);
????            return isOk;
????        }
????        /// 
????        /// 删除单条记录
????        /// 
????        /// 
????        /// 
????        /// 
????        ///  
????        public int DeleteList(object predicate = null, IDbTransaction transaction = null) where T : class
????        {
????            return DBSession.Connection.Delete(predicate, transaction, databaseType: DBSession.DatabaseType);
????        }
????        /// 
????        /// 批量插入功能
????        /// 
????        /// 
????        /// 
????        /// 
????        public bool InsertBatch(IEnumerable entityList, IDbTransaction transaction = null) where T : class
????        {
????            bool isOk = false;
????            foreach (var item in entityList)
????            {
????                Insert(item, transaction);
????            }
????            isOk = true;
????            return isOk;
????        }
????        /// 
????        /// 批量更新（）
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public bool UpdateBatch(IEnumerable entityList, IDbTransaction transaction = null) where T : class
????        {
????            bool isOk = false;
????            foreach (var item in entityList)
????            {
????                Update(item, transaction);
????            }
????            isOk = true;
????            return isOk;
????        }
????        /// 
????        /// 批量删除
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public bool DeleteBatch(IEnumerable ids, IDbTransaction transaction = null) where T : class
????        {
????            bool isOk = false;
????            foreach (var id in ids)
????            {
????                Delete(id, transaction);
????            }
????            isOk = true;
????            return isOk;
????        }
????    }
????}

????

????View Code

????

RepositoryBase.cs（IDataRepository的实现类）

????

using System;
????using System.Collections.Generic;
????using System.Data;
????using Dapper;
????using DapperExtensions;
????using HY.DataAccess;
????namespace HY.ORM
????{
????    /// 
????    /// Repository基类
????    /// 
????    public class RepositoryBase : RepositoryServiceBase, IDataRepository
????    {
????        public RepositoryBase()
????        {
????        }
????        public new void SetDBSession(IDBSession dbSession)
????        {
????            base.SetDBSession(dbSession);
????        }
????        public RepositoryBase(IDBSession dbSession)
????            : base(dbSession)
????        {
????        }
????        /// 
????        /// 根据条件筛选出数据集合
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public IEnumerable Get(string sql, dynamic param = null, bool buffered = true) where T : class
????        {
????            return DBSession.Connection.Query(sql, param as object, DBSession.Transaction, buffered);
????        }
????        /// 
????        /// 根据条件筛选数据集合
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public IEnumerable Get(string sql, dynamic param = null, bool buffered = true)
????        {
????            return DBSession.Connection.Query(sql, param as object, DBSession.Transaction, buffered);
????        }
????        /// 
????        /// 分页查询
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public IEnumerable GetPage(int pageIndex, int pageSize, out long allRowsCount, string sql, dynamic param = null, string allRowsCountSql = null, dynamic allRowsCountParam = null, bool buffered = true) where T : class
????        {
????            IEnumerable entityList = DBSession.Connection.GetPage(pageIndex, pageSize, out allRowsCount, sql, param as object, allRowsCountSql, null, null, buffered, databaseType: DBSession.DatabaseType);
????            return entityList;
????        }
????        /// 
????        /// 根据表达式筛选
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public IEnumerable Get(string sql, Func map,
????            dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id",
????            int? commandTimeout = null)
????        {
????            return DBSession.Connection.Query(sql, map, param as object, transaction, buffered, splitOn);
????        }
????        /// 
????        /// 根据表达式筛选
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public IEnumerable Get(string sql, Func map,
????            dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id",
????            int? commandTimeout = null)
????        {
????            return DBSession.Connection.Query(sql, map, param as object, transaction, buffered, splitOn);
????        }
????        /// 
????        /// 获取多实体集合
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        /// 
????        public SqlMapper.GridReader GetMultiple(string sql, dynamic param = null, IDbTransaction transaction = null,
????            int? commandTimeout = null, CommandType? commandType = null)
????        {
????            return DBSession.Connection.QueryMultiple(sql, param as object, transaction, commandTimeout, commandType);
????        }
????        /// 
????        /// 执行sql操作
????        /// 
????        /// 
????        /// 
????        /// 
????        public int Execute(string sql, dynamic param = null, IDbTransaction transaction = null)
????        {
????            return DBSession.Connection.Execute(sql, param as object, transaction);
????        }
????    }
????}

????

????View Code

????

未完待续……

????

搭建一套自己实用的.net架构(1)【概述】

????

搭建一套自己实用的.net架构(2)【日志模块-log4net】

????

搭建一套自己实用的.net架构(3)【ORM-Dapper+DapperExtensions】

????

2楼要神么自行车: 支持，期待继续写下去

1楼阿水: 顶一记，一致比较关注Dapper！