DbConnection vs OleDbConnection vs OdbcConnection

Question

What are the main advantages of each of the above database connection methods in C# in terms of connecting to multiple possible data sources (being database agnostic)? Also in terms of performance which is likely to offer the best performance across the board?

Finally are there any reasons you would avoid a particular method for a database agnostic application?

The reason I ask is because my application currently uses Ole and I am having a few issues with connecting to certain databases using factories and as such am looking into alternatives. I have heard Odbc is slower than Ole but is there any truth behind this and is it really noticeable in a real world application?

Reason for my interest in this subject is as follows:

My requirements for my current project state that I must have a working data access layer that is capable of connecting to any database without prior knowledge of said database. Therefore I cannot hard code anything specific for any given database in terms of connection. Running dialect specific statements on each given database has been dealt with using an sql query factory type concept. The same goes for substitution and formatting of bind variables.

UPDATE: As it stands I now have a working version of my code which is using ADO.net and database provider factories. This means I am using the base classes as suggested by Adam Houldsworth. The provider is specified in the connection string under the providerName attribute. The connection string is stored in the app.config where it can be retrieved by my database connection class. Provided the correct driver has been installed such as npgsql or the odac package for Oracle then the factory will work fine. Below is a sample of my code showing the basic constructor for a connection object using a provider factory.

private readonly DbFactoryBindVariables m_bindVariables;
private readonly DbProviderFactory m_provider;
private string m_connectionString = String.Empty;
private readonly string m_providerName = String.Empty;
private DbConnection m_dbFactoryDatabaseConnection;


/// <summary>
/// Default constructor for DbFactoryDatabaseConnection.
/// </summary>
public DbProviderFactoryConnection()
{
        m_providerName = ConfigurationManager.ConnectionStrings["ApplicationDefault"].ProviderName;
        m_provider = DbProviderFactories.GetFactory(m_providerName);

        m_dbFactoryDatabaseConnection = m_provider.CreateConnection();

        m_connectionString = ConfigurationManager.ConnectionStrings["ApplicationDefault"].ConnectionString;
        m_dbFactoryDatabaseConnection.ConnectionString = m_connectionString;

        m_bindVariables = new DbFactoryBindVariables(m_dialect.ToLower(), DbFactoryBindSyntaxLoader.Load(this));
}

It may be required to add something similar to the following into the app.config or web.config if it is not already present in the machine.config for your chosen .net framework version.

<system.data>
    <DbProviderFactories>
       <add name="Npgsql Data Provider" 
        invariant="Npgsql" 
        support="FF" 
        description=".Net Framework Data Provider for Postgresql Server"
        type="Npgsql.NpgsqlFactory, Npgsql, Version=2.0.1.0, Culture=neutral, 
        PublicKeyToken=5d8b90d52f46fda7" />
    </DbProviderFactories>
</system.data>

Connection string required:

<add name="ApplicationDefault" connectionString="DATA SOURCE=TNSNAME;PASSWORD=PASS;USER ID=USER;" providerName="Oracle.DataAccess.Client;"/>

At this stage I can now be totally database agnostic provided the correct connection string is used when configuring the clients version of the application.

Answer 1

I would avoid abstracting the connection to the database as you are always targeting the lowest common denominator. Instead, try to abstract the requirement of saving the entities. Each implementation of that abstraction can then be database specific (basically, programming against interfaces).

That said, I have not once experienced an instance where needing to support multiple databases was a hard requirement. In this case, all this aggravation runs into the YAGNI mantra.

An question on comparing OLE DB to ODBC generally can be found here:

what is the difference between OLE DB and ODBC data sources?

Although asking the performance questions upfront is a good thing, the question cannot be answered in the context of your application. Unfortunately, only profiling of both against sample data will give you the answers you need.

There isn't much to note about DbConnection, it is the base class to the other database-specific connection classes.

Have you considered an ORM like NHibernate or a framework like the Enterprise Library Data Access Application Block? These will help you abstract the database (with ORMs, to the point where you don't even need to do any coding in the database).

Update: so far as I can tell from the comments it appears as though your only option is to use the .NET base classes provided (such as DbConnection) or the interfaces (IDbConnection). To my knowledge there isn't anything that can give you the correct connection for a connection string, so you may have to code that part. This way, you can return an OleDbConnection, OdbcConnection, SqlConnection, etc when you detect the connection string, but use them in code as DbConnection or IDbConnection, thus keeping your code agnostic of the underlying database.

Not ideal, but perfectly workable.

Answer 2

Using DbProviderFactory is a good choice if you need to have an agnostic data access layer, without coding the data access more than once.

I don't see any reason you want to avoid it and all the necessary functionality is covered using the base classes on System.Data.Common.

We are using a agnostic data access on Nearforums because we deliver both SQL Server and MySql db scripts. About performance, it depends on the specific connector (Oracle, MySql, PostgreSQL, ...) implementations delivered by the different companies / communities. Most known connectors had been properly tested during several years.