LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@axzxs2001(suwei gui)

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

Last updated at Posted at 2024-02-24

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

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

具体的なコードは以下の通りです：

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：単一データベースを使用：

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：複数のデータベースを使用：


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：読み取りと書き込みの分離：

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

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?