.NET6之Mini API【二十六、封装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();

全部.NET6之MiniAPIPDF版本下载地址 https://club.51aspx.com/circle/10569.html

给TA打赏
共{{data.count}}人
人已打赏
.NET

.NET6之Mini API【二十五、Dapper】

2022-7-12 20:58:50

.NET

.NET Framework 2022 年 6 月安全和质量汇总更新

2022-7-12 21:13:34

0 条回复 A文章作者 M管理员
    暂无讨论,说说你的看法吧
个人中心
今日签到
有新私信 私信列表
搜索