Database Factory pattern with multiple databases

Question

Hi All, I'm creating a database factory pattern for an application that should be able to work with SqlServer and Oracle. I've used a similar approach suggested in the following article:

http://www.primaryobjects.com/CMS/Article81.aspx

Now, the problem is that my application communicates with multiple databases on the same server. For ex: I'm running few queries against database DB1 and few queries against DB2, both on SqlServer. Using the above article, I could create an instance of one database. How can I use the same approach to work with multiple databases. Can somebody help me with this? Thanks.

Answer 1

Pass in a parameter to the factory that tells it what type you want it to create.

Just be warned: You will want to use XA drivers for all databases if you plan to write and use two-phase commit.

Answer 2

I would suggest that you take a look at the repository pattern. With the use of a repository pattern you can abstract your application away from the infrastructure. This would then allow you to communicate with one or more databases, web services, the file system, or any other datasource that is outside of your domain and applications direct control. With this you could then have an infrastructure that behind the scenes can communicate with multiple data sources. An example of this was an ecommerce application that I recently worked on that allowed me to speak with my local database for the direct product catalog but also spoke with SAP behind the scenes for logging new orders/purchases.

With this you might have code that calls into the repository that looks something like this:

AccountRepository _repository = new AccountRepository();
Account account = _repository.GetAccountByID(32);

Or you can say something along the lines of:

Account account = new AccountRepository().GetAccountByID(32);

The basic idea behind a repository is that your application can make calls into it to get the data that it needs. It would return out actual domain objects (such as an Account in the example above). Or it could return IEnumerable<Account> if a list of Accounts is needed.

An implementation of this where you grab data from two datasources might look like (though I don't suggest co-mingling concerns like this):

public class AccountRepository
{
    public Account GetAccountByID(int accountID)
    {
     Account result = null;

     using(MyDataContext dc = new ConnectionFactory.GetConnection(Databases.DB1))
     {
      result = dc.Accounts.Where(a=>a.AccountID == accountID).FirstOrDefault();
     }

     //result is null...go to the other database to get an Account
     if(result == null)
     {
      using(MyDataContext dc = new ConnectionFactory.GetConnection(Databases.DB2))
      {
       result = dc.Accounts.Where(a=>a.AccountID == accountID).FirstOrDefault();
      }
     }

     return result;
    }
}

My preference for a situation such as this would be to create an application service layer that handles the application logic. A repository should technically be concerned with one data source. You can then have a couple different repository for the couple different datasources. In your application layer would would then new up the instance of GetAccountByID from repository 1 (database 1) and if it was null...the application layer would then dip into the second repository (database 2 for example). The repository should follow the SOLID principles if possible. Where my example clearly breaks the SOLID approach is that there is more than one reason for that implementation of GetAccountByID to change!

But...if this is what you need...there is a way to do it!