16

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.

CSharpened
  • 11,674
  • 14
  • 52
  • 86

2 Answers2

6

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.

Community
  • 1
  • 1
Adam Houldsworth
  • 63,413
  • 11
  • 150
  • 187
  • Thanks for the good answer. By programming against interfaces I assume that you mean it would be more wise to have an interface that is then implemented multiple times for each datasource? The issue with this is that I will have no prior knowledge of the datasource and will only receive a connection string from the web.config (changes for each configured users setup) which dictates the datasource to connect to. This means I would have to code an implementation for each possible datasource which would seem very heavy overkill if for example we only end up ever needing 4-6 different platforms? – CSharpened Apr 11 '12 at 08:08
  • 1
    @CSharpened You must have prior knowledge of the potential databases your application will work with. If you don't all you can do is use the lowest common connection mechanism and ANSI SQL, so OLE or ODBC. The suitability question is then: of all the databases you have to support, do they offer ODBC or OLEDB access? – Adam Houldsworth Apr 11 '12 at 08:12
  • I will definetly not have prior knowledge in so much as my code needs to be able to simply use whatever connection string is passed in order to make a connection. It is frustrating but these are the requirements I have been given. If it turns out to be unfeasible then that is kind of ok as it simply disproves the concept I have been asked for. Most datasources I have used so far support one or the other at least but there could be an issue with more obscure ones. At this stage I have been asked to provide support for Oracle, PostgreSQL, SQL Server, MySQL and Spatialite plus one or two others. – CSharpened Apr 11 '12 at 08:16
  • I have taken on board your suggestions and have added an update in my OP. Thanks for the help and advice. – CSharpened Apr 12 '12 at 08:42
3

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.

jorgebg
  • 6,560
  • 1
  • 22
  • 31