ADO.NET の非同期トランザクション

DbConnectionクラスのメソッドが色々と非同期になっているので、トランザクションを非同期で実装する例

参考：MSDN

通常のトランザクション

• 先ずは「同期」でトランザクションを行うメソッド
  • 処理内容はfuncとして外部から指定できるように

public static TResult Transact<TResult>(DbConnection connection, IsolationLevel isolationLevel, Func<DbTransaction, TResult> func)
{
  connection.Open();
  try
  {
    using var transaction = connection.BeginTransaction(isolationLevel);
    try
    {
      var result = func(transaction);
      transaction.Commit();
      return result;
    }
    catch
    {
      transaction.Rollback();
      throw;
    }
  }
  finally
  {
    connection.Close();
  }
}

非同期トランザクション

• 上記メソッドを「非同期」に変更
  • Func<DbTransaction, TResult> funcを
  • Func<DbTransaction, Task<TResult>> funcAsyncに変更

public static async Task<TResult> TransactAsync<TResult>(DbConnection connection, IsolationLevel isolationLevel, Func<DbTransaction, Task<TResult>> funcAsync)
{
  await connection.OpenAsync();
  try
  {
    using var transaction = await connection.BeginTransactionAsync(isolationLevel);
    try
    {
      var result = await funcAsync(transaction);
      await transaction.CommitAsync();
      return result;
    }
    catch
    {
      await transaction.RollbackAsync();
      throw;
    }
  }
  finally
  {
    await connection.CloseAsync();
  }
}

使用例

• 通常と非同期トランザクションのそれぞれの呼び出し方

string TestTransaction()
{
  using var connection = new SqlConnection("接続文字列");
  return Transact(connection, IsolationLevel.ReadCommitted, transaction =>
  {
    using var select = transaction.Connection.CreateCommand();
    select.CommandText = "SELECT id FROM table";
    var result = select.ExecuteScalar();
    return (string)result;
  });
}

async Task<string> TestTransactionAsync()
{
  using var connection = new SqlConnection("接続文字列");
  return await TransactAsync(connection, IsolationLevel.ReadCommitted, async transaction =>
  {
    using var select = transaction.Connection.CreateCommand();
    select.CommandText = "SELECT id FROM table";
    var result = await select.ExecuteScalarAsync();
    return (string)result;
  });
}