Using TransactionScope with ASP.NET Core 6 Web API & System.Data.SQLite

Question

The application in question is an ASP.NET Core 6 Web API. It is using SQLite as the database. The goal is to move it to SQL Server some day, so I am trying to be strict about database layers.

One thing that makes it unique is there are multiple SQLite databases it needs, one for the system, and one for the current "event". The system never changes, but based on how things are configured (which could change between calls to the Web API) the event database can change.

There is a _databaseService for the DB layer that will create the connection:

public DbConnection CreateConnection()
{
    var connection = DbFactory.CreateConnection();

    if (connection != null)
    {
        connection.ConnectionString = ConnectionString;
        connection.Open();
        return connection;
    }

    return null;
}

This is used by the DB layer like this:

public int UpdateInvoiceItem(int priceListItemId, int qty, int invoiceItemId)
{
    const string sql = "update INVOICE_ITEM set PRICE_LIST_ITEM_ID = @priceListItemId, QTY = @qty where INVOICE_ITEM_ID =  @invoiceItemId";

    using var conn = _databaseService.GetEventDbHelper().CreateConnection();

    return conn.Execute(sql, new { priceListItemId, qty, invoiceItemId });
}

All is fine and good, until I try to wrap this into a TransactionScope one layer above in the business layer:

public void UpdateInvoice(Invoice invoice)
{
    using var transactionScope = new TransactionScope();

    foreach (var invoiceItem in invoice.InvoiceItems)
    {
        if (invoiceItem.Qty == 0)
        {
            _invoiceDs.RemoveInvoiceItem(invoiceItem.InvoiceItemId);
        }
        else if (invoiceItem.OrgQty != invoiceItem.Qty ||
                 invoiceItem.OrgPriceListItemId != invoiceItem.PriceListItemId)
        {
            _invoiceDs.UpdateInvoiceItem(invoiceItem.PriceListItemId, invoiceItem.Qty,
                invoiceItem.PriceListItemId);
        }
    }

    transactionScope.Complete();
}

All is fine the first time _invoiceDs.UpdateInvoiceItem is called, but the second time it never returns, I assume it is because of the transaction.

What should I be doing so that each time I get the connection it is part of the TransactionScope? Should the DB layer (UpdateInvoiceItem) get the connection and hold on to it for the life of the class (which is set to scoped)? Is there a whole different approach I should be taking?

Answer 1

Assuming that UpdateInvoiceItem will be called many times in UpdateInvoice, in the UpdateInvoiceItem method really surprised me.

using var conn = _databaseService.GetEventDbHelper().CreateConnection(); 

You should first understand the concept of TransactionScope, if you could use _context, your code could be more clean. You can refer the repo.