Efficient database access when dealing with multiple abstracted repositories
- by Nathan Ridley
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?
