Where’s Lou

Creating a SQLEXPRESS database file from code was a bit tricky to figure out. If you’re using it as an embedded database engine, like SQLite, one of the things I was looking for was the ability to “get code and run” a project locally. The internet was especially unhelpful because everything was coming back with descriptions of using Visual Studio or SQL Management Consoles, or with topics about how to handle errors that happen creating the aspnetdb.mdf file.

So to cut right to the chase, here’s how you do it:

public static void CreateSqlExpressDatabase(string filename)
{
    string databaseName = System.IO.Path.GetFileNameWithoutExtension(filename);
    using (var connection = new SqlConnection(
        "Data Source=.\\sqlexpress;Initial Catalog=tempdb;"+
        "Integrated Security=true;User Instance=True;"))
    {
        connection.Open();
        using (var command = connection.CreateCommand())
        {
            command.CommandText =
                "CREATE DATABASE " + databaseName +
                " ON PRIMARY (NAME=" + databaseName +
                ", FILENAME='" + filename + "')";
            command.ExecuteNonQuery();

            command.CommandText =
                "EXEC sp_detach_db '" + databaseName + "', 'true'";
            command.ExecuteNonQuery();
        }
    }
}

Pretty simple in the end. Creates a connection to sqlexpress for the purpose of creating the database in a specific location, then removes it from the sql server instance’s knowledge. The use of tempdb in the connection string is important when this code is run from a context that has very low permissions. While running the app in the webdev.webserver (I try not to develop as a local admin) a connection to the default catalog ‘master’ was denied.

In theory the databaseName doesn’t matter, but it does appear several times in the mdf file.

Now if you’re using an embedded database with a connection string like Data Source=.\SQLEXPRESS;AttachDbFileName=|DataDirectory|\MyData.mdf;Integrated Security=True;User Instance=True; you can create it when your global application start with something like this:

    var appData = (string)AppDomain.CurrentDomain.GetData("DataDirectory");
    var filename = System.IO.Path.Combine(appData, "MyData.mdf");
    if (!System.IO.File.Exists(filename))
        CreateSqlExpressDatabase(filename);

Followed by a call to ActiveRecordStarter.UpdateSchema(); of course!