How to automatically detect database model changes in ServiceStack.OrmLite, and have the database update automatically.

CheckDatabaseModel.cs

public void CheckDatabaseModel(Type modelType)
{
    // Get the referenced model version
    string modelVersion = Assembly.GetAssembly(modelType).GetName().Version.ToString();

    // Determine the last model version number from the configuration
    var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    var lastModelVersion = config.AppSettings.Settings["DatabaseModelVersion"];

    // Determine if the model has changed
    if(lastModelVersion == null || lastModelVersion.Value != modelVersion)
    {
        Console.WriteLine("Model has changed");
        using(var db = Resolve<IDbConnectionFactory>().OpenDbConnection())
        {
            // Drop and recreate the tables

            // Determine the tables from the model assembly
            var tables = modelType.GetNestedTypes();

            var orderTables = new Dictionary<int, Type>();
            var unorderedTables = new List<Type>(); // Tables without the attribute will be created last, but in no specific order
            foreach(var table in tables)
            {
                var order = Attribute.GetCustomAttribute(table, typeof(TableCreationOrderAttribute)) as TableCreationOrderAttribute;
                if(order != null)
                    orderTables.Add(order.Order, table);
                else
                    unorderedTables.Add(table);
            }

            foreach(var table in orderTables.OrderBy(t=>t.Key))
                db.DropAndCreateTable(table.Value);

            foreach(var table in unorderedTables)
                db.DropAndCreateTable(table);

            // Repopulate database with initial data.

            // Save the model version to settings
            if(lastModelVersion == null)
                config.AppSettings.Settings.Add("DatabaseModelVersion", modelVersion);
            else 
                config.AppSettings.Settings["DatabaseModelVersion"].Value = modelVersion;     
            config.Save(ConfigurationSaveMode.Modified);
        }
    } else {
        // The model numbers matched
        Console.WriteLine("Model is current");
    }
}

TableCreationOrderAttribute.cs

public class TableCreationOrderAttribute : Attribute
{
    public int Order { get; private set; }

    public TableCreationOrderAttribute(int order)
    {
        Order = order;
    }
}

Usage.md

#Usage:

1. Create a separate Model library with auto incrementing build numbers. By setting the assembly version to a wildcard:

    [assembly: AssemblyVersion("1.0.*")]
   

2. Ensure your model tables are contained in a suitably named public static class i.e.

    public static class Model
    {
        public class Article : IHasId<int>
        {
   

3. Add the TableCreationOrderAttribute to the model project, outside the static class - So it's not considered part of it!

4. Decorate your model with the TableCreationOrder i.e.

    [TableCreationOrder(3)]
    public class Article : IHasId<int>
    {
        ...
   

5. Add the CheckDatabaseModel method to your project. (If you have added it to a utility library just reference it!)

6. Add a reference to the Model assembly in your project. And call the check on the type of the model assembly:

    CheckDatabaseModel(typeof(Model));
   

7. Ensure your application has an app.config or web.config as appropriate. It can be empty, the code will auto-initialise it.

8. Relax, your project will now pick up changes to your model automatically when it runs, and your database will be updated!

---

CheckDatabaseModel required to reference:

• using System.Reflection;
• using System.Configuration;