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

Question

The ASP.NET web application being created must support two different types of databases, namely Access and MS SQL Server 2008 Express.

I already have connection strings for each type of database stored in web.config, and I have a different web.config value that indicates which one to use. Therefore, I can get the correct connection string without any problems.

The big problem is with database objects. For Access, which I have already implemented, I use the OleDbConnection, OleDbCommand, and OleDbDataReader objects in the code to invoke the database.

It seems like for SQL Server I cannot use these objects, but I will need to use the SqlConnection, SqlCommand and SqlDataReader objects to accomplish almost the same things.

I want to use as much of my current code as possible and not create two separate blocks for each type of database. (I have many methods that take OleDbDataReader as a parameter - for example, I don't want to make 2 of each of these methods.)

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

Is it possible to take my existing code for Access, replace all Ole objects with Db objects, and then apply these objects as the appropriate type depending on the current database type?

- ASP.NET?

, . .

Answer 1

, framework 2.0 DbDataReader, DbDataReader isntead OleDbDataReader, .

SQL, , , , .

, Access , #2010-09-24#, SQL Server , '2010-09-24'. , , , .

Answer 2

, DbProviderFactories. ( System.Data.Common), (, DbConnection DbCommand) . :

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

, , , DbProviderFactories.GetFactoryClasses(). . , , .

factory.CreateCommandBuilder, , , ..