.net Core 6  配置Dapper上下文类

　　　　　　　　　　　　　　---类似于EF的dbContext

　引入dapper原因：使用ef编写查询linq语句有点麻烦，所以使用dapper编写sql语句较为方便些

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
    {
        public string Configuration { get; set; }   //连接字符串
        public DapperDBContextOptions Value { get { return this; } }
    }
}

　　1-2、DapperDBContextServiceCollectionExtensions　　

/// 

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

　　1-3、IContext 　　

public interface IContext
    {
        // 

        /// Indicates if transaction is started.
        /// 
        bool IsTransactionStarted { get; }

        /// 

        /// Begins transaction.
        /// 
        void BeginTransaction();

        /// 

        /// Commits operations of transaction.
        /// 
        void Commit();

        /// 

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

　　1-4、DapperDBContext

 /// 

    /// Dapper 上下文类  参考 https://www.jb51.net/article/233494.htm   https://blog.csdn.net/weixin_39836063/article/details/110869852
    /// 
    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; }
        /// 

        /// 用于给子类重写的创建连接的抽象方法,   因为这个时候你不知道子类使用dapper连接的是哪种数据库, 
        /// 所以写一个抽象方法, 子类连接哪种数据库,就创建哪种数据库的链接
        /// 
        /// 连接字符串
        /// 
        protected abstract IDbConnection CreateConnection(string connectionString);

        /// 

        /// 从构造函数中接受options
        /// 
        /// 
        protected DapperDBContext(IOptions 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 GetAsync(int id) where T : class, new()
        {
            return await _connection.GetAsync(id, _transaction, _commandTimeout);
        }

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

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

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

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

        public async Task<bool> UpdateAsync(T model) where T : class, new()
        {
            return await _connection.UpdateAsync(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 Query(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return _connection.Query(sql, param, _transaction, true, _commandTimeout, commandType);
        }

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

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

        public async Task QueryFirstOrDefaultAsync(string sql, object param = null, CommandType commandType = CommandType.Text)
        {
            return await _connection.QueryFirstOrDefaultAsync(sql, param, _transaction, _commandTimeout, commandType);
        }
        public IEnumerable Query(string sql, Func 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> QueryAsync(string sql, Func 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 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
    {
        /// 

        /// 使用dapper 连接system_content_project(自己的数据库)库的上下文类
        /// 
        public ProjectDapperContext(IOptions optionsAccessor):base(optionsAccessor)
        {
        }
        /// 

        /// 重写父类的创建连接方法,   因为写父类的时候,不知道你在创建连接的时候要连接那种数据库, 所以写了一个抽象方法, 让你重写
        /// 
        /// 连接字符串
        /// 
        protected override IDbConnection CreateConnection(string connectionString)
        {
            IDbConnection conn = new SqlConnection(connectionString);
            return conn;
        }
    }

    Program中注入Dapper上下文

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

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

　　3-1、通过构造注入

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

        //使用dapper上下文
        private ProjectDapperContext _ProjectDapperContext;
        /// 

        /// //注入dapper上下文类
        /// 
        /// 
        /// 注入dapper上下文
        public ContractCharts(ProjectDapperContext ProjectDapperContext)
        {
            _ProjectDapperContext = ProjectDapperContext;
        }
    }

　　3-2、Dapper使用