What are the differences between ODBC and ADO.NET

Question

I'm devolving a new module that will connect to an Oracle DB over .net 4.5.1.

1. Should I use ODBC or ADO.NET and what are the differences between them?

2. I read that ADO.NET requires an additional installation of an Oracle's client. Is there any way to use the API without installing the additional module?

Answer 1

ODBC is a generic, database-agnostic API for C/C+/C++/ObjectiveC-based connections to DBMS, optionally via ODBC driver manager, which typically handles translation between older and newer ODBC API calls and conversion between codepages/charactersets, thence via DBMS-specific ODBC Drivers, which translate the ODBC API calls to the DBMS-specific API and/or DBMS-specific client layer.

C/C+/C++-based tool
-> [optional] ODBC driver manager
   -> ODBC driver 
      -> DBMS libraries
         -> DBMS

ADO.NET is a generic, database-agnostic API, for C#-based connections to DBMS, via DBMS-specific ADO.NET Providers, which translate ADO.NET API calls to the DBMS-specific API and/or DBMS-specific client layer.

C#-based tool
-> ADO.NET Provider
   -> DBMS libraries
      -> DBMS

(JDBC and OLE DB are similar generic, database-agnostic API, respectively for Java- and Visual Basic-based connections to DBMS.)

Database vendors often provide "checkbox" (meaning, "yes, we have that") ODBC drivers and ADO.NET providers, as well as JDBC drivers and/or OLE DB providers, which are not necessarily the best performing nor most fully featured options.

My employer produces a portfolio of high-performance drivers and providers, supporting a wide range of DBMS; comparison testing with free two-week evaluation license is encouraged. "Single-Tier" options generally require the presence of the DBMS client on the same host; "Multi-Tier" options generally remove this requirement on the data consuming host.

C#-based tools cannot connect directly to ODBC drivers, but they can use an ADO.NET Provider for ODBC Data Sources to bridge these APIs. There is no speed advantage inherent to ODBC vs ADO.NET, but there is some speed loss for such bridged solutions, due to the extra layer of API translation.

C#-based tool
-> ADO.NET Provider
   -> ODBC driver 
      -> DBMS libraries
         -> DBMS

(Microsoft's programming tools use the a Windows built-in ADO.NET-to-ODBC Bridge Provider invisibly and automatically, when you choose an ODBC data source as the target of a C# programming project. My employer also produces such bridge providers, which serve better in many situations.)

Answer 2

ODBC is a generic provider, ODBC drivers are available for almost any data source - even simple CSV-Files.

As a drawback you have fewer functions than in ADO.NET and (at least in theory) less performance.

ADO.NET is more strict to connect to a relational database. Please note, the Oracle provider from Microsoft is deprecated, you should not use it for new projects.

Just for completeness, there is also a third provider called OLE DB (see namespace System.Data.OleDb). The Microsoft OLE DB Provider for Oracle (msdaora) is also deprecated. The provider from Oracle OraOLEDB.Oracle has still full support.

For an state-of-the-art connection to Oracle you should use the "Oracle Data Provider for .NET" (ODP.NET), you can download it from here: 64-bit Oracle Data Access Components (ODAC) Downloads

All ODBC, OLE DB and ODP.NET require an Oracle Client installation. The only way to bypass it, is to use the "ODP.NET Managed Driver". You can download it also from the same location as above.