How to use code first and data migration with ServiceStack.OrmLite

Introduction

ServiceStack.OrmLite is an Open Source, Fast, Simple, Typed ORM for .NET, the performance is better than EntityFramework and more flexible. You can find more detail in their GitHub site.

And in this article, I will show you how to do the code first with OrmLite and keep the data.

Background

So, I want to try to instead of EntityFramework with OrmLite, but by default, OrmLite's code first just can work in first time for initialize the project, because it will delete all of the data and re-create the table structure every time, that's mean it can't migrate the data when the project is running, but don't worry, I will show you how can solve this problem :)

Actually, my logic is very simple as below:

1. Copy the data to a temp table
2. Rename the old table
3. Create a new table by OrmLite
4. Copy the data from temp table to a new table
5. Delete the old table

Using the Code

1. Create an independent Model project and install ServiceStack.OrmLite & ServiceStack.OrmLite.SqlServer from Manage NuGet Packages

I just create a User model for testing as below:

using System.ComponentModel.DataAnnotations;
using ServiceStack.Model;

namespace CoderBlog.Model
{
    /// <summary>
    /// User model, and it's need to inherit IHasId<int> so that we can use the OrmLite code first 
    /// </summary>
    public class User : IHasId<int>
    {
        [ServiceStack.DataAnnotations.AutoIncrement]
        public int Id { get; set; }

        [Required]
        [StringLength(10)]
        public string UserName { get; set; }

        [Required]
        [StringLength(20)]
        public string Password { get; set; }

        [StringLength(30)]
        public string Email { get; set; }
               
        [StringLength(20)]
        public string FirstName { get; set; }

        [StringLength(20)]
        public string LastName { get; set; }
    }
}

For the model DataAnnotations, I suggest use the System.ComponentModel.DataAnnotations, because this will be support more feature then OrmLite DataAnnotations.

2. Create a console project and refer the Model project, don't forget to install ServiceStack.OrmLite (don't need ServiceStack.OrmLite.SqlServer)

3. We have created another independent console project for data migration, so we need to pass below information by app.config file:

1. UpdateAll: whether update all tables
2. UpdateTables: Which's tables need to be update, split with comma and just will do when UpdateAll is false. (Sometime we may just want to udpate several tables)
3. ModelNamespace: The Model project's namespace, we can dynamic update the table and fields by this, so why need an independent Model project app.config file:

<appSettings>
    <!--Update all tables-->
    <add key="UpdateAll" value="true" />
    <!--Which's tables need to be update, split with comma and just will do when UpdateAll is false-->
    <add key="UpdateTables" value="Page" />
    <!--Code first Model for generate the tables in database-->
    <add key="ModelNamespace" value="CoderBlog.Model" />
</appSettings>

4. We need to dynamic get the models for create table and columns, so we can use dynamic load the model by Assembly.Load (that's why we need to create an independent model project)

//get the connection string and other settings from app.config
var connection = ConfigurationManager.ConnectionStrings["Default"].ConnectionString;

var isUpdateAll = Convert.ToBoolean(ConfigurationManager.AppSettings["UpdateAll"]);

var updateTables = ConfigurationManager.AppSettings["UpdateTables"].Split(',').ToList();

var nameSpace = ConfigurationManager.AppSettings["ModelNamespace"];

//load the assembly for dynamic to load model
var asm = Assembly.Load(nameSpace);

//dynamic get the models by namespace
var models = asm.GetTypes().Where(p =>
     p.Namespace == nameSpace
).ToList();

List<object> objects = new List<object>();
foreach (var model in models)
{
    objects.Add(Activator.CreateInstance(model));
}

5. Create a dbFactory by OrmLite and dynamic to create the tables

//create the db factory with OrmLite
var dbFactory = new OrmLiteConnectionFactory(connection, SqlServerDialect.Provider);

using (var db = dbFactory.OpenDbConnection())
{
    using (IDbTransaction trans = db.OpenTransaction(IsolationLevel.ReadCommitted))
    {
        foreach (var o in objects)
        {
            var model = o.GetType();

            if (isUpdateAll || (updateTables.Where(t => t == model.Name).Any() && !isUpdateAll))
            {
                //dynamic to call the UpdateTable method so that can support all models
                //I will show you later how it works
                Migration m = new Migration();     
                //dynamic to call the method "UpdateTable"            
                MethodInfo method = typeof(Migration).GetMethod("UpdateTable");
                MethodInfo generic = method.MakeGenericMethod(model);
                generic.Invoke(m, new object[] { db, new MSSqlProvider() });
            }
        }
        trans.Commit();
    }
}

6. How's the Migration class works As you know, with OrmLite's Create Table API, you need to pass the model object (class) to it as below:

using (var db = dbFactory.Open())
{
    if (db.CreateTableIfNotExists<Poco>())
    {
        db.Insert(new Poco { Id = 1, Name = "Seed Data"});
    }

    var result = db.SingleById<Poco>(1);
    result.PrintDump(); //= {Id: 1, Name:Seed Data}
}

But in my case, we need to dynamic to do that, and we have get the model objects by Assembly before , so in this time we need to dynamic call the method. We created a Migration class and a method for UpdateTable:

/// <summary>
/// Update table structure by model
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="connection"></param>
/// <param name="sqlProvider"></param>
public void UpdateTable<T>(IDbConnection connection, ISqlProvider sqlProvider) where T : new()
{
    try
    {
        connection.CreateTableIfNotExists<T>();

        var model = ModelDefinition<T>.Definition;
        string tableName = model.Name;  //the original table
        string tableNameTmp = tableName + "Tmp"; //temp table for save the data

        //get the existing table's columns
        string getDbColumnsSql = sqlProvider.GetColumnNamesSql(tableName);
        var dbColumns = connection.SqlList<string>(getDbColumnsSql);

        //insert the data to a temp table first
        var fkStatement = sqlProvider.MigrateTableSql(connection, tableName, tableNameTmp);
        connection.ExecuteNonQuery(fkStatement.DropStatement);

        //create a new table
        connection.CreateTable<T>();

        //handle the foreign keys
        if (!string.IsNullOrEmpty(fkStatement.CreateStatement))
        {
            connection.ExecuteNonQuery(fkStatement.CreateStatement);
        }

        //get the new table's columns
        string getModelColumnsSql = sqlProvider.GetColumnNamesSql(tableName);
        var modelColumns = connection.SqlList<string>(getModelColumnsSql);

        //dynamic get columns from model
        List<string> activeFields = dbColumns.Where(dbColumn => modelColumns.Contains(dbColumn)).ToList();

        //move the data from temp table to new table, so that we can keep the original data after migration
        string activeFieldsCommaSep = string.Join("," , activeFields);
        string insertIntoSql = sqlProvider.InsertIntoSql(tableName, "#temp", activeFieldsCommaSep);

        connection.ExecuteSql(insertIntoSql);
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

7. Generate SQL script for data migration For handle difference database, we need to create an interface for handle SQL script generation:

/// <summary>
/// Interface for Sql provider, you can implement it for your custom provider
/// </summary>
public interface ISqlProvider
{
    /// <summary>
    /// Generate drop FK and create FK sql and temp table for migrate the table data
    /// </summary>
    /// <param name="connection"></param>
    /// <param name="currentName"></param>
    /// <param name="newName"></param>
    /// <returns></returns>
    FKStatement MigrateTableSql(IDbConnection connection, string currentName, string newName);

    string GetColumnNamesSql(string tableName);

    string InsertIntoSql(string intoTableName, string fromTableName, string commaSeparatedColumns);
}

And a class for handle foreign keys when create and drop data

/// <summary>
/// For generate SQL string for drop and re-recreate foreign keys 
/// </summary>
public class FKStatement
{
    public string ParentObject { get; set; }
    public string ReferenceObject { get; set; }
    public string DropStatement { get; set; }
    public string CreateStatement { get; set; }
}

For demo, I just implement this interface with MSSQLServer provider as below:

/// <summary>
/// MSSQL provider
/// </summary>
public class MSSqlProvider : ISqlProvider
{
    /// <summary>
    /// Generate migration SQL, base on individual Database, so we need to handle this by difference provider
    /// </summary>
    /// <param name="connection"></param>
    /// <param name="currentName"></param>
    /// <param name="newName"></param>
    /// <returns></returns>
    public FKStatement MigrateTableSql(IDbConnection connection, string currentName, string newName)
    {
        var fkStatement = new FKStatement();
        //get the drop and re-create foreign keys sqls
        var sql_get_foreign_keys = @"SELECT OBJECT_NAME(fk.parent_object_id) ParentObject, 
                OBJECT_NAME(fk.referenced_object_id) ReferencedObject,
                'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
                    + ' DROP CONSTRAINT ' + fk.NAME + ' ;' AS DropStatement,
                'ALTER TABLE ' + s.name + '.' + OBJECT_NAME(fk.parent_object_id)
                + ' ADD CONSTRAINT ' + fk.NAME + ' FOREIGN KEY (' + COL_NAME(fk.parent_object_id, fkc.parent_column_id)
                    + ') REFERENCES ' + ss.name + '.' + OBJECT_NAME(fk.referenced_object_id)
                    + '(' + COL_NAME(fk.referenced_object_id, fkc.referenced_column_id) + ');' AS CreateStatement
            FROM
                sys.foreign_keys fk
            INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
            INNER JOIN sys.schemas s ON fk.schema_id = s.schema_id
            INNER JOIN sys.tables t ON fkc.referenced_object_id = t.object_id
            INNER JOIN sys.schemas ss ON t.schema_id = ss.schema_id
            WHERE
                OBJECT_NAME(fk.referenced_object_id) = '" + currentName + "' AND ss.name = 'dbo';";

        var fkSql = connection.SqlList<FKStatement>(sql_get_foreign_keys);
        if (fkSql.Count > 0)
        {
            foreach (var fk in fkSql)
            {
                fkStatement.DropStatement += fk.DropStatement;
                if (fk.ParentObject != currentName)
                {
                    fkStatement.CreateStatement += fk.CreateStatement;
                }
            }
        }

        fkStatement.DropStatement += " select * into #temp from (select * from [" + currentName + "]) as tmp; drop table [" + currentName + "]; ";
        return fkStatement;
    }

    /// <summary>
    /// Get the table's columns
    /// </summary>
    /// <param name="tableName"></param>
    /// <returns></returns>
    public string GetColumnNamesSql(string tableName)
    {
        return "SELECT name FROM syscolumns  WHERE id = OBJECT_ID('" + tableName + "');";
    }

    /// <summary>
    /// Insert data to new table, for MSSQL server 2008 above, I will disable all CONSTRAINT before insert data and enable them after done.
    /// </summary>
    /// <param name="intoTableName"></param>
    /// <param name="fromTableName"></param>
    /// <param name="commaSeparatedColumns"></param>
    /// <returns></returns>
    public string InsertIntoSql(string intoTableName, string fromTableName, string commaSeparatedColumns)
    {
        return "EXEC sp_msforeachtable \"ALTER TABLE ? NOCHECK CONSTRAINT all\"; SET IDENTITY_INSERT [" + intoTableName + "] ON; INSERT INTO [" + intoTableName + "] (" +
            commaSeparatedColumns + ") SELECT " + commaSeparatedColumns + " FROM [" + fromTableName + "]; SET IDENTITY_INSERT [" + intoTableName + "] OFF;  drop table [" + fromTableName + "];EXEC sp_msforeachtable \"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all\"";
    }
}

8. Create an External Tool for data migration

After the console project done, you can create an external tool in visual studio, when you use it, it will auto help to do the code first and migration:

Source Code

You can find the full source code in below : https://github.com/coderblog-winson/CoderBlog.OrmLite.Demo

And you can find more in my blog :)