Keeping an application database agnostic (ADO.NET vs encapsulating DB logic)

Question

We are making a fairly serious application that needs to remain agnostic to the DB a client wants to use. Initially we plan on supporting MySQL, Oracle & SQL Server. The tables & views are simple as are the queries (no real fancy SQL), therefore the question:

1. Use native DB drivers (MySQLDbConnection etc.) and encapsulate the logic of executing queries and processing results or
2. Use a generic OleDbConnection

Obviously option 2 involves no overhead, but I presuming the performance is not as great as with native access?

Answer 1

Note: This answer is relevant if you decide to use basic ADO.NET 2 functionality instead of an ORM (such as Entity Framework or NHibernate) or LINQ to SQL.

Let's assume you've got a connection string defined in your app.config:

<connectionStrings>
    <add name="SomeConnection"
         providerName="System.Data.SqlClient"
         connectionString="..." />
</connectionStrings>

Notice the presence of the providerName attribute and its value. You could also put in a value for another DB provider, e.g. System.Data.SQLite.

(Note that non-standard providers, i.e. those that are not in the .NET Framework by default, need to be registered first, either in app.config or in the client machine's machine.config.)

Now, you can work with the specified database in a completely provider-agnostic fashion as follows:

using System.Configuration;  // for ConfigurationManager
using System.Data;           // for all interface types
using System.Data.Common;    // for DbProviderFactories

var cs = ConfigurationManager.ConnectionStrings["SomeConnection"];
//                                              ^^^^^^^^^^^^^^^^

var factory = DbProviderFactories.GetFactory(cs.ProviderName);
//                                           ^^^^^^^^^^^^^^^

using (IDbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = cs.ConnectionString;
    //                            ^^^^^^^^^^^^^^^^^^^
    connection.Open();
    try
    {
        using (IDbCommand command = connection.CreateCommand())
        {
            ...  // do something with the database
        }
    }
    finally
    {
        connection.Close();
    }
}

Note how this code only works with interface types. The only place where you indicate a particular DB provider is through the providerName attribute value in the app.config file. (I've marked all the places where a setting from app.config is taken with ^^^s.)

---

Further reading:

• Generic Coding with the ADO.NET 2.0 Base Classes and Factories:
  similar to my answer, but goes into more detail.

• ADO.NET Managed Providers and DataSet Developer Center:
  includes, among other things, an index of available ADO.NET database providers.

Answer 2

IMHO using an ORM is a good design decision in order to have a database agnostic application. Switching database might be as easy as changing a config setting and connection string.