Querying Oracle from .Net

So you need to connect to Oracle directly because the Linked Server in MS Sql Server is causing locks that refuse to release till you bounce SQL Server. THATS BAD! now consider it happened on our shared production clustered instance. Well ... you just have to wait till everyone ... globally has agreed you may. Fairly it does impact them, but your production system is down till then.

As you can imagine, major architecture shift we are on, has now been put into hyper drive. So less MS Sql Server, especially shared instances. Role in the new fad, SOA, Mongo etc. Granted SOA has been around, but were working at doing it right.

This is just a short how to, as I have to connect my new micro service to get the info from Oracle we were getting through the Linked Server. I found the documentation on getting going rather ropey.

So for a basic connection to Oracle. Assuming Windows x64, .Net 4

1. Download "Instant Client Package - Basic Lite" and install it.
2. Download "Oracle Data Access Components (ODAC) Downloads" and extract that to a useful location
3. Reference the Oracle.DataAccess.dll from your project (useful location\ODAC1120320Xcopy_x64\odp.net4\odp.net\bin\4\Oracle.DataAccess.dll)
4. Forget all that tnsnames.ora stuff you keep reading about all over.
5. Construct your connection string like this e.g.

   string oradb = "user id=myUserName;password=MyPassword;data source=" +
                              "(DESCRIPTION=" +
                              "(ADDRESS=(PROTOCOL=tcp)(HOST=name.of.server.com)(PORT=1521))" +
                              //Or other port number if not default 
                              "(CONNECT_DATA=(SERVICE_NAME=myDatabase))" +
                              ");";
   

6.  Open a new connection e.g. using (var conn = new OracleConnection(oradb))
7. Query away.
8. Beware the memory leaks. Close the and dispose the connection for each query, long running connections with lots of commands executed against it tend to leak like a sift.

References:

1. http://www.oracle.com/technetwork/topics/dotnet/whatsnew/index.html
2. http://docs.oracle.com/cd/B19306_01/win.102/b14307/featConnecting.htm#sthref114

When I work out how to include an attachment I'll update with a sample code file.

Updated 17/06/2013
Code Sample has dummy .dll's as I can't redistribute Oracles Dll's that you'll need to do for yourself.

Look out for my next blog with instructions for creating a NuGet package to host yourself for easy oracle project creation.