What is the difference between ODBC and OleDB?

Question

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.

Danndanna

Answer 1

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.

Denni

Answer 2

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.

Conroy

I think it's important to point out that the second bullet only applies to suppliers of an OLEDB driver (or to some very specialist clients) - the majority of client software never exposes such an interface. And the fourth bullet is simply marketing-speak. – Denni

@McSim: None of these bullet points apply to the question. The question was about using these databases from C#. The OP will not be calling an ODBC C-based API, nor an OLE DB COM-based API. You say you went with OLEDB. Are you calling the COM API? – Godunov

Answer 3

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.

Godunov

I didn't downvote, but I don't think you addressed the question. There are a lot of idiots out there - I've found it best just to ignore them. – Denni

Then I don't understand the question. He's not trying to decide which API to use, is he? I thought he was trying to decide which drivers to use for the most flexibility. I got nothing from the question that suggests the OP is about to start calling the OLEDB COM API. – Godunov

Besides, idiots are not the problem. The problem is smart people who downvote for good reasons - and then won't tell me what the reason is! – Godunov