I found this question here: OLEDB v/s ODBC
Which gave me more information, but did not really answer the question I'm asking, so I shall proceed from there.
I am working in C#. I'll spare you the long story about how I arrived at this conundrum, but basically I'm trying to decide between ODBC and oleDB.
We work with a lot of different clients who have vastly varied Databases (some SQL, some oracle, some something else that I've never heard of and didn't bother to remember the name of)
Now, from what I understand, ODBC is old, and was/is the standard. And now OleDB has come along and... is... different? but accomplishes the same thing (it talks to databases)
Why would I want to use one over the other? ODBC is (according to the above post) cross-platform, which is good, but he offers very little information as to what OleDB offers that ODBC does not.
In my other research, I've found (on the MSDN forums) People saying "use OleDB if you can, if you have to, resort to ODBC" Naturally, three's no reasoning given for this, so I'd like to hear some.
ODBC is a C API for accessing databases. There is a standard for it, it is supported by every major database vendor, it is very well documented, it is cross-platform. OLEDB is a similar interface that uses Microsoft's COM technology instead of the C API. This means that it is only easily useable on platforms that support COM.
At the end of the day, both libraries provide roughly equivalent basic functionality. Indeed, Some OLEDB drivers actually make use of ODBC rather than native database libraries.
So, if you are C# developer, working on Windows, OLEDB is the obvious choice between the two. If you are using C (or C++ not using COM), or need cross-platform support, then ODBC is the better bet.
As a C# developer and because you are accessing many different datasources you should go with OLEDB. I copied the following from this white paper because it gives some hints what to use when:
OLE DB is Not a Replacement for ODBC
The ODBC technology and third-party market have matured to a point at which ODBC is an ideal technology for accessing SQL databases. As a result, an integral part of OLE DB is a new OLE DB driver manager that enables OLE DB consumers to talk to ODBC providers. The following information can guide your choice of which technology to use:
- If you are accessing standard relational databases from a non-OLE environment, ODBC is the best choice.
- If you want to expose a data interface to non-SQL data, OLE DB is the best choice.
- If you are programming in an OLE environment, OLE DB is the best choice.
- If want to build interoperable database components, OLE DB is the only choice.
If you're programming in C#, you will not directly use either one. You'll use ADO.NET in some form.
True, the provider that you specify in your connection string may turn out to be an ODBC provider or an OleDB provider, but this will not matter to your code. ADO.NET will both APIs from your view.
