Efficient database access when dealing with multiple abstracted repositories

Question

I want to know how most people are dealing with the repository pattern when it involves hitting the same database multiple times (sometimes transactionally) and trying to do so efficiently while maintaining database agnosticism and using multiple repositories together.

Let's say we have repositories for three different entities; Widget, Thing and Whatsit. Each repository is abstracted via a base interface as per normal decoupling design processes. The base interfaces would then be IWidgetRepository, IThingRepository and IWhatsitRepository.

Now we have our business layer or equivalent (whatever you want to call it). In this layer we have classes that access the various repositories. Often the methods in these classes need to do batch/combined operations where multiple repositories are involved. Sometimes one method may make use of another method internally, while that method can still be called independently. What about, in this scenario, when the operation needs to be transactional?

Example:

class Bob
{
    private IWidgetRepository _widgetRepo;
    private IThingRepository _thingRepo;
    private IWhatsitRepository _whatsitRepo;

    public Bob(IWidgetRepository widgetRepo, IThingRepository thingRepo, IWhatsitRepository whatsitRepo)
    {
        _widgetRepo = widgetRepo;
        _thingRepo= thingRepo;
        _whatsitRepo= whatsitRepo;
    }

    public void DoStuff()
    {
        _widgetRepo.StoreSomeStuff();
        _thingRepo.ReadSomeStuff();
        _whatsitRepo.SaveSomething();
    }

    public void DoOtherThing()
    {
        _widgetRepo.UpdateSomething();
        DoStuff();
    }
}

How do I keep my access to that database efficient and not have a constant stream of open-close-open-close on connections and inadvertent invocation of MSDTS and whatnot? If my database is something like SQLite, standard mechanisms like creating nested transactions are going to inherently fail, yet the business layer should not have to be concerning itself with such things.

How do you handle such issues? Does ADO.Net provide simple mechanisms to handle this or do most people end up wrapping their own custom bits of code around ADO.Net to solve these types of problems?

Answer 1

Basically you want to utilize a UnitOfWork for this. I personally use NHibernate because their ISession interface is basically a Unit of Work, so it will batch together all commands and send them to the database together (there are additional smarts and object state tracking in there as well, which help with this). So the number of discrete commands sent to the database will depend on the life cycle of your ISession. In a web context I generally go with a Conversation Per Request, which means the ISession is created at the beginning of the request, and flushed (sent to the DB) at the end of the request.

There is a lot of potential here to change the life cycle of the session based on whether or not you need shorter or longer session, and you can also utilize transactions which can have a separate life cycle if you need to.

Answer 2

Consider that your abstracted repositories could be implemented in any number of databases: SQLite, Microsoft SQL Server, direct file access, etc -- even though you know that they are from the same database, it's not reasonable for "Bob" to attempt to make sure each repository can be transactionalized with respect to the other repositories.

Bob should perhaps talk to a DoStuffService that contains concrete implementations of your repositories. Since this service is creating concrete implementations of your repositories, it is capable of creating an appropriate transaction. This service would then be responsible for safely executing a UnitOfWork (thanks, ckramer) on behalf of Bob.