With OrmLite, is there a way to automatically update table schema when my POCO is modified?

Question

Can OrmLite recognize differences between my POCO and my schema and automatically add (or remove) columns as necessary to force the schema to remain in sync with my POCO?

If this ability doesn't exist, is there way for me to query the db for table schema so that I may manually perform the syncing? I found this, but I'm using the version of OrmLite that installs with ServiceStack and for the life of me, I cannot find a namespace that has the TableInfo classes.

Answer 1

I created an extension method to automatically add missing columns to my tables. Been working great so far. Caveat: the code for getting the column names is SQL Server specific.

namespace System.Data
{
    public static class IDbConnectionExtensions
    {
        private static List<string> GetColumnNames(IDbConnection db, string tableName)
        {
            var columns = new List<string>();
            using (var cmd = db.CreateCommand())
            {
                cmd.CommandText = "exec sp_columns " + tableName;
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    var ordinal = reader.GetOrdinal("COLUMN_NAME");
                    columns.Add(reader.GetString(ordinal));
                }
                reader.Close();
            }
            return columns;
        }

        public static void AlterTable<T>(this IDbConnection db) where T : new()
        {
            var model = ModelDefinition<T>.Definition;

            // just create the table if it doesn't already exist
            if (db.TableExists(model.ModelName) == false)
            {
                db.CreateTable<T>(overwrite: false);
                return;
            }

            // find each of the missing fields
            var columns = GetColumnNames(db, model.ModelName);
            var missing = ModelDefinition<T>.Definition.FieldDefinitions
                .Where(field => columns.Contains(field.FieldName) == false)
                .ToList();

            // add a new column for each missing field
            foreach (var field in missing)
            {
                var alterSql = string.Format("ALTER TABLE {0} ADD {1} {2}", 
                    model.ModelName,
                    field.FieldName, 
                    db.GetDialectProvider().GetColumnTypeDefinition(field.FieldType)
                    );
                Console.WriteLine(alterSql);
                db.ExecuteSql(alterSql);
            }
        }
    }
}