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

EDIT: The fully managed ODP.NET is now available in production. It is very small (less than 10MB) and is not dependent on other files. You can obtain it here:

http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

Original answer:

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

Feel free to provide your feedback on this and other future features at our feature request website:http://apex.oracle.com/pls/apex/f?p=18357:46

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.

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) :-)