DEV Community

Mohsen Esmailpour
Mohsen Esmailpour

Posted on • Updated on

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;
    }
}
Enter fullscreen mode Exit fullscreen mode

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")));
Enter fullscreen mode Exit fullscreen mode

Now it's time to create transaction middleware.

public class DbTransactionMiddleware
{
    private readonly RequestDelegate _next;

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

    public async Task Invoke(HttpContext httpContext, SqlConnectionProvider connectionProvider)
    {
    }
}
Enter fullscreen mode Exit fullscreen mode

Here above is the definition of middleware. I'm going to add the necessary codes to handle transaction step by step.

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

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.

IDbTransaction transaction = null;

try
{
    transaction = connectionProvider.CreateTransaction();

    await _next(httpContext);

    transaction.Commit();
}
catch (Exception)
{
    transaction?.Rollback();

    throw;
}
finally
{
    transaction?.Dispose();
}
Enter fullscreen mode Exit fullscreen mode

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

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, @TodoListId)";
        var parameters = new DynamicParameters();
        parameters.Add("Title", todoItem.Title, 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");
    }
}
Enter fullscreen mode Exit fullscreen mode

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
{
}
Enter fullscreen mode Exit fullscreen mode

And decorating our API action with Transaction attribute:

[Transaction]
[HttpPost("todo-item")]
public async Task<IActionResult> Post(...)
{
    ...
}
Enter fullscreen mode Exit fullscreen mode

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>();
}
Enter fullscreen mode Exit fullscreen mode

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();
    });
}
Enter fullscreen mode Exit fullscreen mode

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();
}
catch (Exception)
{
    if (transaction != null)
        await transaction.RollbackAsync();

    throw;
}
finally
{
    if (transaction != null)
        await transaction.DisposeAsync();
}
Enter fullscreen mode Exit fullscreen mode

You can find the source code for this walkthrough on Github.

Discussion (8)

Collapse
danstur profile image
danstur

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.

Collapse
moesmp profile image
Mohsen Esmailpour Author

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.

Collapse
akashkava profile image
Akash Kava

Disposing transaction will rollback it anyway, so no need for this block
catch (Exception)
{
transaction?.Rollback();

throw;
Enter fullscreen mode Exit fullscreen mode

}

Collapse
moesmp profile image
Mohsen Esmailpour Author

Good catch, thanks.

Collapse
alexandis profile image
alexandis

Could you please adjust your code accordingly?

Thread Thread
moesmp profile image
Mohsen Esmailpour Author

Sure

Collapse
thiagobottacin profile image
Thiago Bottacin

In this case, what about Unit of Work pattern?

Collapse
moesmp profile image
Mohsen Esmailpour Author

Well, this is a kind UoW because all data will be saved if the request is processed successfully or nothing will be saved if an exception is thrown.