ASP.NET: How to support two database types in one application? (Access, MS SQL Server 2008 Express)

Question

The ASP.NET web application I am developing needs to support two different types of databases, namely Access and MS SQL Server 2008 Express.

I already have connection strings for each database type stored in web.config, and I have another web.config value that specifies which one to use. So I can get the proper connection string with no problem.

The big problem comes with the database objects. For Access, which I have already implemented, I am using the objects OleDbConnection, OleDbCommand and OleDbDataReader in the code to make the database calls.

It appears that for SQL Server, I can't use those objects, but rather I would need to use the objects SqlConnection, SqlCommand and SqlDataReader to do essentially the same things.

I want to reuse as much of my current code as possible and not have to create two separate blocks for each database type. (I have a lot of methods that take an OleDbDataReader as a parameter - I do not want to have to make 2 of each of those methods, for example.)

I noticed that the connection objects both inherit from DbConnection. And the same is true for the data readers (DbDataReader) and the commands (DbCommand).

Would it be possible to take my existing code for Access, replace all of the Ole objects with the Db objects, and then cast those objects as the proper type depending on the current database type?

Are there any best practices for supporting two database types in one ASP.NET application?

I can add some of my code if that would help. Thanks.

Answer 1

Yes, from framework 2.0 all data readers inherit from the DbDataReader class, so your methods could take a DbDataReader isntead of an OleDbDataReader, and you could use the methods with any database.

However, the databases have different dialects of SQL, so you either have to stay on a narrow path of features that work in all databases that you use, or have separate queries for some tasks.

A specific example of differences is that Access uses data literals like #2010-09-24# while SQL Server uses date literals like '2010-09-24'. Generally most that has to do with dates differs.

Answer 2

The link you're likely missing is the functionality of the DbProviderFactories class. Using this class (and associated helpers also in System.Data.Common), you can abstract the provider and use references to the base classes (such as DbConnection and DbCommand) to do the work. It'd look something like this:

private void DoSomething(string provider, string connectionString, string something)
{    
    DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
    DbConnection connection = factory.CreateConnection();
    connection.ConnectionString = connectionString;
    DbCommand command = connection.CreateCommand();
    command.CommandText = something;
    DbDataReader reader = command.ExecuteReader();
    //etc...
}

The provider name is a bit tricky to acquire, but should be the invariant class name that matches one of those returned by DbProviderFactories.GetFactoryClasses(). Or, you can simply hard code them. They don't change much, but it is a magic string embedded in your code and may cause issues eventually.

Additional features can be accessed through factory.CreateCommandBuilder that can help you traverse the differences in how the providers handle things like parameters and such.