ReBuildAll Blog
Thoughts (mostly) on .NET development

You might be familiar with SQL Server Express User Instances. These are per user copies of the database server, into which you can attach files "on the fly", without a real need for a fully managed SQL environment. You probably bumped into it if you have used the default setup for MVC or ASP.NET where you tried to use membership or other services. ASP.NET created a .MDF and .LDF file for you in the App_Data directory automatically. These are actually SQL Server Express User Instance databases.

If you ever checked the ConnectionString, it will contain a |DataDirectory| directive (and the term User intance=true. At runtime, the ASP.NET runtime resolves this to point into the App_Data folder of the application. The SQL client runtime of .NET is able to connect up this file into the user instance and use it, without any user action needed. It can also create it, if it does not exist.

While ASP.NET provides this behavior for its own databases by default, there is nothing stopping us from using the same feature with our custom application databases.


Create a missing database

When I put code into source code control, I rarely put it actual .MDF and .LDF files (databases). Instead, usually an .SQL script goes in there that can create my database. However, since LINQ has the neat feature to create the missing database, why not use that? This way I could make my project create the database it needs by itself, when starting up.

It would be ideal for distributing demos and examples. The person who opens it up, just needs to start it. Visual Studio self hosts the web application in its own web server, the database is created in the SQL Server Express User Instance, and it just starts working.

It is all very simple in theory, until you want to call LINQ's CreateDatabase() on a LINQ model that has a ConnectionString with the |DataDirectory| variable. I used CreateDatabase() succesfully before with regular SQL server databases. But when I tried the user instance approached, it failed.

My first try was the following code:

MyDataContext ctx = new MyDataContext( connString )
if (ctx.DatabaseExists() == false)
{
    ctx.CreateDatabase();
}

And what I got was an ArgumentException, that told me: Illegal characters in path.. Strange. I checked the callstack, which was:

   at System.IO.Path.CheckInvalidPathChars(String path)
   at System.IO.Path.NormalizePath(String path, Boolean fullCheck, Int32 maxPathLength)
   at System.IO.Path.GetFullPathInternal(String path)
   at System.IO.Path.GetFullPath(String path)
   at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.CreateDatabase()
   at System.Data.Linq.DataContext.CreateDatabase()

I checked everything, but all seemed to be fine.

I then tried substituting the actual path in the connection string, and my code worked. So what is going on?


Bug in LINQ DataContext

LINQ DataContext uses its own SQL wrapper, and that wrapper has a bug. It cannot resolve the |DataDirectory| constant. I used to .NET Reflector to verify this. Sure enough, the System.Data.Linq.SqlClient.SqlProvider class retrieves the AttachDbFilename entry from the connection string, and then uses it directly. So if you have |DataDirectory| in there, you are out of luck: an exception will inform you of this

Luckily, you can retrieve the value of the DataDirectory entry from the current AppDomain. So you could do the substitution yourself, pass the corrected connection string to the DataContext and create the database. Voila!


DataDirectory

So first, we need the DataDirectory entry. We can use the following code to retrieve it from the current AppDomain:

string dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();

Putting it together

So I ended up writing the following method into my Global.asax.cs file:

        private void EnsureDatabase()
        {
            string connString = null;
            connString = global::System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

            if (connString.Contains("|DataDirectory|"))
            {
                string dataDirectory = AppDomain.CurrentDomain.GetData("DataDirectory").ToString();
                connString = connString.Replace("|DataDirectory|", dataDirectory);
            } 

            using ( MyDataContext ctx = new MyDataContext( connString ) )
            {
                if (ctx.DatabaseExists() == false)
                {
                    ctx.CreateDatabase();
                }
            }
        }

This code loads my connection string and if it finds the |DataDirectory| directive in there, it will replace it with the DataDirectory value from the AppDomain. It then fires up an instance of the DataContext and makes sure the database exists. Sure enough, this works (except ... see below).

This code can be called in Global.asax.cs in the Application_Start() method. After this, whenever you run the application and it finds the database is missing, it will create it. You would still need to populate it with initial data, if that is a requirement, because the database will be empty of course.


Multiple user instance databases

While testing the above problem, I replace the DataDirectory variable by hand first, tried it that way, and it worked. I then stopped the web server, deleted the created files and started playing around. I bumped into a problem when I then wanted to create the files again. As if SQL server believed the files existed, although I deleted them. As it turns out, User Instances are not as simple as they sound. There is a slight problem when you want to attach a database with the same path into the user instance that already existed. I wrote a separate blog post about that. See it here.