C# – Nested transaction management .NET

cdatabasedesigndesign-patternsrelational-database

I have an asp.net core project that is divided into the following structure

  • Controller
    • Store
      • Repository

The controller receives requests, and calls the correct Stores that are responsible for actions against the database (Create Users, Update Records, etc)
The way I see it the controller should be agnostic to the database – it should not control the transactions, or even be aware of their existence.

The store creates a transaction, and calls the required repositories, and their required CRUD methods.

This is pretty straight forward. However, often I find myself repeating code – because of the transaction management.

Sometimes I want to add users, and add them to a group – So If I had a GroupsStore, and a UsersStore – I now have a GroupsUsersStore that does exactly the same as those store, but by putting that in one transaction.

This is obviously wrong, but I can't think of a better solution – since transactions can't be nested in sql server, and I want those stores to work independently as well.

How do you solve such issue?

Best Answer

Your Stores sound like a business layer, correct? I would assume that a given single method in the stores might call multiple repos to achieve its work, with everything being rollbackable in the event of an error. If that's the case, then your store should initiate the sql connection and transaction, and hand them down as a dependencies to the repos.

Inside FooStore.cs:

public StoreResult<ResultThing> DoStuff(int stuff) 
{
     using (var connection = GetOpenConnection())
     using (var transaction = connection.BeginTransaction())
     {
          var fooRepo = new FooRepo(connection, transaction);
          fooRepo.DoThing(stuff); // calls connection.Execute(...)
          // add more repo calls here
          transaction.Commit();
          return new StoreResult<ResultThing>();
     }
}

Now, your StoreResult will need to return some kind of success/fail/messaging data to indicate back to the controller that called it what kind of result occurred.

Related Topic