Tuesday, March 16, 2010

.Net Annoyance: Define |DataDirectory| for Connection Strings

Update 20100326: For an acceptable workaround see at the bottom.

One thing  where I think common practice in .Net is behind Java is the idea of using an embedded database for integration tests.

Sql Server Express Edition has the nice feature to specify the DB-File in the connection string with AttachDbFilename.
This makes it easy to distribute your database with your project, which is a nice thing. The project template of ASP.Net MVC for instance uses this feature out of the box.

However the connection string usually contains this fragment:
|DataDirectory| is a placeholder, that in the case of the ASP.Net MVC template refers to the directory App_Data. This way it is possible to specify a relative path for your DB-File.

This seems a nice feature, but only as long as you are only distributing your solution containing the application.
The problems start, when you would like to open a connection to the same database from outside of the actual application.This is a common scenario for integration tests, which live in another project. AttachDbFilename seems not to accept relative paths that point to a location higher in the directory structure … this is a major annoyance!

You can define the value for |DataDictionary| like this:
AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\XYZ\App_Data\");
… but this does not solve the problem, since it still is not possible to use a relative path that point to a location higher in the directory structure.

The following results in a runtime exception:
AppDomain.CurrentDomain.SetData("DataDirectory", @"..\..\XYZ\App_Data");
Argh! Why make it so hard?

Does anybody have a solution for this problem?
Here the problem on Stackoverflow, and here an explanation on the Data Access blog.

Update 20100326: I settled for the following acceptable workaround:

In my App.config of my test-project I have the following section:
    <add key="DataDirectory" value="..\..\..\BookShop\App_Data\"/>

In the test-setup I then execute the following code:
var dataDirectory = ConfigurationManager.AppSettings["DataDirectory"];
var absoluteDataDirectory = Path.GetFullPath(dataDirectory);
AppDomain.CurrentDomain.SetData("DataDirectory", absoluteDataDirectory);


  1. Despite setting
    AppDomain.CurrentDomain.SetData("DataDirectory", @"C:\Program Files (x86)\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\");
    in code,
    the actual connection string still keeps het |DataDirectory| tag, hence doesn't find the directory.
    Can't set the directory in the app.config file either, cos it can't handle the spaces in the path.
    So, how can I access my database file in a path below "Program Files" (which is the default)?!!

  2. Thanks for the [Update 20100326]. I had a very similar requirement to get an integration test project up and running and this worked a treat


Related Posts Plugin for WordPress, Blogger...