Patterns and Practices

During some recent development the team I was experiencing some friction with databases and developing. We were using Castle Active Record and we soon realized that as new people joined the team, left the team (rinse, wash, repeat), db changes occurred in the Active Record Schema etc, that the set up time to going was miserable.

The problem at the time is that you’d have to set up the DB in SQL Express (which is what we used during development), make sure the schema was correct, make sure you had the most recent baseline,change scripts, etc. We were not following a db change management workflow as in regards to version control as we were using Active Record (all schema is based off of the objects) and we were developing a new product from the ground up. Therefore once we were done we could auto-gen the scripts and go from there. At that point we could implement a version control process for our db.

To make a long story short … we wanted to be able to check in active record changes, and make updating the database as easy as visiting a route on the ASP.NET MVC Application. This route would update the database schema and update the tables, etc. The issue is that if the database was never created in the App_Data directory (we did not check in the .mdf into version control) then we would have a problem. We needed to create one on the fly.

How

Here’s how to create a database on the fly with a few simple SQL commands. Here is an example:

        public void CreateDatabase(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();
                }
            }
        }

What we’re doing here is creating a database via  file name and then disconnecting the database from the server. This leaves us with an mdf file sitting on the disk somewhere.

The ‘filename’ attribute specifies where the file (example.mdf) should go. This is obtained by executing the following code:

string dataDirectory = Convert.ToString(AppDomain.CurrentDomain.GetData("DataDirectory"));
string mdfPath = Path.Combine(dataDirectory, "admin.mdf");

After we create the database we need to create the schema’s. Here’s the call to do this:

public void CreateSchema(string path, string schema)
        {
            var connectionString =
                String.Format(
                    @"Data Source=.\SQLEXPRESS;AttachDbFileName={0};Integrated Security=True;User Instance=True;", path);
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = String.Format("CREATE SCHEMA [{0}]", schema);
                    command.ExecuteNonQuery();
                }
            }
        }

With the call above we have attached to the db using SQLExpress and created a schema through the CREATE SCHEMA call. We would call this method for each schema we had like this:

// Get your mdf path as shown above
localDbManager.CreateSchema(mdfPath, "Customers");
localDbManager.CreateSchema(mdfPath, "Shipping");
localDbManager.CreateSchema(mdfPath, "Logistics");
// ... etc, etc, etc ...

Remember, this may look expensive in regards to setting up a db, but this is only called when the user requests it, OR, if set up to do so, such as at the time of initialization of the application (which is where I put this code – this allows the dev who is opening the app for the first time to get the latest and greatest in regards to the db set up when he/she starts the app).

Finally, we call:

ActiveRecordStarter.UpdateSchema();

This tells ActiveRecord To generate the scripts and run them against the current db.

At this point you would have a db up and running within your App_Data directory.

I have purposely left out a lot of details of how the app sets itself up, so I have  included a sample app you can download and run. All of the assemblies are included. Download, open the sln, and run. As long as you have access to write a file to the file system, this will work.

Download Sample

The sample is a ASP.NET MVC application with the Spark View Engine. Other technologies used in the sample: Windsor Container, Castle Active Record

Important Notes: The Windsor.config file contains the ActiveRecord connection information. We use Windsor Container to inject and resolve instances in the controllers and other areas of the app. The app will attempt to create and update the db upon application start (global.asax) and you can force a schema update through the /admin/updateschema/ route.

Download Zipped Solution (5.3 MB)

Running the Sample

Running the sample is fairly straightforward. When the app starts you will be presented with this screen:

If you look in your App_Data directory you will see your admin.mdf file inside of the directory as such:

If you were to attach this to SQL Express Management Studio and looked at the DB, you would see the tables, etc.

To load data into the db for the sample, I have created a simple route that creates a random customer and loads it into the database. Click the link “Create Random Customer” and a random customer will be created. You can then click on the customer to view details about the customer or you can delete the customer by clicking on the “x”. Its a very rudimentary example, but it provides all of the end to end goop for you to grok this example.

Conclusion

By no means is this a replacement for your production DB. You’d never want to auto-gen your DB (unless it always started out empty, etc). This is the perfect scenario for an agile project where changes are made frequently and keeping db changes up to date across a developer team is a difficult task.

What about pre-loaded data for look up tables, etc?

This is a fairly simple problem to fix. I have created a property that is injected via setter injection into the Application.cs file. The property is “ShouldInitializeData”. If true (set this in the /config/WIndsor.config) the application will initialize some data with the DataInitializationService. Inside of this service you could call a handful of other services to initialize data, or you could perform the initialization inside of this service itself. Its up to you at this point. I have included this as part of the sample.

Enjoy.