Search code examples
asp.netdatabasems-accesssql-server-expressdbcommand

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

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.

Solution

  • 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.