Dapper的使用

.net 6 配置Dapper上下文类---类似于EF的dbContext（转载）

本文来自博客园，作者：じ逐梦，转载请注明原文链接：https://www.cnblogs.com/ZhuMeng-Chao/p/16339188.html

1、编写基层类，用于生成Dapper上下文类使用--并安装所需包

1-1、DapperDBContextOptions

using Microsoft.Extensions.Options;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace GroupThreeProject.RepositoryLibrary.Db.DapperLibary
{
    public class DapperDBContextOptions : IOptions<DapperDBContextOptions>
    {
        public string Configuration { get; set; }   //连接字符串
        public DapperDBContextOptions Value { get { return this; } }
    }
}

1-2、DapperDBContextServiceCollectionExtensions　

/// <summary>
    /// 用于在ui层或者api层注册上下文的扩展方法
    /// </summary>
    public static class DapperDBContextServiceCollectionExtensions
    {
        public static IServiceCollection AddDapperDBContext<T>(this IServiceCollection services, Action<DapperDBContextOptions> setupAction) where T : DapperDBContext
        {
            if (services == null)
            { throw new ArgumentNullException(nameof(services)); }
            if (setupAction == null) { throw new ArgumentNullException(nameof(setupAction)); }
            // services.AddOptions<DapperDBContextOptions>(); 
            services.Configure(setupAction);
            services.AddScoped<T>();
            return services;
        }
    }

　1-3、IContext 　

public interface IContext
    {
        // <summary>
        /// Indicates if transaction is started.
        /// </summary>
        bool IsTransactionStarted { get; }

        /// <summary>
        /// Begins transaction.
        /// </summary>
        void BeginTransaction();

        /// <summary>
        /// Commits operations of transaction.
        /// </summary>
        void Commit();

        /// <summary>
        /// Rollbacks operations of transaction.
        /// </summary>
        void Rollback();
    }

1-4、DapperDBContext

/// <summary>
    /// Dapper 上下文类  参考 https://www.jb51.net/article/233494.htm   https://blog.csdn.net/weixin_39836063/article/details/110869852
    /// </summary>
    public abstract class DapperDBContext : IContext
    {
        private IDbConnection _connection;
        private IDbTransaction _transaction;
        private int? _commandTimeout = null;
        private readonly DapperDBContextOptions _options;  //使用强类型来接受配置信息

        public IDbConnection Connection { get { return _connection; } }

        public bool IsTransactionStarted { get; private set; }
        /// <summary>
        /// 用于给子类重写的创建连接的抽象方法,   因为这个时候你不知道子类使用dapper连接的是哪种数据库, 
        /// 所以写一个抽象方法, 子类连接哪种数据库,就创建哪种数据库的链接
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        protected abstract IDbConnection CreateConnection(string connectionString);

        /// <summary>
        /// 从构造函数中接受options
        /// </summary>
        /// <param name="optionsAccessor"></param>
        protected DapperDBContext(IOptions<DapperDBContextOptions> optionsAccessor)
        {
            _options = optionsAccessor.Value;

            _connection = CreateConnection(_options.Configuration);  //通过options中存放的连接字符串, 起连接
            _connection.Open();

            DebugPrint("Connection started.");
        }

        #region Transaction

        public void BeginTransaction()
        {
            if (IsTransactionStarted)
                throw new InvalidOperationException("Transaction is already started.");

            _transaction = _connection.BeginTransaction();
            IsTransactionStarted = true;

            DebugPrint("Transaction started.");
        }

        public void Commit()
        {
            if (!IsTransactionStarted)
                throw new InvalidOperationException("No transaction started.");

            _transaction.Commit();
            _transaction = null;

            IsTransactionStarted = false;

            DebugPrint("Transaction committed.");
        }

        public void Rollback()
        {
            if (!IsTransactionStarted)
                throw new InvalidOperationException("No transaction started.");

            _transaction.Rollback();
            _transaction.Dispose();
            _transaction = null;

            IsTransactionStarted = false;

            DebugPrint("Transaction rollbacked and disposed.");
        }

        #endregion Transaction

        #region Dapper.Contrib.Extensions

        public async Task<T> GetAsync<T>(int id) where T : class, new()
        {
            return await _connection.GetAsync<T>(id, _transaction, _commandTimeout);
        }

        public async Task<T> GetAsync<T>(string id) where T : class, new()
        {
            return await _connection.GetAsync<T>(id, _transaction, _commandTimeout);
        }

        public async Task<IEnumerable<T>> GetAllAsync<T>() where T : class, new()
        {
            return await _connection.GetAllAsync<T>();
        }

        public long Insert<T>(T model) where T : class, new()
        {
            return _connection.Insert<T>(model, _transaction, _commandTimeout);
        }

        public async Task<int> InsertAsync<T>(T model) where T : class, new()
        {
            return await _connection.InsertAsync<T>(model, _transaction, _commandTimeout);
        }
        public bool Update<T>(T model) where T : class, new()
        {
            return _connection.Update<T>(model, _transaction, _commandTimeout);
        }

        public async Task<bool> UpdateAsync<T>(T model) where T : class, new()
        {
            return await _connection.UpdateAsync<T>(model, _transaction, _commandTimeout);
        }


        #endregion


        #region Dapper Execute & Query


        public int ExecuteScalar(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.ExecuteScalar<int>(sql, param, _transaction, _commandTimeout, commandType);
        }

        public async Task<int> ExecuteScalarAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.ExecuteScalarAsync<int>(sql, param, _transaction, _commandTimeout, commandType);
        }
        public int Execute(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.Execute(sql, param, _transaction, _commandTimeout, commandType);
        }

        public async Task<int> ExecuteAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.ExecuteAsync(sql, param, _transaction, _commandTimeout, commandType);
        }

        public IEnumerable<T> Query<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.Query<T>(sql, param, _transaction, true, _commandTimeout, commandType);
        }

        public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryAsync<T>(sql, param, _transaction, _commandTimeout, commandType);
        }

        public T QueryFirstOrDefault<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.QueryFirstOrDefault<T>(sql, param, _transaction, _commandTimeout, commandType);
        }

        public async Task<T> QueryFirstOrDefaultAsync<T>(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryFirstOrDefaultAsync<T>(sql, param, _transaction, _commandTimeout, commandType);
        }
        public IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text)
        {
            return _connection.Query(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType);
        }

        public async Task<IEnumerable<TReturn>> QueryAsync<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryAsync(sql, map, param, _transaction, true, splitOn, _commandTimeout, commandType);
        }

        public async Task<SqlMapper.GridReader> QueryMultipleAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryMultipleAsync(sql, param, _transaction, _commandTimeout, commandType);
        }

        #endregion Dapper Execute & Query

        public void Dispose()
        {
            if (IsTransactionStarted)
                Rollback();

            _connection.Close();
            _connection.Dispose();
            _connection = null;

            DebugPrint("Connection closed and disposed.");
        }

        private void DebugPrint(string message)
        {
#if DEBUG
            Debug.Print(">>> UnitOfWorkWithDapper - Thread {0}: {1}", Thread.CurrentThread.ManagedThreadId, message);
#endif
        }
    }

2、仓储层添加(封装)Dapper上下文类

 

 

 

public class ProjectDapperContext:DapperDBContext
    {
        /// <summary>
        /// 使用dapper 连接system_content_project(自己的数据库)库的上下文类
        /// </summary>
        public ProjectDapperContext(IOptions<DapperDBContextOptions> optionsAccessor):base(optionsAccessor)
        {
        }
        /// <summary>
        /// 重写父类的创建连接方法,   因为写父类的时候,不知道你在创建连接的时候要连接那种数据库, 所以写了一个抽象方法, 让你重写
        /// </summary>
        /// <param name="connectionString">连接字符串</param>
        /// <returns></returns>
        protected override IDbConnection CreateConnection(string connectionString)
        {
            IDbConnection conn = new SqlConnection(connectionString);
            return conn;
        }
    }

  Program中注入Dapper上下文

 

 

 

//注入Dapper上下文
    builder.Services.AddDapperDBContext<ProjectDapperContext>(options => { options.Configuration = connection;});

 

3、使用封装好的Dapper上下文类

3-1、通过构造注入

//合同图表数据信息
    public class ContractCharts
    {

        //使用dapper上下文
        private ProjectDapperContext _ProjectDapperContext;
        /// <summary>
        /// //注入dapper上下文类
        /// </summary>
        /// <param name="systemCenterProjectDbContext"></param>
        /// <param name="systemContentProjectDapperContext">注入dapper上下文</param>
        public ContractCharts(ProjectDapperContext ProjectDapperContext)
        {
            _ProjectDapperContext = ProjectDapperContext;
        }
    }

3-2、Dapper使用