I had a chance to learn about ODP.Net.
There are several ways to connect to Oracle database from within .NET. Let us see which one to choose. Each of those methods having its own advantages and disadvantages.
1.Using .NET Data Provider for OLEDB
This method is used to develop database-independent applications based on ADO.NET 1.1
2.Using .NET Data Provider for ODBC
These seems to be the slowest to connect oracle. This method can help in developing multi-platform database independent applications using ADO.NET or if you want to connect to legacy systems or database systems existing on other platforms.
3.Microsoft’s .NET Data Provider for Oracle
Microsoft claims that this method helps you to make use of advanced and extensive features available in Oracle which were not supported by oledb and Odbc
4. Oracle Data Provider for .NET (ODP.NET)
This provider is developed by Oracle to facilitate developers connecting to Oracle databases with tight integration (along with best performance) and advanced features. This method seems to be more apt to connect to Oracle,as ODP.NET has tight integration with Oracle database.
Which method wins?
1. The last two (.NET Framework data provider for Oracle and ODP.NET) are the preferred methods for working with Oracle databases. These two directly talk with Oracle databases without any mediation layer (like OLEDB or ODBC), thus improving performance.
2.ODP.NET supports all native Oracle data types in the Oracle.DataAccess.Types namespace.he ODP.NET OracleDataAdapter allows you to safely map Oracle data types to .NET data types where there is a potential for data loss.
3. ODP.net supports callback callback method to handle the notification when the cache item gets invalidated(based on a trigger in oracle).
Let us consider a scenario where data is stored in the Oracle database and accessed by an ASP.NET application using ADO.NET. Let us think that we want to cache the data that gets filled up in to combo boxes of country, states, language and related fields are generally static in nature most of times. Here we can use data caching of ASP.Net. But unfortunately, ASP.NET does not allow a dependency to be specified whereby a cache item is dependent on data stored in a database table. The cache validation becomes more challenging when the web server and data base server are on different machines and web applications are deployed using web farms with instances of the same application running on multiple Web servers for load balancing. one way to do this is regular polling of the data to look for invalidate state and update the cache.
4. Look and make sure that you do not run in to connection leak issues with .Net Framework data provider for Oracle
ODP.NET is freely available from the Oracle Technology Network (OTN) at http://www.oracle.com/technology/software/tech/windows/odpnet/index.html. ODP.NET requires Oracle client version 9.2 or later.In most cases, Oracle does not charge any fees for distributing the Oracle client nor ODP.NET with a third-party application
I also looked at the following links to learn more
Improve ODP.NET Performance
Build a .NET Application on the Oracle Database
Optimize Result Set Retrieval Using ODP.NET and Ref Cursors