Transaction middleware in ASP.NET Core

#netcore #aspnetcore #dapper #csharp

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.

Top comments (8)

danstur • Oct 16 '21

Not a big fan of these implicit trickeries, but if you do go for this you definitely have to handle status codes as well.

Unhandled exceptions in controllers will get converted to a 500 status code which indicate a server error - something that should hopefully happen rarely. If you want to return an error to the client you do this via 4xx status codes.

Using an async using block instead of the manual try/catch and then simply committing if the status code is 2xx is much less code and handles exceptions and status codes in one go.

Mohsen Esmailpour • Oct 16 '21

Well, this middleware is about handling transaction and you can implement exception handling middleware to catch errors and translate to proper status code like this one What every ASP.NET Core Web API project needs - Part 3 - Exception handling middleware, it's about separating concerns.