How to connect to OLEDB and ODBC in VB.net without using multiple objects

Question

When using ADODB I was able to use a connection string that was either an OLEDB provider or an ODBC connection and use the same connection object. After converting over to ADO.NET and using the OleDB.OleDBConnection I found that you cannot specify an ODBC connection string because the .NET Framework Data Provider for OLE DB does not support the OLE DB Provider for ODBC (MSDASQL). I don't want to use multiple connection objects (System.Data.OLEDB and System.Data.ODBC) for data access because our program has data access in hundreds of places. How are people allowing connections to databases without using multiple connection objects? Thanks!

Answer 1

You can use the DbProviderFactory to abstract the data access. It allows you to obtain an instance of the correct connection type for each connection string object you throw at it.

Given the following connection string config:

<configuration>
  <connectionStrings>
    <clear/>
    <add name="NorthwindSQL" 
     providerName="System.Data.SqlClient" 
     connectionString=
     "Data Source=MSSQL1;Initial Catalog=Northwind;Integrated Security=true"
    />
    <add name="NorthwindAccess" 
     providerName="System.Data.OleDb" 
     connectionString=
     "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Data\Northwind.mdb;"
    />
    <add name="NorthwindODBC" 
     providerName="System.Data.Odbc"
     connectionString="Driver=ODBCDriver;server=ODBCServer;"
  </connectionStrings>
</configuration>

This will give you the appropriate connection:

//Read connection string
ConnectionStringSettings setting = ConfigurationManager.ConnectionStrings["NorthwindAccess"];

//Create connection
DbProviderFactory factory = DbProviderFactories.GetFactory(setting.ProviderName);
using(DbConnection connection = factory.CreateConnection())
{
    connection.ConnectionString = setting.ConnectionString;
    // Use the connection ...
    DbCommand command = connection.CreateCommand();
    // ...

}