I am a MS guy & prefer to work & express myself using MS technologies. Lately things changed. I had to work on a product that supported Oracle DB. It has been a long time since I had written code to connect to Oracle DB, less even to install an Oracle Database. Luckily for me Oracle DB was already installed. So I set out to connect to Oracle. I found that there are quite a few options to connect to Oracle. I am going to discuss them in this post.
1) You can do it via Microsoft .NET data provider for ODBC / OLEDB. This look like old way of communication to me especially when ADO.NET provides the powerful data provider model.
2) Microsoft’s data provider for Oracle – This is installed by default while installing .NET Framework / VS.NET. The provider resides in System.Data.OracleClient assembly. This requires the installation of Oracle client software.
(N.B. If you are using .NET 1.0 / VS.NET 2002 you can download the provider here. Oracle Client software seems to be specific to version of Oracle, for instance you can get access to 10g client from here & 9i Client from here. I haven’t tried the backward compatibility for the Oracle Client Software i.e. accessing 9i database from 10g client).
3) Oracle Data Provider for .NET (ODP.NET) is the ADO.NET provider coming straight from Oracle. It seems to be better in Performance & more close to Oracle in terms of available features. You can download the same here. Note this bundle also includes ASP.NET Provider, Oracle Services for Distributed Transactions, support for .NET 2.0 & 1.x. I tried installing Oracle 11g ODAC, it works perfectly fine with VS.NET 2008 & I was able to connect to my 9i Oracle instance from it. Oracle also provides Developer Tools for Visual Studio (ODT.NET), which for instance allows you to access Oracle from server explorer. A detail list of ODT features can be found here. ODT also comes bundled with ODP, & the same can be downloaded from here. The base assembly for ODP.NET is Oracle.DataAccess.
(N.B. After installation you of Oracle Client / ODP you need to create a file named TNSNAMES.ORA in the %Oracle Installation%/Network/Admin folder).
4) Of course I didn’t use either of above. We are using a O/R mapper & one which seems to be reasonable at present is NHibernate. You can download its latest version 2.0 from here. NHibernate 2.0 wiki is here. Now the question is how to you make NHibernate use ODP.NET? Simplest solution is given here.
Hope this would save your valuable time.
Niraj Bhatt - Architect's Blog 
My question is how does one distribute a WPF or Windows Forms application to users’ desktops without installing the full blown Oracle Client suite on their machine?
With a web app this is easy. The client is on the web server.
With terminal server deployment and citrix, it’s not an issue as the client software is on the distribution machine – one place to manage and no issues with administrative privileges.
So in my situation, trying to use ClickOnce or a setup.exe style deployment directly to the client, what do I need to do to ensure ODP.Net will work properly?
What’s the fundamental glue between the Oracle.DataAccess.Client library and the db server? does it need client software? does it simply need the tnsnames.ora file that maps the SID and Host to the connection’s alias?
Any thoughts?
Hi Trey,
Normally this can be tackled by creating a distribution layer over WCF or ASMX. Typically they will form the App Server for you, and the only place where you will need to install ODP.NET.
Coming to your other observations, we normally use Citrix products for zero installation access (but need to factor in the cost involved). ClickOnce is reasonably good at getting delta updates for client applications & surely makes maintenance / patching easier. When you install ODP.NET, it will install the required client software, and only thing you will need to do is configure your tnsnames.ora file.
Hope that helps.
First time on earth…
My last lecture
When ODAC ,Oracle Sever 11GR2, Win HPC Server 2008 Cometogether…
Oracle & ilOUG DBA North Forum
http://www.iloug.org.il/DBA_NorthForum.php
AGENDA
•A background on HPC architecture
•Integration with compute nodes with ODACCLIENT and ORACLE DB
•ODT & ODAC-Deployment on ComputeNodes
•Guidelines to ODP.NET Performance &improving latency.
•BONUS
–Performance Tuning with ODT
–UsingCIFS Protocol for RMAN backups On windows
dAVID ITSHAK
Firstly, interesting blog.
Secondly, I do not think creation of a tnsnames.ora file is necessary if you install ODP.NET. You can always use a tnsless connection string. Something along the lines of
var oc = new OracleConnection
{
ConnectionString =
“Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=unixservername)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbServiceName)));User Id=userid;Password=pwd;”
};