Transaction middleware in ASP.NET Core
Tags: netcore, aspnetcore, dapper, csharp
Published: 2021-10-15 16:12:46

Sometimes to fulfill a business use case in an HTTP request you need to save data to the database on each step of processing the request and if a part fails, you have to roll back previous parts. In this article, I'm going to show you how middleware can be used to create transaction and commit changes to the database implicitly if no exception is thrown. Let's assume we are using SQL database, Dapper micro ORM and repository pattern for hiding data access layer.

Let's get started by creating connection provider class:
```csharp
public class SqlConnectionProvider
{
    private readonly IDbConnection _connection;
    private IDbTransaction _transaction;

    public SqlConnectionProvider(string connectionString)
    {
        _connection = new SqlConnection(connectionString);
    }

    public IDbConnection GetDbConnection => _connection;

    public IDbTransaction GetTransaction => _transaction;

    public IDbTransaction CreateTransaction()
    {
        if (_connection.State == ConnectionState.Closed)
            _connection.Open();

        _transaction = _connection.BeginTransaction();

        return _transaction;
    }
}
```
We use `SqlConnectionProvider` to create `SqlConnection` object and inject it to repositories and besides `SqlConnection`, it's responsible for creating transaction too. In `ConfigureServices` in `Startup` class we need to register `SqlConnectionProvider`:
```csharp
services.AddScoped((_) => new SqlConnectionProvider(Configuration.GetConnectionString("Default")));
```

Now it's time to create transaction middleware. Here is the definition of middleware. I'm going to add the necessary codes to handle transaction step by step.
```csharp
public class DbTransactionMiddleware
{
    private readonly RequestDelegate _next;

    public DbTransactionMiddleware(RequestDelegate next)
    {
        _next = next;
    }

    public async Task Invoke(HttpContext httpContext, SqlConnectionProvider connectionProvider)
    {
    }
}
```
First, we don't want to open transaction for `GET` request. normally for `GET` request we just fetch data and in `POST`, `PUT` and `DELETE` we modify data.

```csharp
// For HTTP GET opening transaction is not required
if (httpContext.Request.Method.Equals("GET", StringComparison.CurrentCultureIgnoreCase))
{
    await _next(httpContext);
    return;
}
```

The below code is straightforward. We open transaction, calling the next middleware and then commit transaction and finally, disposing transaction.

```csharp
IDbTransaction transaction = null;

try
{
    transaction = connectionProvider.CreateTransaction();

    await _next(httpContext);

    transaction.Commit();
}
finally
{
    transaction?.Dispose();
}
```

Also we need pass to pass transaction to repository, otherwise we face this error:
> "ExecuteNonQuery requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized."

Let's assume we have a todo repository:
```csharp
public class TodoItemRepository : ITodoItemRepository
{
    private readonly SqlConnectionProvider _connectionProvider;
    private readonly IDbConnection _connection;

    public TodoItemRepository(SqlConnectionProvider connectionProvider)
    {
        _connectionProvider = connectionProvider;
        _connection = connectionProvider.GetDbConnection;
    }

    public Task<int> AddTodoItemAsync(TodoItem todoItem)
    {
        const string command = "INSERT INTO TodoItems (Title, Note, TodoListId) VALUES (@Title, @Note, @TodoListId)";
        var parameters = new DynamicParameters();
        parameters.Add("Title", todoItem.Title, DbType.String);
        parameters.Add("Note", todoItem.Note, DbType.String);
        parameters.Add("TodoListId", todoItem.TodoListId, DbType.Int32);

        // Passing transaction to ExecuteAsync method
        return _connection.ExecuteAsync(command, parameters, _connectionProvider.GetTransaction);
    }

    public Task<IEnumerable<TodoItem>> GetTodoItemsAsync()
    {
        return _connection.ExecuteScalarAsync<IEnumerable<TodoItem>>("SELECT * FROM TodoItems");
    }
}
```

Sometimes it's not necessary to open a transaction. We can open transaction more accurately by decorating action with an attribute.

```csharp
[AttributeUsage(AttributeTargets.Method, AllowMultiple = false)]
public class TransactionAttribute : Attribute
{
}
```

And decorating our API action with `Transaction` attribute:
```csharp
[Transaction]
[HttpPost("todo-item")]
public async Task<IActionResult> Post(...)
{
    ...
}
```

And in transaction middleware we can check if action is decorated with `Transaction` attribute or not:
```csharp
public class DbTransactionMiddleware
{
    private readonly RequestDelegate _next;

    public DbTransactionMiddleware(RequestDelegate next)
    {
        _next = next;
    }

    public async Task Invoke(HttpContext httpContext, SqlConnectionProvider connectionProvider)
    {
        // For HTTP GET opening transaction is not required
        if (httpContext.Request.Method.Equals("GET", StringComparison.CurrentCultureIgnoreCase))
        {
            await _next(httpContext);
            return;
        }

        // If action is not decorated with TransactionAttribute then skip opening transaction
        var endpoint = httpContext.Features.Get<IEndpointFeature>()?.Endpoint;
        var attribute = endpoint?.Metadata.GetMetadata<TransactionAttribute>();
        if (attribute == null)
        {
            await _next(httpContext);
            return;
        }

        IDbTransaction transaction = null;

        try
        {
            transaction = connectionProvider.CreateTransaction();

            await _next(httpContext);

            transaction.Commit();
        }
        finally
        {
            transaction?.Dispose();
        }
    }
}

public static class MiddlewareExtensions
{
    public static IApplicationBuilder UseDbTransaction(this IApplicationBuilder app)
        => app.UseMiddleware<DbTransactionMiddleware>();
}
```

And final step is registering middleware just before `UsEndpoints` middleware:
```csharp
public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
{
    ...
   
    app.UseRouting();

    app.UseAuthorization();
    
    app.UseDbTransaction();

    app.UseEndpoints(endpoints =>
    {
        endpoints.MapControllers();
    });
}
```

If you are using Entity Framework, you can open and commit transaction in this way:
```csharp
IDbContextTransaction transaction = null;
try
{
    transaction = await dbContext.Database.BeginTransactionAsync();

    await _next(httpContext);

    await transaction.CommitAsync();
}
finally
{
    if (transaction != null)
        await transaction.DisposeAsync();
}
```

You can find the source code for this walkthrough on [Github](https://github.com/mo-esmp/transaction-middleware).