Windows ODBC Datasources and .Net System.Data.Odbc: Which driver is used?

Question

Hi all,

I am confused on how to connect to Oracle via ODBC.

I have created an ODBC datasource named "oracle" in my Control Panel > Administrative Tools, and used the driver "Microsoft ODBC for Oracle".

In my app.config , I have the following connection strings

<?xml version="1.0"?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="MappingAssistantGui2.Properties.Settings.maConnectionString"
      connectionString="Dsn=oracle;uid=ma;pwd=ma" providerName="System.Data.Odbc" />
    <add name="MappingAssistantGui2.Properties.Settings.maConnectionStringSqlServer"
      connectionString="Dsn=sqlserver;uid=sli;pwd=slislisli" providerName="System.Data.Odbc" />
    <add name="MappingAssistantGui2.Properties.Settings.maConnectionStringOracle"
      connectionString="Dsn=oracle;uid=ma;pwd=ma" providerName="System.Data.Odbc" />
  </connectionStrings>
  <startup>
    <supportedRuntime version="v2.0.50727" />
  </startup>
</configuration>

In my code I use the connection string maConnectionString (the first one). I am confuse on what driver is being used underneath:

1. Microsoft ODBC for Oracle
2. System.Data.Odbc
3. Are these two the same?
4. Or are the System.Data.Odbc classes through which I am accessing the driver? I mean, is "Microsoft ODBC for Oracle" an implementation of ODBC access for Oracle, created by Microsoft?
5. Something else?

Do I need to have defined an ODBC datasource to be connecting with System.Data.Odbc namespace to a database (whichever of Oracle, SQL Server, MySQL)?

Thanks!

Answer 1

In the configuration you described you will use both 'drivers'. Technically, System.Data.Odbc is not a driver, is a provider.

To understand what's going on you need to separate the managed side (.Net) from the native side (ODBC). The Microsoft ODBC Driver for Oracle is a driver for ODBC. It allows any application capable of ODBC to connect to Oracle. ODBC is a native technology. On the otehr hand .Net Framwork uses managed data provider for it's data access. One such provider is the System.Data.Odbc provider which allows .Net applications to connect to any ODBC data source. So when your managed application connects to maConenctionString it uses the System.Data.Odbc provider to connect to the ODBC source named 'oracle' and the ODBC manager will load the Microsoft ODBC Driver for Oracle to connect to the actual back end.

Now that being said it is recommended you use native providers whenever possible. So instead of connecting via the ODBC provider to an ODBC data source, is much better if you use directly the respective native provider like the .Net Managed Provider for Oracle (for older frameworks) or the newer System.Data.OracleClient.