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.

Use Linked Reports in SQL Server Reporting Services!

A couple of weeks ago I was asked to develop a number of reports in SQL Server Reporting Services 2005. Most of these reports display information from different websites that the client develops and hosts for their own clients, something like 25 different websites.

My client's original idea was to create a "template" report that can then be copied for each website separately.​ The problem with this approach is of course that if something needs to be changed, the change will have to be copied into all 25 reports.

My initial idea was to use a join in my sql queries to the Active Directory, so that based on the current user I could figure out which AD user groups the user belongs to and then, based on a naming convention, derive from the group names which websites the user should have access to. Well, that's sort of complicated. I couldn't even get a linked server to the Active Directory working, probably because of some permissions problems.

And then I decided to use my favourite approach: I found this pretty good book on the subject: http://www.amazon.com/Microsoft-Server-2005-Reporting-Services/dp/0072262397/, skimmed through its contents and found an elegant solution in one of the last chapters.

The idea is

1. to have website as a parameter in your report;
2. put the report in a hidden folder on the Report Server available only to users authorized to view the information across websites; we'll call it the base report;
3. create a folder for each website and set permissions on each folder;
4. from the base report create a linked report for each website in the corresponding website folder
5. in the linked report, go to the parameters configuration page, set the default value of our website parameter to the desired value and choose not to prompt the user for the parameter value.

To sum up, we now have a base report available to the cross-site admins and a website-specific linked report available to the website admins. If the report needs to be changed, in most cases we'll only need to update the base report!

Another useful best practice that I learned from the book is to create a template report containing the basic layout: the header, the footer, the logo, page numbering, etc. Then we can place this template .rdl report into the visual studio folder where Report Designer stores its templates (for VS2005, it can be C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject) . After that, when we add a new report to our Visual Studio report project, the template will appear in the list of installed templates for us to choose from. When we create a new report based on the template, we'll get all the formatting and features from the template and can then proceed to modify the report as desired:

Happy coding!