Database transaction and separation of concerns

Question

Apllication structure:

Business layer

public interface IOrderDataService 
{
    void Save(Order order);
}

public interface IOrderLineDataService 
{
    void Save(OrderLine orderLine);
}

public class OrderManager
{
    private IOrderDataService _OrderDataService;
    private IOrderLineDataService _OrderLineDataService;

    public void Create(Order order, List<OrderLine> lines)
    {
        _OrderDAtaService.Save(order);

        foreach(var line In lines)
        {
            line.Id = order.Id;
            // some other business logic
            _OrderLineDataService(line);
        }
    }             
}

Data layer implements "DataService" interefaces from the business layer

public class SqlOrderDataService : IOrderDataService 
{
    void Save(Order order) {};
}

public class SqlOrderLineDataService : IOrderLineDataService 
{
    void Save(OrderLine orderLine) {};
}

Now I want that creation of order will use SqlTransaction for creating order and order lines. But..

If I wrap a method OrderManaget.Create with SqlTransactionScope - it will work, but then business layer will depend on sql database.

If I create a wrapper method which create both Order and OrderLines. And use transaction there - it will work

public interface IOrderManagerDataService 
{
    void Save(OrderLine orderLine, OrderLine lines);
}

But in this case business logic will be in Sql implementation of data layer. Which violate a separation of concerns and I want keep it on business layer.

Do you have some advise for this problem?

Answer 1

If you truly want to keep all of it separate, you could create your own TransactionScope interface and use that in your Create Method.

The implementation of said interface then uses the SqlTransactionScope to do the "real" work.

Answer 2

What you need is a Unit of Work for your Repositories. The Unit of Work would have an interface similar to

public interface IUnitOfWork
{
    ITransaction Start();
    void Commit();
    void RollBack();
}

then your save method on IOrderDataService and IOrderLineDataService would take an ITransaction, becoming:

public interface IOrderDataService 
{
    void Save(Order order, ITransaction trans);
}

public interface IOrderLineDataService 
{
    void Save(OrderLine orderLine, ITransaction trans);
}

By having a unit of work you can avoid knowing about a SQL transaction and program to the interface while still being able to use a SQL transaction in the specific implementation.

Answer 3

Persisting data to a date store (DB or other) isn't truly business logic, so I would push the SqlTransactionScope as close to the data layer as possible as it is controlling how the data is persisted/transacted. Your last method of:

void Save(Order order, List<OrderLine> orderlines)

This makes the most sense as the SqlTransactionScope would be implemented in that method. The business layer could take the results of the save and perform any mutations/transformations/derivations as needed.

One could also keep the individual save methods for Order and OrderLines if users are allowed to update those individually if desired.

Answer 4

If you want to avoid SqlTransaction you may use TransactionScope from System.Transactions. This will have the added benifit that any systems that use MSDTC may use the same transaction. It also decouples your database from your business layer allowing the business layer to logically group your transactional data.

Answer 5

I post here my currents thought about that problem.
So after I started implementing own IDatabaseTransaction I realize that root of the problem not in the transaction, but in the fact that my database layer still contains some business logic(generate some default values while inserting new rows).

So my new decision was create and generate all needed data on business layer and then pass that information as parameters to the one method of data layer where code will be executed inside transaction

public interface IOrderCreationDataService 
{
    void SaveFullOrder(Order order, List<OrderLine> orderLines);
}

public class SqlOrderCreationDataService : IOrderCreationDataService 
{
    void SaveFullOrder(Order order, List<OrderLine> orderLines) 
    {
        using(var scope = new TransactionScope())
        {
            this.SaveOrder();
            this.SaveorderLines();
        }
    };
}