The ASP.Internet web application I'm developing needs to facilitate two various kinds of databases, namely Access and MS SQL Server 2008 Express.

I curently have connection strings for every database type saved in web.config, and that i have another web.config value that identifies which to make use of. In order to obtain the proper connection string quickly and easily.

The large problem has the database objects. For Access, which i've already implemented, I'm while using objects OleDbConnection, OleDbCommand and OleDbDataReader within the code to create the database calls.

It seems that for SQL Server, I can not use individuals objects, but instead I will have to make use of the objects SqlConnection, SqlCommand and SqlDataReader to complete basically exactly the same things.

I wish to reuse because my current code as you possibly can without having to create two separate blocks for every database type. (I've got a large amount of techniques that take an OleDbDataReader like a parameter - I don't want to need to make 2 of every of individuals techniques, for instance.)

I observed the connection objects both inherit from DbConnection. And this is also true for that data visitors (DbDataReader) and also the instructions (DbCommand).

Will it be easy to take my existing code for Access, replace all the Ole objects using the Db objects, after which cast individuals objects because the proper type with respect to the current database type?

What are the guidelines for supporting two database types in a single ASP.Internet application?

I'm able to then add of my code in the event that is needed. Thanks.

Yes, from framework 2. all data visitors inherit in the DbDataReader class, so that your techniques might take a DbDataReader isntead of the OleDbDataReader, and you could utilize the techniques with any database.

However, the databases have different dialects of SQL, which means you either need to remain on a narrow path of features that actually work in most databases that you employ, or have separate queries for many tasks.

A particular illustration of variations is the fact that Access uses data literals like #2010-09-24# while SQL Server uses date literals like '2010-09-24'. Generally most which has related to dates differs.

The hyperlink you are likely missing may be the functionality from the DbProviderFactories class. By using this class (and connected assistants also in System.Data.Common), you are able to abstract the provider and employ references towards the base classes (for example DbConnection and DbCommand) to complete the job. It'd look something similar to 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 title is a little tricky to get, but ought to be the invariant class title that suits among individuals came back by DbProviderFactories.GetFactoryClasses(). Or, you can just hard code them. They do not change much, however it is a miracle string baked into your code and could cause issues eventually.

Capabilities could be utilized through factory.CreateCommandBuilder that will help you traverse the variations in the way the companies handle such things as parameters and the like.