I recently had the not-so-great-or-fun pleasure of a pure .Net to Oracle application show-down :) And yes, I say that with some pun.
In summary, keeping to proper form, I developed a DataProviderFactory that provided the following:
- SQL Server native client API
- Oracle
- BDC to SQL Server
- BDC to Oracle
Now, BDC = Business Data Catalog - a new service provider native to MOSS (Micrsoft Office SharePoint Server) 2007. And trust me - the BDC ROCKS!!!
Back to providers.
My provider factory basically provided classes for base types and class bases - the lowest level. In the solution I coded specific classes for the given provider (Oracle, SQL, etc) and within the Assembly.cs for that class - I'd reference the appropriate base class. All run-of-the-mill best practice stuff. Right? Well, almost.
After several hours of seeing varrying results from the Oracle database, I began to get a little suspicious - and sure enough - the provider with .Net is not all things .Net - at least not for Oracle.
If you are coding for a Oracle specific application - check out this link for specifics on the Oracle Data Provider for .Net - or the ODP. The API is 'similar' - but only SIMILAR - it is not the same. I invariably tore out the .Net System.Data.OracleClient and replaced it with the Oracle .Net provider and all came together - but this was not obvious or intuitive.
HTH's another weary developer struggling to sort out why - for lack of a better way to say it - when dealing with Oracle - it's not all things .Net.