C# handle multiple database types?

Question

I have an app that needs to be able use either an sqlite3 datebase or an mysql database. The customer wants to be able to choose between the two (obviously one would be local and one would be online).

My thoughts for doing this would be by creating a database interface that has all of the common methods (ExecuteReader, ExecuteScalar, etc) then having a SQLite3Database class that extends this interface and a MySQLDatabase class that extends it as well. On app initialization it would create the database interface depending on settings the customer choose.

However if I do this route, then I loose access to the Server Explorer and the simple methods for adding datasets, etc.

So my questions are... Am I going about this the correct way? Is there a better option that I am missing? And if not, how can I get access to the server explorers usability when im creating the data connection points VIA code?

Thanks.

Answer 1

You can make use of an OR/M which abstracts all the DB specifics for you.

Or, you can create your own Data Access Layer for your application. You could write some DAL-interfaces (which contain for instance a 'GetPersonById' method). Then, you could write several implementations for those interfaces:

• one implementation that targets MySQL
• another implementation that targets SqlLite3.

In your application, you program against the interfaces, not directly to the implementations. Write a factory class which instantates the correct implementation of those interfaces (based on a config setting for instance).

Answer 2

If the application is non-trivial, then I would say you should be looking at ORM tools.

It won't solve 100% of the problem, but it will get you there. For example, NHibernate is the ORM for my current project, and there is very little database specific code.