.NET Core学习——Dapper

于 2020-03-02 16:43:43 发布
阅读量2.8k 收藏
点赞数 1
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Abbey_qi/article/details/104607741
版权

一、了解Dapper

我们都知道，ORM全称是，Object Relationship Mapper，即，对象关系映射。也就是可以用object来map我们的db，而且市面上的orm框架有很多，其中有一个框架叫做dapper，而且被称为the king of ORM。

　　市场上，也有一些其他的ORM，比如EF Core，NHibernate 、FreeSQL等等，来处理大数据访问及关系映射。既然官方推出了EF Core，说明其对框架的支持会很友好，为什么又会有那么多的ORM框架供我们使用呢？其实，每一个框架都有其适用的场景。如果你在小的项目中，使用Entity Framework、Entity Framework Core、NHibernate 来处理大数据访问及关系映射，未免有点杀鸡用牛刀。你又觉得ORM省时省力，这时Dapper 将是你不二的选择。其实，Entity Framework Core的性能并不是很高，当对性能有要求的时候，一般公司都会自己封装一套ORM。

为什么选择Dapper？

  1. 轻量。只有一个文件（SqlMapper.cs），编译完成之后只有120k(好象是变胖了)
  2. 速度快。Dapper的速度接近与IDataReader，取列表的数据超过了DataTable。
  3. 支持多种数据库。Dapper可以在所有Ado.net Providers下工作，包括sqlite, sqlce, firebird, oracle, MySQL, PostgreSQL and SQL Server
  4. 可以映射一对一，一对多，多对多等多种关系。
  5. 性能高。通过Emit反射IDataReader的序列队列，来快速的得到和产生对象，性能不错。
  6. 支持FrameWork2.0，3.0，3.5，4.0，4.5

 二、使用Dapper

在这里，我们在dotnet core下面使用Dapper操作MySQL。

首先，就先创建一个core webapi的项目，基于core3.1版本创建一个项目。

Dapper安装，使用NuGet来安装Dapper程序包

Install-Package Dapper -Version 2.0.30

Install-Package Dapper.Contrib -Version 2.0.30

使用NuGet安装MySQL.Data的程序包 

安装这些程序包之后，在appsettings.json文件中添加链接数据库的字符串：

"ConnectionStrings": {
    "DefaultConnection": "server=服务器;port=端口号;database=regatta{0};SslMode=None;uid=userName;pwd=passWord;Allow User Variables=true"
  }

然后，封装一个工具类，来获得我们的连接字符串，和管理连接池。

IDapperContext：

    /// <summary>
    /// Dapper上下文
    /// </summary>
    public interface IDapperContext : IDisposable
    {
        /// <summary>
        /// 数据库连接对象
        /// </summary>
        IDbConnection ReadConnection { get; }

        /// <summary>
        /// 数据库连接对象
        /// </summary>
        IDbConnection WriteConnection { get; }
    }

 DapperContext：

    public class DapperContext : IDapperContext
    {
        /// <summary>
        /// 读连接字符串
        /// </summary>
        private string _readConnectionString;

        /// <summary>
        /// 写连接字符串
        /// </summary>
        private string _writeConnectionString;

        private bool _useMiniProfiling;

        /// <summary>
        /// 读连接
        /// </summary>
        private IDbConnection _readConnection;

        /// <summary>
        /// 写连接
        /// </summary>
        private IDbConnection _wrteConnection;

        /// <summary>
        /// 配置
        /// </summary>
        private readonly AppSetting _appSetting;

        /// <summary>
        /// 构造函数注入IOptions
        /// </summary>
        /// <param name="appSetting"></param>
        public DapperContext(IOptions<AppSetting> appSetting)
        {
            _appSetting = appSetting.Value;
            _readConnectionString = _appSetting.ReadOnlyConnectionString;
            _writeConnectionString = _appSetting.SetConnectionString;
            _useMiniProfiling = _appSetting.UseMiniProfiling;
        }

        /// <summary>
        /// 连接字符串
        /// </summary>
        /// <param name="connectionString"></param>
        public DapperContext(string connectionString)
        {
            _readConnectionString = connectionString;
        }

        #region 读

        /// <summary>
        /// 获取连接
        /// </summary>
        public IDbConnection ReadConnection
        {
            get
            {
                if (_readConnection == null || _readConnection.State == ConnectionState.Closed)
                {
                    if (_useMiniProfiling)
                    {
                        _readConnection = new ProfiledDbConnection(new MySqlConnection(_readConnectionString), MiniProfiler.Current);
                    }
                    else
                    {
                        _readConnection = new MySqlConnection(_readConnectionString);
                    }
                }
                if (_readConnection.State != ConnectionState.Open)
                {
                    _readConnection.Open();
                }
                return _readConnection;
            }
        }

        /// <summary>
        /// 释放连接
        /// </summary>
        public void Dispose()
        {
            if (_readConnection != null && _readConnection.State == ConnectionState.Open)
                _readConnection.Close();
            if (_wrteConnection != null && _wrteConnection.State == ConnectionState.Open)
                _wrteConnection.Close();
        }

        #endregion 读

        #region 写

        /// <summary>
        /// 获取连接
        /// </summary>
        public IDbConnection WriteConnection
        {
            get
            {
                if (_wrteConnection == null || _wrteConnection.State == ConnectionState.Closed)
                {
                    if (_useMiniProfiling)
                    {
                        _wrteConnection = new ProfiledDbConnection(new MySqlConnection(_writeConnectionString), MiniProfiler.Current);
                    }
                    else
                    {
                        _wrteConnection = new MySqlConnection(_writeConnectionString);
                    }
                }
                if (_wrteConnection.State != ConnectionState.Open)
                {
                    _wrteConnection.Open();
                }
                return _wrteConnection;
            }
        }

        #endregion 写
    }

 创建一个仓储，用来存放数据库CRUD等操作

IRepository：

    /// <summary>
    /// 数据库CRUD等操作
    /// </summary>
    /// <typeparam name="T"></typeparam>
    public interface IRepository<T> where T : class
    {
        /// <summary>
        /// 上下文
        /// </summary>
        IDapperContext Context { get; }

        /// <summary>
        /// 只读连接
        /// </summary>
        IDbConnection ReadConnection { get; }

        /// <summary>
        /// 读写连接
        /// </summary>
        IDbConnection WriteConnection { get; }

        /// <summary>
        /// 获取分页数据
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="criteria">查询设置</param>
        /// <param name="param"></param>
        /// <returns></returns>
        PageDataView<TEntity> GetPageData<TEntity>(PageCriteria criteria, object param = null) where TEntity : class;

        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="criteria"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        PageDataView<TEntity> GetAllData<TEntity>(PageCriteria criteria, object param = null) where TEntity : class;

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        long Add(T entity, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 批量添加数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        long BatchAdd(IEnumerable<T> list, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        ///
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        bool Update(T entity, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 删除单条数据
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        bool Remove(T entity, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="list"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        bool BatchRemove(IEnumerable<T> list, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 根据主键获取数据
        /// </summary>
        /// <param name="key"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        T GetByKey(object key, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 获取所有数据
        /// </summary>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        IEnumerable<T> GetAll(IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 根据条件获取数据列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        IEnumerable<T> GetBy(object sql = null, object param = null, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 查询数据列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        IEnumerable<dynamic> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 多对象查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        int Excute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null);

        /// <summary>
        /// 执行是否存在数据
        /// </summary>
        /// <param name="sqlStr">查询（例：SELECT COUNT(1)  FROM XXX ）</param>
        /// <returns></returns>
        bool Exists(string sqlStr, object param = null);
    }

Repository：

    public class Repository<T> : IRepository<T> where T : class
    {
        public Repository(IDapperContext context)
        {
            Context = context;
        }

        public IDapperContext Context { get; private set; }
        public IDbConnection ReadConnection => Context.ReadConnection;

        public IDbConnection WriteConnection => Context.WriteConnection;

        public PageDataView<TEntity> GetPageData<TEntity>(PageCriteria criteria, object param = null) where TEntity : class
        {
            var p = new DynamicParameters();
            string proName = "ProcGetPageData";
            p.Add("_tables", criteria.TableName);
            p.Add("_fields", criteria.Fields);
            p.Add("_where", criteria.Condition);
            p.Add("_pageIndex", criteria.CurrentPage);
            p.Add("_pageSize", criteria.PageSize);
            p.Add("_orderby", criteria.Sort);
            p.Add("_totalcount", dbType: DbType.Int32, direction: ParameterDirection.Output);
            p.Add("_pagecount", dbType: DbType.Int32, direction: ParameterDirection.Output);
            var pageData = new PageDataView<TEntity>();

            pageData.Items = ReadConnection.Query<TEntity>(proName, p, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList();
            pageData.TotalNum = p.Get<int>("_totalcount");
            pageData.TotalPageCount = p.Get<int>("_pagecount"); //Convert.ToInt32(Math.Ceiling(pageData.TotalNum * 1.0 / criteria.PageSize));
            pageData.CurrentPage = criteria.CurrentPage > pageData.TotalPageCount ? pageData.TotalPageCount : criteria.CurrentPage;

            return pageData;
        }

        public PageDataView<TEntity> GetAllData<TEntity>(PageCriteria criteria, object param = null) where TEntity : class
        {
            var p = new DynamicParameters();
            string proName = "ProcGetAllData";
            p.Add("_tables", criteria.TableName);
            p.Add("_fields", criteria.Fields);
            p.Add("_where", criteria.Condition);
            p.Add("_orderby", criteria.Sort);
            p.Add("_totalcount", dbType: DbType.Int32, direction: ParameterDirection.Output);
            var pageData = new PageDataView<TEntity>();
            pageData.Items = ReadConnection.Query<TEntity>(proName, p, commandType: CommandType.StoredProcedure, commandTimeout: 300).ToList();
            pageData.TotalNum = p.Get<int>("_totalcount");
            return pageData;
        }

        public long Add(T entity, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity", "Add to DB null entity");
            }
            var res = WriteConnection.Insert(entity, transaction: transaction, commandTimeout: commandTimeout);
            return res;
        }

        public long BatchAdd(IEnumerable<T> list, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            if (list == null)
            {
                throw new ArgumentNullException("list", "BatchAdd to DB null entity");
            }
            var res = WriteConnection.Insert(list, transaction: transaction, commandTimeout: commandTimeout);
            return res;
        }

        public virtual bool Update(T entity, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity", "Update in DB null entity");
            }
            return WriteConnection.Update(entity, transaction: transaction, commandTimeout: commandTimeout);
        }

        public virtual bool Remove(T entity, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            if (entity == null)
            {
                throw new ArgumentNullException("entity", "Remove in DB null entity");
            }
            return WriteConnection.Delete(entity, transaction: transaction, commandTimeout: commandTimeout);
        }

        public bool BatchRemove(IEnumerable<T> list, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            if (list == null)
            {
                throw new ArgumentNullException("list", "BatchAdd to DB null entity");
            }
            return WriteConnection.Delete(list, transaction: transaction, commandTimeout: commandTimeout);
        }

        public virtual T GetByKey(object queryId, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            if (queryId == null)
            {
                throw new ArgumentNullException("queryId");
            }
            return ReadConnection.Get<T>(queryId, transaction: transaction, commandTimeout: commandTimeout);
        }

        public virtual IEnumerable<T> GetAll(IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return ReadConnection.GetAll<T>(transaction: transaction, commandTimeout: commandTimeout);
        }

        public virtual IEnumerable<T> GetBy(object sql = null, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return ReadConnection.Query<T>(sql.ToString(), param, commandTimeout: commandTimeout);
        }

        public IEnumerable<dynamic> Query(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return ReadConnection.Query<dynamic>(sql, param);
        }

        public GridReader QueryMultiple(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            return ReadConnection.QueryMultiple(sql, param, transaction, commandTimeout, commandType);
        }

        public int Excute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null)
        {
            return WriteConnection.Execute(sql, param, transaction: transaction);
        }

        public bool Exists(string sqlStr, object param = null)
        {
            return ReadConnection.Query<dynamic>(sqlStr, param).Count() > 0 ? true : false;
        }
    }

 利用工厂模式创建仓库

IFactoryRepository：

    /// <summary>
    /// 创建仓库接口
    /// </summary>
    public interface IFactoryRepository
    {
        /// <summary>
        /// 创建仓库
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <returns></returns>
        IRepository<T> CreateRepository<T>(IDapperContext context) where T : class;
    }

 FactoryRepository：

    /// <summary>
    /// 工厂
    /// </summary>
    public class FactoryRepository : IFactoryRepository
    {
        /// <summary>
        /// 创建Repository
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="context"></param>
        /// <returns></returns>
        public IRepository<T> CreateRepository<T>(IDapperContext context) where T : class
        {
            IRepository<T> repository = new Repository<T>(context);
            return repository;
        }
    }

相关文章：

**Dapper的基本使用 

NetCore+Dapper WebApi架构搭建（一）：基本框架

NetCore+Dapper WebApi架构搭建（二）：底层封装

NetCore+Dapper WebApi架构搭建（三）：添加实体和仓储

NetCore+Dapper WebApi架构搭建（四）：仓储的依赖注入

 

 

神经兮兮的小饼
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值