Design Patterns for Multiple Database Scenarios

Question

Our .NET application must connect to two different SQL databases. Some queries will be redirected to the 1st database, and some to the second. Are there any specific design patterns to achieve this. Is there a DataAdapter that can switch at runtime from one database to another.

Answer 1

Encapsulate each database for Strategy - when it comes to databases, we often call them Repositories . Now you can encapsulate both Composite implementations that route the request.

Imagine we have an IRepository interface. You can direct them like this:

public class RoutingRepository : IRepository { private readonly IRepository repository1; private readonly IRepository repository2; public RoutingRepository(IRepository repository1, IRepository repository2) { if (repository1 == null) { throw new ArgumentNullException("repository1"); } if (repository2 == null) { throw new ArgumentNullException("repository2"); } this.repository1 = repository1; this.repository2 = repository2; } public SomeEntity SelectEntity(int id) { if (this.UseRepository1()) { return this.repository1.SelectEntity(id); } else { return this.repository2.SelectEntity(id); } } // more IRepository members can go here... private bool UseRepository1() { // implement routing logic here... } } 

Customers will see the IRepository interface, therefore, according to the Liskov replacement principle , they will never know the difference.