How to write a .Net application that works with bo

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.

---

It looks like Oracle may be about to bring out a senible Fully Managed ADO.NET provider themselfs it may even be easy to install! see here that claims Beta – 2011, Production – End of 2011.

Answer 1

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 2

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 3

Would using ODBC for both be feasible?

Answer 4

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 5

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

Answer 6

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