Sunday, May 28, 2017

Accessing Oracle Databases in .NET

We recently went through hell trying to get an Oracle provider installed that works and is not a huge set of files that are impossible to install. We are primarily a SQL Server "shop" and hadn't had to use Oracle in a long, long time. So, this was quite an ordeal. I've been using SQL Server for 17 years (no expert, but I know my way around) ... but Oracle always mystifies me. It's just so different!

Anyway, I was not the one looking for a valid provider, it was my husband (we work together) ... and he finally found something that worked. And, luckily, he found it before he pulled out all his hair (and he *does* have a lot of it)!  You'll need to use this NuGet package (it's only a 2.5 MB download) and quite easy to install (as most NuGet packages are):

https://www.nuget.org/packages/Oracle.ManagedDataAccess/

If you went to the above link, you'll see that all you have to do is install the package from the NuGet Package Manager Console command line. The Package Manager Console has been built into Visual Studio (2012 and later). Find it under Tools | NuGet Package Manager | Package Manager Console. If, for some reason, you do not see the NuGet stuff under Tools (either because you have a VS earlier than 2012 or it just wasn't installed when you installed VS), then it can be installed manually as shown here:

https://docs.microsoft.com/en-us/nuget/guides/install-nuget#nuget-package-manager-in-visual-studio

I should also mention that when you add the NuGet Oracle.ManagedDataAccess package, it'll add a section to the config file that contains a sample connection string:

<oracle.manageddataaccess.client>
<version number="*">
<dataSources>
<dataSource alias="SampleDataSource" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL))) "/>
</dataSources>
</version>
</oracle.manageddataaccess.client>

We added the following <connectionStrings> setting in the config:

<connectionStrings>
<add name="Oracle" connectionString="DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX.XXX.XXX.XXX)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=MyDatabase)));User id=MyID;Password=MyPwd;"/>
</connectionStrings>

Obviously, change the XXX.XXX.XXX.XXX to your server's IP address, as well as changing MyDatabase, MyID and MyPwd to your own settings.

That should do the trick! Happy coding!  =0)