Best practices when using oracle DB and .NET

Question

What are the best practices or pit falls that we need to be aware of when using Microsoft Oracle provider in a web service centric .NET application?

Answer 1

Some practices we employ based on our production experience:

• Validate connections when retrieving them from the connection pool.
• Write your service code to not assume that connections are valid - failure to do so can cause quite a bit of grief especially in production environments
• Wherever possible, explicitly close and dispose connections after using them (using(conn){} blocks work well)
• In a service, you should use connections for the shortest time possible - particularly if you are looking to create a scalable solution.
• Consider using explicit timouts on requests appropriate to the typical duration of a request. The last thing you want is to have one type of request that hangs to potentially block your whole system.
• Wherever possible use bind variables to avoid hard parses at the database (this can be a performance nightmare if you don't start out with this practice). Using bind variables also protect you from basic SQL-injection attacks.
• Make sure you have adequate diagnostic support built into your system - consider creating a wrapper around the Oracle ADO calls so that you can instrument, log, and locate all of them.
• Consider using stored procedures or views when possible to push query semantics and knowledge of the data model into the database. This allows easier profileing and query tuning.
• Alternatively, consider use a good ORM library (EF, Hibernate, etc) to encapsulate data access - particularly if you perform both read and write operations.
• Extending on the above - don't pepper your code with dozens of individually written SQL fragments. This quickly becomes a maintainability nightmare.
• If you are committed to Oracle as a database, don't be afraid to use Oracle-specific features. The ODP library provides access to most features - such as returning table cursors, batch operations, etc.
• Oracle treats empty strings ("") and NULLs as equivalent - .NET does not. Normalize your string treatment as appropriate for Oracle.
• Consider using NVARCHAR2 instead of VARCHAR2 if you will store Unicode .NET string directly in your database. Otherwise, convert all unicode strings to conform to the core ASCII subset. Failure to do so can cause all sorts of confusing and evil data corruption problems.

Answer 2

The Oracle Providers work fine in an ASP.NET application, but be aware of:

• Matching the right version of the oracle client 32-bit or 64-bit with your application pool
• 32-bit client for a 32-bit app pool, 64-bit client for a 64-bit app pool.
• Permissions - grant the app pool user rights to the oracle client directory (c:\oracle\product\10.2.0\client_1)

This doesn't have anything to do with ASP.NET, but it is important to note that Oracle stores empty string and null both as null, so if you need to know that something was empty and not null, you need to add an additional column to track that...

Answer 3

Some more tips:

• Avoid using Microsoft Oracle provider because it goes out of support (http://blogs.msdn.com/adonet/archive/2009/06/15/system-data-oracleclient-update.aspx)
• If you're commited to Oracle use oracle specific features and link Oracle.DataAccess assembly to your code
• If you're not sure and want to be flexible, use System.Data.Common classes and load oracle provider through