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.

Software Development without Source Control?

You'd think that every single company that does anything with software development would be using some form of source code control.

Not quite so. Just recently I spent a few days helping a customer that has 3 software developers and many different applications, both web and desktop apps. To my surprise they were not using any source control. I was quite shocked. At the end of each day, I had to upload my code to my web mail, just to be sure it doesn't get lost if my laptop crashes or gets stolen.

They explained that they almost never work on the same application with more than one developer and that their production environment always has the latest code deployed to it. Well, for web applications, because they are using ASP.NET Websites (as opposed to ASP.NET web applications) and they deploy all their source code into production environment, at least they have their source code relatively safe. But for  desktop applications, this is obviously not the case, because a desktop app must first be compiled.

There are of course many advantages to using source code control. To name just a few:

1. Source code (which is one of the main assets of any software project) is safely stored and hopefully regularly backed up, so that it does not get lost.
2. Every developer has access to the latest version of the source code, without having to copy files from one developer to another.
3. Source control systems maintain a history of changes to the source code, so that it's always possible to see who, when and how changed the code. And often, if developers are disciplined enough to comment before checking-in their code, one can even deduce why certain changes were maid.
4. Using source control, source code can be versioned and labeled, so that one can easily see what code is part of what version of the product.
5. There are more advanced source control features that can greatly improve the software development cycle, such as branching and check-in policies.

Delta-N, the company that I work for, specializes in Microsoft TFS (Team Foundation Server), which is an excellent source control system, but is also much much more. We've already made an appointment with this particular customer to see if we can help them set up source control!

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!

Solving web.config conflicts in child ASP.NET web apps in subdirectories

It often happens that we want to host a .NET web app as a (virtual) subdirectory under another existing web application. For example, we have a parent web app at http://mywebapp.com and a child web app at http://mywebapp.com/admin/

Because of the way that configuration settings are inherited in ASP.NET, it can happen that the parent app web.config settings conflict with the child app web.config, leading to configuration errors in the child application. To solve this prolbem, we need to disable web.config inheritance in the web.config file of the parent application. This is done by wrapping the whole <system.web> section into a <location> element with the inheritInChildApplications attribute set to "false", like this:

  <location inheritInChildApplications="false">

    <system.web>
       ...

    </system.web>   

  </location>

Happy coding!

How to combine URLs with relative paths in C#

Many of us are familiar with the wonderful class System.IO.Path. It makes life a lot easier when working with file system paths, for example:

string filePath = System.IO.Path.Combine(@"c:\docs", @"important\", "myfirst.doc");

This results in in this path: c:\docs\important\myfirst.doc
This is handy when you need to combine a (part of the) path coming from a config file or as a parameter and you don't know for sure whether people work with trailing or leading slashes or not. The static methods of the Path class take care of that for us!

Now, I've been wondering for some time if there is a way to do something similar with URLs and relative paths. The easiest way to do this that I found is to use the constructor of the System.Uri class:

string webPath = new Uri(new Uri("http://www.delta-n.nl/"), @"\public\default.aspx").ToString();

This results in http://www.delta-n.nl/public/default.aspx. To be on the safe side, you could even better use the System.Uri.TryCreate(...) static method.

There is also a class called System.Web.VirtualPathUtility. However it works only with the paths from the current web app context and cannot be used for the above scenario.

Happy coding!