Is there a database connection library out there that will handle multiple types of DB connections and operations (i.e. OleDb, SQLite, ADO, etc)?

Question

What I have is an Access database that we are working on, but we need to convert it to SQLite and send it to another client while keeping the code working for the Access database as well for all others using our program.

Minimum of what I need is something that can handle connections and operations on Access and SQLite databases. Does something like this even exist. And I know the data and queries are different for these databases, I can handle that separately.

Are there any other suggestions for how to handle two separate database and this situation?

EDIT:

What I really need is something where I can do this:

public AnyDBConnection GetConnection(string connectionString)
{
    //I guess somehow this needs to know the DB type (OleDB, SQLite, etc.)
    return new AnyDBConnection(connectionString);
}

A little, but It's not exactly what I'm looking for. See above. — Mike Webb, May 20 '11 at 16:37

score 2 · Accepted Answer · answered May 20 '11 at 16:53

For a lower-level solution, all the ADO.NET classes have common base classes and interfaces, such as DbConnection, IDbConnection, etc. The interfaces are under the namespace System.Data while the abstract base classes are under System.Data.Common.

Most 3rd-part ADO.NET libraries inherit/implement these so that all database-specific connections and commands and so on can be dealt with under these common interfaces. They should each also have a class deriving from DbProviderFactory for a centralized interface for creating the different concrete objects.

You could have a CreateFactory method somewhere that looks up in configuration what database is being used and the connection string, then creates the appropriate factory object for you.

score 0 · Answer 2 · answered May 20 '11 at 16:49

Usually, people go with using an ORM for these kind of scenarios. LLBLGgen is commercial but I think is the most advanced one if you need support for many databases. Also, You can use Enterprise Library:

 public static Database GetDataBaseInstance()
    {
        try
        {
            //Create an object of the Database class and get the database connection info
            Database db = DatabaseFactory.CreateDatabase(SqlConnectionString);
            return db;
        }
        catch
        {
            throw;
        }
    }

Also, take a look at this article. and use it with your code. and make sure that your code is compatible for all database type

score 0 · Answer 3 · edited May 23 '17 at 11:44

I suggest you to use persistence ignorance approach from Domain Driven Design. The purpose of DDD is to decouple the system: model knows nothing about the storage. In DDD you will adapt Repository pattern or better Generic Repository Pattern. This pattern usually use ORM inside (that is actually quite easy to add). So you will be able to save/query your data from any storage.

To sum up

Use DDD
Implement Generic Repository for persistence ignorance
Get ORM like NHibernate or Entity Framework
Add Inversion of Control to select the desired storage

score 0 · Answer 4 · answered May 20 '11 at 17:26

If you have ADO.NET 2.0 providers for all your supported databases, and connection strings defined in the <connectionStrings> section of your config file, you can do something like:

ConnectionStringSettings c = ConfigurationManager.ConnectionStrings[name];
DbProviderFactory factory = DbProviderFactories.GetFactory(c.ProviderName);
using(IDbConection connection = factory.CreateConnection())
{
    connection.ConnectionString = c.ConnectionString;
    ... etc
}

Often you'll want to encapsulate this in general-purpose DAL classes so that, for example, you don't need to instantiate the factory each time you want a connection.

Is there a database connection library out there that will handle multiple types of DB connections and operations (i.e. OleDb, SQLite, ADO, etc)?

4 Answers4