Where/How do I handle different connection types using Repository Pattern with ADO.Net?

Question

I am new to the repository pattern but am creating a repository to connect to two different database types that have the same data structure. Where and how should I handle the connection?

Here are my requirements/constraints/project description

1. I will be connecting to SQL Server 2005 and DB2 (on Iseries)
2. I will be using the repository in a WPF application.
3. Although I would like to use Entity Framework, I cannot. This is because IBM charges a rediculous $11,000 for a product called DB2 Connect which will then give me access to their Datacontext. Because of budgetary constraints, I will be using ADO.Net instead and the IDBConnection interface.
4. The reason why I'm using two different databases is 1) political, 2) because our ERP system is AS400 based which is very limiting so I need to download my data to SQL Server and 3) political.

All of the tutorials I've found discuss opening the connection inside the call to the GetRecords() method. However, that seems to limit me to one database.

1. So, should I pass in my connection object to my GetRecords method?: GetRecords(MyIDbConnection)? This seems to limit me if I decide to use XML (for whatever reason.)

2. Inside my GetRecords method, should I make a call to App.Config to get the connection string? Will this limit me if this repository is compiled into a .dll that doesn't have an app.config?

3. Should I pass in the connection through the constructor instead?

4. Could I use a data adapter somehow?

Please advise.

Thank You.

Answer 1

There is a generic ADO.Net component layer: IDbConnection, IDbCommand, IDbTransaction, IDataReader etc. Both SqlClient and ADO OracleCLient implement these interfaces, so in theory you can have the repository return the generic interface(s) and code agains them.

Answer 2

You might want to consider using the Gateway pattern in conjunction with the Repository pattern to provide a common way for the repository to communicate with the data access layer. The Gateway would provide a standard, platform-agnostic interface that doesn't require the Repository to know anything about underlying data source and its corresponding API. You could inject an instance of the Gateway into your repository using a DI framework. This would allow you to control which type of Gateway was served up to your repository through a central component configuration strategy.

Answer 3

The standard answer is #3 - you should pass the IDbConnection (or DataContext or other connection-like object) through the constructor.

Most implementations also have a default constructor which passes null to the specific constructor. The specific constructor then checks for null and if encountered, either uses the app.config to get a default connection string or uses an actual DI framework to create a fully generic connection. This way your application code doesn't have to be passing in constructor arguments all the time - you use it only for testing/mocking.

By the way - I believe that NHibernate supports DB2, so you might want to consider that as an alternative to raw ADO.NET. The Repository would be implemented much the same way, taking an ISession argument to the constructor.