How to write a .Net application that works with both SqlServer and Oracle (now that System.Data.OracleClient is deprecated)

Question

see also System.Data.OracleClient namespace discontinued?

(Firstly don’t panic yet, System.Data.OracleClient is not being removed by Microsoft yet, however it is not in the client version of .net 4)

This use to be possible by using System.Data.OracleClient, however Microsoft has decided to deprecate OracleClient. (OracleClient is the ADO.NET provider for Oracle developed by Microsoft and ships as a part of the .NET Framework.)

You could use ODP.Net, however do you wish your Sql Server customers to have to install ODP.Net? (Do you wish any of your customers to have to install Oracle software?)

DataDirect is not an option as it costs an arm and a leg; it may just about be affordable if you need to connect a single server to a mainframe. However it is not an option to expect all customers to buy it.

Moving form C# to Java is not a good option, as I am a C# programmer and wish to be able to earn a living!

Like most ISVs that have to support multiple RDBMS we only need a solution that allows us to use the small subset of Oracle that is the same as SqlServer. Therefore System.Data.OracleClient was powerful enough for us.

(Maybe we should just start storing all data in flat files, so that customer’s DBA stop trying to tell us how to write software. Oracle DBAs are the worse!)

---

My real question is how can I write .NET software that talks to Oracle that is us pain free to installed as .NET software that talks to SqlServer. Having to use ODP.NET just makes the oracle client install yet more painful, with yet more to go wrong.

If I was using JAVA I could just use JDBC type 4 drivers. Microsoft provides one for SqlServer and Oracle provides one for Oracle. However it seems that Oracle wishes to make .Net usage as hard as possible and Microsoft wishes to make Oracle usage as hard as possible.

---

So far the best option looks like devArt's dotConnect.

However I am starting to question weather .NET is a good development system for ISV, as sooner or later you always get a customer that demands oracle support. In the Java world they seem to have this problem solved.

Answer 1

Would using ODBC for both be feasible?

Answer 2

There's a big difference between being deprecated and being removed.

If you're just using the subset of functionality that is common to both SQL Server and Oracle, then you should be fine moving through to ASP.Net 4.0 and continuing to program how you do currently.

Using something like EntitySpaces, would let you be relatively provider-agnostic. (it uses OracleClient in it's DAL)

Completely agree with Philippe about Oracle's client software being a pain. I've lost track of the number of times I've installed it and hated the experience. Give me MySQL, SQLServer or even Access in preference.

(Actually, I take back the Access comment) :-)

Answer 3

Aren't people supposed to be using the ADO.NET Entity Framework now? See http://stackoverflow.com/questions/82644/can-you-use-microsoft-entity-framework-with-oracle

Answer 4

In addition to what others have suggested you could consider using the Provider pattern and Oracle's ODP.NET.

Answer 5

As long as you do not invoke an ODP.Net object, you don't need to install ODP if you only access SQL Server.

Answer 6

Have you looked at alternate Oracle providers like devArt's dotConnect? This driver, as an example, is 100% managed code, supports many advanced Oracle features, provides Entity Framework support (that will come some day in ODP.NET, I'm sure).

There are a bunch of other features (like ASP.NET provider model support) that we didn't get until the ODP.NET 11g release. Pretty liberal licensing and reasonably priced. Also comes with it's own profiler, if I remember correctly.

In their tests, performs favorably compared to ODP.NET. I'd love to point you to something open source but, in my experience, once you've bought into Oracle as a database, you've already accepted the fact that you'll be laying down some cash for your database-related tooling.

Answer 7

You might consider using SubSonic 3.0! I have it run my SQLServer, MySQL and SQLite based application and user can switch between these 3 at runtime!

Answer 8

Use the "The Provider Factory Pattern" which is basically using a factory to give a data provider that is completely abstracted away from the database request methods that uses it here is a blogpost with some sample code that shows how to do this also Jean-Paul Boodhoo on Demystifying Design Patterns Part 1 on dnrtv.com shows how to as well.

This is some very cool stuff basically you have a factory that provides a methods for getting a connection

public IDbConnection GetConnection()
{
     IDbConnection connection =  _frameworkDBProviderFactory.CreateConnection();
     connection.ConnectionString = _authenticationSettings.ConnectionString;            
     return connection;
}

though an interface so you can call any type of Database who's connection object implements the IDbConnection interface (SQLServer, MySQL, Oracle, etc.) and it just works.

By abstracting away what DB your using you can even swap them out at run time and your application will never know, it doesn't need to so to connection to an Orical DB, download the ODP.NET, same thing with mysql connector both implement IDbConnection, and write your code against the abstracted connection.

Answer 9

In my experience, you cannot simply deploy the ODP.NET data provider DLL. Oracle requires a Home installation for anything more than the default configuration (for instance, we use LDAP name resolution, requiring an LDAP.ora file in a special Home path).

However, ODP.NET implements the ADO.NET 2.0 standards just fine (DbProviderFactory, etc). And I have programmed against the base classes (DbConnection, DbCommand, etc) without any need for the specific classes for some time at my company.

My suggestion for making this data access work is to use/follow the guidance in the Entlib or use NHibernate.

If you have a logistics or IT problem installing ODP.NET or getting it to your customer/client, I suggest you talk to your IT people and Oracle about solutions for that.

Answer 10

The customer should install odp.net and the Oracle client on there machines. You should not deploy it. Your application will find the needed Oracle dll's in the GAC.

Answer 11

I believe that ODBC can still use both SQL Server and Oracle with some transparency, so I would take a look at using ADO.Net with the ODBC provider. It won't give you all of the performance or features that SqlClient or OracleClient would, but it should be pretty close to the same code for Oracle or SQL Server.

Answer 12

Hi Ian,

One way to easily ensure that the required Oracle client side software (including ODP.NET) is always available on the deployment machine is to embed it with your application. ODP.NET has gotten a lot easier to embed now that XCOPY ODP.NET is available. You can download it from this link:

http://www.oracle.com/technology/software/tech/windows/odpnet/index.html

With XCOPY ODP.NET, all you need to do when you deploy your application is the following:

1) Copy your application to the target machine

2) Run "install.bat" which copies a couple of Oracle DLL's to the target machine (including ODP.NET and the Oracle client side (OCI) software)

3) Run "configure.bat", which does a "gacutil" and updates the registry of the target machine

4) Provide your application with connect string information. You can use the EZCONNECT connect string ("hostname@servicename") or you (or your customer) can share preexisting sqlnet configurations by setting the TNS_ADMIN registry entry or environment variable to point to another Oracle home that has sqlnet connect aliases already configured.

That's it! It is really that simple.

I hope you will take a good look at ODP.NET XCOPY in the link above to see for yourself how easy it is these days to embed ODP.NET with your app.

---

Additional notes:

If you choose not to embed ODP.NET with your application, in both the case of Microsoft OracleClient and in the case of ODP.NET, there needs to be additional Oracle client side (OCI) software installed on any deployment machine. The only difference between the two cases is that when you are using ODP.NET, it also needs to exist on the deployment machine. The good news is that a typical Oracle install on your customer machine will include ODP.NET already.

Now, if your target machine already has ODP.NET installed you don't need to do anything else. You just need to distribute your application. If you do need to install ODP.NET using the standard installer, you can also download it from the link provided above. The standard ODP.NET install only takes a few minutes and configures everything for you.

And again, you can use EZConnect connect strings to make networking configuration a piece of cake, or use the TNS_ADMIN registry entry or environment variable to take advantage of pre-existing connect aliases that your customer is already used to using.

Hope this helps,

Christian Shay

Oracle

Answer 13

Being database independent is a very hard job because there are a lot of specific things (bind variable naming, object quotation, ...). Use a library like NHibernate which puts a layer between your application and the real database.

If you have to connect to both databases, than it's a question of deployment. If you connect to oracle (using System.Data.OracleClient or Oracle.DataAccess.Client) you need oracle client software installed on your machine. The database provider deployed with framework is not enough to connect to an oracle database.

If you connect to oracle, you have to install software from oracle. If you fear the deprecation, than install and use ODP.Net. There are some differences between the 2 oracle database provider.

For better deployment oracle introduced the concept of instant client. This client can deployed using xcopy deployment. Sine oracle 11 the instant client can be bundled with ODP.Net.

The installation of ODP.Net is described in the post of Christian Shay.

The Oracle.DataAccess assembly must not be in die global assembly cache. Put it in your bin directory. The instant client used by Oracle.DataAccess must not be in a different directory on your client machine. Put it in your bin directory. The documentation describes how to configure your application to find a instant client.