Things I have is definitely an Access database that people are focusing on, but we have to convert it to SQLite and send it to a different client and keep the code employed by the Access database too for those others using our program.

The least things i require is something which are designed for connections and procedures on Access and SQLite databases. Does something similar to this even exist. And That I be aware of data and queries will vary of these databases, I'm able to handle that individually.

Are there more recommendations for how to deal with two separate database which situation?


Things I actually need is one thing where I'm able to do that:

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

Usually, people opt for utilizing an ORM of these type of situations. LLBLGgen is commercial however i think is easily the most advanced one if you want support for a lot of databases. Also, You should use Enterprise Library:

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

Also, have a look at this article. and employ it together with your code. and make certain that the code works with for those database type

For any lower-level solution, all of the ADO.Internet classes have common base classes and connects, for example DbConnection, IDbConnection, etc. The connects they are under the namespace System.Data as the abstract base courses are under System.Data.Common.

Most 3rd-part ADO.Internet libraries inherit/implement these to ensure that all database-specific connections and instructions and so forth could be worked with under these common connects. They ought to each in addition have a class drawing from DbProviderFactory for any centralized interface for creating the various concrete objects.

You might have a CreateFactory method somewhere that appears up in configuration what database has been used and also the connection string, then produces the right factory object for you personally.

It is best to to make use of persistence ignorance approach from Domain Driven Design. The objective of DDD would be to decouple the machine: model knows nothing concerning the storage. In DDD you'll adapt Repository pattern or better Generic Repository Pattern. This pattern usually use ORM inside (that's really really simple to include). Which means you will have the ability to save/query your computer data from any storage.

To summarize

  • Use DDD
  • Implement Generic Repository for persistence lack of knowledge
  • Get ORM like NHibernate or Entity Framework
  • Add Inversion of Control to choose the preferred storage

For those who have ADO.Internet 2. companies for your supported databases, and connection strings defined within the <connectionStrings> portion of your config file, that you can do something similar to:

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

Frequently you will want to encapsulate this generally-purpose DAL classes to ensure that, for instance, you don't have to instantiate the factory every time you desire a connection.