software.herbrandson.com

In creating OpenForum, one of the things I felt was really important was that it be as easy as possible for users to get up and running. The more steps involved in getting it setup, the fewer people who would actually try it out. I know I’ve had more then a few experiences of downloading a development tool, getting 7 steps into the install/setup process, and then deciding to look into some of the tools competitors. I want tools that are simple and powerful. If it’s a pain to just get them running, what is it going to be like to customize them? Another thing I really hate is having to copy copious numbers of files into specific directories. I’ve used at least a few WYSIWYG editors that had 10x more files then the actual application I was building! So, when it came to a database for OpenForum, I wanted to avoid requiring users to do any connection string configuration, script running, or file copying. One utility that I’ve seen which I feel does a great job of solving that problem is the ASP.NET Membership Provider, especially the way that it’s used in the default MVC template. If you’ve never tried it out, it’s pretty slick. Here are the steps to get it installed/configured.

1. Do nothing

Not bad, eh? All you have to do is run the project and go to any of the pages that use membership (i.e. try logging in). As soon as you do anything that requires the use of the Membership Provider module, a database is automagically generated for in the App_Data directory. That’s what I call easy. That was exactly the experience I wanted for OpenForum. Run the application and a database is created for you.

However, it turns out that it’s not as straight forward as I was hoping. The key piece behind making everything work is a feature in SqlExpress called User Instances. User instance databases are created on a per user basis and can be attached to the SqlExpress engine at runtime. Here’s what a connection string looks like

Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\Database1.mdf; Integrated Security=True; User Instance=True

Notice the “AttachDbFilename” property. That’s what tells Sql where the database file is on disk. The “|DataDirectory|” bit will be expanded to the path to the App_Data directory for the current project.

I’m using LinqToSql, so I did a quick experiment using CreateDatabase , but quickly ran into a problem. It turns out there is a bug somewhere in the bowels of LinqToSql that causes the AttachDbFilename stuff to fail if the path to the database file (the .mdf file) is more then 128 characters. So, I broke out the second best development tool there is, and decompiled the ASP.NET Membership Provider code to figure out how the guys at Microsoft were doing it. It turns out the solution is to first create the database in a temp directory and then copy it to where you need it to go. Not too bad really as far as workarounds go. With a few minutes more of fooling around, I had the following code that seems to work really well.

using (OpenForumDataContext context =
          new OpenForumDataContext(DEFAULT_CONNECTION_STRING))
{
    string finalDirectory = (string)AppDomain.CurrentDomain
                                      .GetData("DataDirectory");

    if (!Directory.Exists(finalDirectory))
    {
        Directory.CreateDirectory(finalDirectory);
    }

    string finalPath = Path.Combine(finalDirectory, "OpenForum");

    if (!File.Exists(finalPath + ".mdf"))
    {
        string tempPath = Path.Combine(Path.GetTempPath(),
                         Guid.NewGuid().ToString());

        using (OpenForumDataContext tempContext =
                    new OpenForumDataContext(tempPath + ".mdf"))
        {
            tempContext.CreateDatabase();
            tempContext.ExecuteCommand(Resources.DefaultData);
            tempContext.ExecuteCommand(@"Declare @name as varchar(100);
                          set @name = DB_NAME();
                          Use master;
                          exec sp_detach_db @name, 'true';");
        }

        File.Move(tempPath + ".mdf", finalPath + ".mdf");
        File.Delete(tempPath + ".ldf");
    }
}