MiniAPI(二十六)：Dapperのカプセル化


前回お話ししたように、DapperはIDbConnectionを拡張することで実現されていますが、その結果として生じる問題の一つが分離性の欠如です。例えば、ユニットテストを行う際、mockが少々困難となります。そこで、私はDapperに対して独自の拡張を行いました。以下でその方法を共有し、これは単に私自身のニーズに基づいて実装したものであるため、必ずしも全面的で完璧なものではないことをご了承ください。

この拡張のアプローチは、インターフェースIDapperPlusDBとその実装クラスDapperPlusDBを定義し、実装クラス内でDapperのメソッドを呼び出すことです。これにより、Service層でこの実装クラス(IDapperPlusDBのインターフェースを持つ)をMockすることが可能になります。また、DataBaseTypeとDataBaseMarkという2つのプロパティを追加しました。これにより、アプリケーションが複数種類のデータベースに接続する場合や、読み取りと書き込みを分離したデータベースに接続する場合に拡張が行えます。

具体的なコードは以下の通りです：
```csharp
public enum DataBaseType
{
    None,
    Sqlite,
    Postgre,
    SqlServer,
    Oracle,
    MySql
}

public interface IDapperPlusDB : IDisposable
{
    IDbConnection GetConnection();
    DataBaseType DataBaseType { get; }
    string? DataBaseMark { get; }
    IEnumerable<T> Query<T>(string sql, object? param = null, IDbTransaction? transaction = null, bool buffered = false, int? commandTimeout = null, CommandType? commandType = null);
    Task<IEnumerable<T>> QueryAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
    Task<T> QuerySingleOrDefaultAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
    int Execute(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
    Task<int> ExecuteAsync(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
    T ExecuteScalar<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
    Task<T> ExecuteScalarAsync<T>(string sql, object? param = null, IDbTransaction? transaction = null, int? commandTimeout = null, CommandType? commandType = null);
}

public class DapperPlusDB : IDapperPlusDB
{
    IDbConnection _dbConnection;
    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;
    }
    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 "SqlConnection":
                DataBaseType = DataBaseType.SqlServer;
                break;
            case "OracleConnection":
                DataBaseType = DataBaseType.Oracle;
                break;
            case "MySqlConnection":
                DataBaseType = DataBaseType.MySql;
                break;
        }
        _dbConnection = dbConnection;
    }
    public string? DataBaseMark { get; }
    public DataBaseType DataBaseType { get; }
    public IDbConnection GetConnection()
    {
        return _dbConnection;
    }
    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);
    }
    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);
    }
    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);
    }
    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);
    }
    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);
    }
    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);
    }
    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();
        }
    }
}

```

**使用例1：単一データベースを使用：**
```csharp
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();
```

**使用例2：複数のデータベースを使用：**
```csharp

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();

```

**使用例3：読み取りと書き込みの分離：**
```csharp
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();
```

(Translated by GPT)

元のリンク：https://mp.weixin.qq.com/s?__biz=MzA3NDM1MzIyMQ==&mid=2247485014&idx=1&sn=e852bbd5baa60e199ce2ad4ab62e9bbd&chksm=9f00597ca877d06ac79c8440600dd6e658636f9d1b3fba8a54fb8837d3223b326982e9a02e3b&token=347102560&lang=zh_CN#rd