Oracle for a .NET Developer

Even though I've been developing data driven applications for some 8 years or so, you'd be surprised to learn that I had never actually developed against an Oracle database. I've worked mostly with SQL Server, but also occasionally with MS Access and MySQL. Yet Oracle had always been terra incognita for me, until early this month I got two different assignments at two different customers, and both of them involved an ASP.NET web application with an Oracle back end!

Not that I was very happy about that, but I decided to approach it in this spirit of "I can do it" and "live and learn". Well, it took me some extra time to figure out how to work with an Oracle client, how to set up an Oracle Express database on my laptop, how to work with Oracle development tools such as Oracle SQL Developer and Toad for Oracle. But in the end I got it working just fine and learned quite a bit in the process.

So if you are a .NET developer and have to program against an Oracle database, this is how you can set up your development environment:

1. If you like working with .NET Entity Framework and LINQ for your data access (and I think you should), do install the Oracle Data Access Components for Microsoft Entity Framework. At the moment it is in beta, but it works just fine! This install includes the Oracle Instant Client, Oracle Data Provider for .NET 4 and Oracle Developer Tools for Visual Studio.
2. If you don't want to work with Entity Framework, just install the latest version of the Oracle client.
3. Install the free Oracle Express database for your local development.
4. Install the free Oracle SQL Developer tool, which can be compared to SQL Server Management Studio Express. You may also wish to try the licensed Toad for Oracle, which to my taste has just too many features.
5. In Visual Studio, open the Server Explorer and add a data connection to your Oracle Database by using Oracle Data Provider for .NET. Then generate an entity model based on this connection.
6. When setting up a connection string to an Oracle database in a test or production environment, I recommend using a full connection string without a reference to tnsnames.ora, which looks something like this:
   Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=MyOracleSID)));User Id=myUsername;Password=myPassword;
   Even though this may seem a bit cumbersome, I found that it's better to use such a connection string instead of relying on the tnsnames.ora file, especially if there are different versions of an Oracle client installed, each having its own tnsnames.ora file.

Because of time pressure, in this particular case I had to abandon my favorite approach to new things, which is reading a good book on the subject, in favor of the "trial & error" approach. But I hope that one day I'll read a good (and small!) book about Oracle in order to better understand concepts behind it.