How to choose an Oracle provider for .Net application?

Question

I am trying to figure out what is the best way to connect an (existing) ASP.Net application to an Oracle database to read its dictionary information.

There are simply too many possibilities:

• MS Data Provider for Oracle (requires 8.1.7, namespace System.Data.OracleClient)
• Oracle Data Provider for .NET (requires 9.2, namespace Oracle.DataAccess)
• Oracle Provider for OLE DB
• MSDASQL and ODBC

As my current app uses MSSQL server, further options would be:

• Linked Server, access via server..user.object
• Linked Server via OPENROWSET

There are a couple of questions on similar topics on SO, but only some have accepted answers.

What's your experience with each of the drivers? What are their pros and cons?

Of course Oracle is recommending ODP.Net. Is the requirement of version 9.2 (or higher) a problem today?

Answer 1

Dump OLE DB and ODBC options, if you have direct data access provider there is no need in using OLE DB or ODBC.

I recommend Oracle Data Provider for .NET.

Answer 2

I too recommend ODP.NET. Choose the latest provider (http://www.oracle.com/technology/tech/windows/odpnet/index.html). It can connect with an Oracle 9.2 database or a newer release of the database.

The MS Data Provider for Oracle is very limited. You can't work with arrays for example and user defined types. And why would Microsoft provide good support for connecting to Oracle?

You can also check out the provider of devart: http://www.devart.com/dotconnect/oracle/ . It supports the entity framework.

Answer 3

Microsoft just announced it is deprecating System.Data.OracleClient.

I think that leaves you with a few choices (at least):

• ODP.NET (free, from Oracle)
• DevArt
• DataDirect

DevArt also has a LINQ to Oracle implementation which could be interesting to you.