.NET6之MiniAPI(二十六)：封装Dapper

　　在上一篇说过，Dapper是通过扩展IDbConnection来达到实现的，那带来的一个问题就是隔离性不好，比如在做单元测试时，mock就有点困难，所以在实践中，我对Dapper作了扩展，下面分享出来，以供大家参考，同时说明一下，这仅是自己根据自己的需要实现的一种方式而以，不一定是全面和完善的。

　　本扩展的思路是定义接口IDapperPlusDB和实现类DapperPlusDB，在实现类中调用Dapper的方法，使用多少实现多少，这样一来，我们就能在Service层去Mock这个实现类了（因为他有接口IDapperPlusDB)。同时我添加了DataBaseType和DataBaseMark两个属性，来扩展当应用中连接多种数据或，或连接读写分离的数据库时使用。

　　具体代码如下：

/// <summary>
/// 数据库类型
/// </summary>
public enum DataBaseType
{
    None,
    Sqlite,
    Postgre,
    SqlServer,
    Oracle,
    MySql
}

/// <summary>
/// IDapperPlusDB数据库类型 
/// </summary>
public interface IDapperPlusDB : IDisposable
{
    /// <summary>
    /// 连接对象
    /// </summary>
    /// <returns></returns>
    IDbConnection GetConnection();

    /// <summary>
    /// 数据库类型
    /// </summary>
    DataBaseType DataBaseType { get; }
    /// <summary>
    /// 数据库标志
    /// </summary>
    string? DataBaseMark { get; }

    /// <summary>
    /// 查询方法
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="buffered">是否缓存结果</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    IEnumerable<T> Query<T>(string sql, object? param = null, IDbTransaction? transaction = null, bool buffered = false, int? commandTimeout = null, CommandType? commandType = null);
    /// <summary>
    /// 异步查询方法
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="buffered">是否缓存结果</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    Task<IEnumerable<T>> QueryAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
    /// <summary>
    /// 查询单个对象异步方法
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param> 
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    Task<T> QuerySingleOrDefaultAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);

    /// <summary>
    /// 执行方法
    /// </summary>
    /// <param name="sql">映射实体类</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    int Execute(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);

    /// <summary>
    /// 异常执行方法
    /// </summary>
    /// <param name="sql">映射实体类</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    Task<int> ExecuteAsync(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
    /// <summary>
    /// 查询单值
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    T ExecuteScalar<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);

    /// <summary>
    /// 异步查询单值
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    Task<T> ExecuteScalarAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
}
/// <summary>
/// IDapperPlusDB数据库类型 
/// </summary>
public class DapperPlusDB : IDapperPlusDB
{
    /// <summary>
    /// 连接对象
    /// </summary>
    IDbConnection _dbConnection;
    /// <summary>
    /// 构造
    /// </summary>
    /// <param name="dbConnection">连接对象</param>
    public DapperPlusDB(IDbConnection dbConnection)
    {

        switch (dbConnection.GetType().Name)
        {
            case "SqliteConnection":
                DataBaseType = DataBaseType.Sqlite;
                break;
            case "NpgsqlConnection":
                DataBaseType = DataBaseType.Postgre;
                break;
            case "SqlConnection":
                DataBaseType = DataBaseType.SqlServer;
                break;
            case "OracleConnection":
                DataBaseType = DataBaseType.Oracle;
                break;
            case "MySqlConnection":
                DataBaseType = DataBaseType.MySql;
                break;
        }
        _dbConnection = dbConnection;
    }

    /// <summary>
    /// 构造
    /// </summary>
    /// <param name="dbConnection">连接对象</param>
    /// <param name="dataBaseMark">数据库标志</param>
    public DapperPlusDB(IDbConnection dbConnection, string dataBaseMark)
    {
        DataBaseMark = dataBaseMark;
        switch (dbConnection.GetType().Name)
        {
            case "SqliteConnection":
                DataBaseType = DataBaseType.Sqlite;
                break;
            case "NpgsqlConnection":
                DataBaseType = DataBaseType.Postgre;
                break;
            case "SqlClientConnection":
                DataBaseType = DataBaseType.SqlServer;
                break;
            case "OracleConnection":
                DataBaseType = DataBaseType.Oracle;
                break;
            case "MySqlConnection":
                DataBaseType = DataBaseType.MySql;
                break;
        }
        _dbConnection = dbConnection;
    }
    /// <summary>
    /// 数据库标志
    /// </summary>
    public string? DataBaseMark { get; }

    /// <summary>
    /// 数据库类型
    /// </summary>
    public DataBaseType DataBaseType { get; }
    /// <summary>
    /// 连接对象
    /// </summary>
    /// <returns></returns>
    public IDbConnection GetConnection()
    {
        return _dbConnection;
    }
    /// <summary>
    /// 查询方法
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="buffered">是否缓存结果</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    public IEnumerable<T> Query<T>(string sql, object? param = null, IDbTransaction? transaction = null, bool buffered = false, int? commandTimeout = null, CommandType? commandType = null)
    {
        return _dbConnection.Query<T>(sql, param, transaction, buffered, commandTimeout, commandType);
    }
    /// <summary>
    /// 查询异步方法
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param> 
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    public async Task<IEnumerable<T>> QueryAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return await _dbConnection.QueryAsync<T>(sql, param, transaction, commandTimeout, commandType);
    }
    /// <summary>
    /// 查询单个对象异步方法
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param> 
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    public async Task<T> QuerySingleOrDefaultAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return await _dbConnection.QuerySingleOrDefaultAsync<T>(sql, param, transaction, commandTimeout, commandType);
    }

    /// <summary>
    /// 执行方法
    /// </summary>
    /// <param name="sql">映射实体类</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    public int Execute(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return _dbConnection.Execute(sql, param, transaction, commandTimeout, commandType);
    }

    /// <summary>
    /// 异步执行方法
    /// </summary>
    /// <param name="sql">映射实体类</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    public async Task<int> ExecuteAsync(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return await _dbConnection.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
    }
    /// <summary>
    /// 查询单值
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    public T ExecuteScalar<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return _dbConnection.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
    }

    /// <summary>
    /// 异步查询单值
    /// </summary>
    /// <typeparam name="T">映射实体类</typeparam>
    /// <param name="sql">sql语句</param>
    /// <param name="param">参数对象</param>
    /// <param name="transaction">事务</param>
    /// <param name="commandTimeout">command超时时间(秒)</param>
    /// <param name="commandType">command类型</param>
    /// <returns></returns>
    public async Task<T> ExecuteScalarAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null)
    {
        return await _dbConnection.ExecuteScalarAsync<T>(sql, param, transaction, commandTimeout, commandType);
    }

    public void Dispose()
    {
        if (_dbConnection != null)
        {
            _dbConnection.Dispose();
        }
    }
}

　　使用场景一，单数据库使用：

sing Dapper;
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddScoped<IDapperPlusDB, DapperPlusDB>(service =>
{
    return new DapperPlusDB(new SqlConnection(builder.Configuration.GetConnectionString("SqlServer")));
});

var app = builder.Build();
app.MapGet("/answers/{QuestionID}", async (IDapperPlusDB db, int QuestionID) =>
{
    return await db.QueryAsync<AnswerModel>("select * from answers where QuestionID=@QuestionID", new { QuestionID });
});
app.Run();

　　使用场景二，多种数据库使用：

using Dapper;
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddScoped<IDapperPlusDB, DapperPlusDB>(service =>
{
    return new DapperPlusDB(new MySqlConnection(builder.Configuration.GetConnectionString("MySql")));
});
app.MapGet("/answers/{QuestionID}", async (IDapperPlusDB db, int QuestionID) =>
{
    return await db.QueryAsync<AnswerModel>("select * from answers where QuestionID=@QuestionID", new { QuestionID });
});
var app = builder.Build();
app.MapGet("/data/{id}", async (IEnumerable<IDapperPlusDB> dbs, int id) =>
{
    IDapperPlusDB? mssqlDB = null, mysqldb = null;
    foreach (var db in dbs)
    {
        switch (db.DataBaseType)
        {
            case DataBaseType.SqlServer:
                mssqlDB = db;
                break;
            case DataBaseType.MySql:
                mysqldb = db;
                break;
        }
    }
    if (mssqlDB != null && mysqldb != null)
    {
        return new
        {
            MSSqlData = await mssqlDB.QuerySingleOrDefaultAsync<AnswerModel>("select * from answers where id=@id;", new { id }),
            MySqlData = await mysqldb.QuerySingleOrDefaultAsync<CityModel>("select * from city where id=@id;", new { id })
        };
    }
    return new
    {
        MSSqlData = new AnswerModel { },
        MySqlData = new CityModel { }
    };
});
app.Run();

　　使用场景三，读写分离：

using Dapper;
using Microsoft.Data.SqlClient;
using MySql.Data.MySqlClient;
using System.Data;

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddScoped<IDapperPlusDB, DapperPlusDB>(service =>
{
    return new DapperPlusDB(dbConnection: new MySqlConnection(builder.Configuration.GetConnectionString("MySqlRead")), dataBaseMark: "read");
});
builder.Services.AddScoped<IDapperPlusDB, DapperPlusDB>(service =>
{
    return new DapperPlusDB(dbConnection: new MySqlConnection(builder.Configuration.GetConnectionString("MySqlWrite")), dataBaseMark: "write");
});
var app = builder.Build();

app.MapGet("/data/{id}", async (IEnumerable<IDapperPlusDB> dbs, int id) =>
{
    IDapperPlusDB? readDB = null, writedb = null;
    foreach (var db in dbs)
    {
        switch (db.DataBaseMark)
        {
            case "read":
                readDB = db;
                break;
            case "write":
                writedb = db;
                break;
        }
    }
    if (readDB != null && writedb != null)
    {
        return new
        {
            MSSqlData = await readDB.QuerySingleOrDefaultAsync<CityModel>("select * from city where id=@id;", new { id }),
            MySqlData = await writedb.QuerySingleOrDefaultAsync<CityModel>("select * from city where id=@id;", new { id })
        };
    }
    return new
    {
        MSSqlData = new CityModel { },
        MySqlData = new CityModel { }
    };
});
app.Run();

　　想要更快更方便的了解相关知识，可以关注微信公众号