Transaction middleware in ASP.NET Core

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:

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:

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.

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.

// 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.

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:

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.

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

And decorating our API action with Transaction attribute:

[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:

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:

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:

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.

Blog

Transaction middleware in ASP.NET Core

Mohsen Esmailpour

Join Our Newsletter. No Spam, Only the good stuff.

Related