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

0
15

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
{
 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>
 <!--
 <add key="UpdateAll" value="true" />
 <!--
 <add key="UpdateTables" value="Page" />
 <!--
 <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)


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"];


var asm = Assembly.Load(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


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))
 {
 
 
 Migration m = new Migration(); 
 
 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(); 
}

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 :

public void UpdateTable<T>(IDbConnection connection, ISqlProvider sqlProvider) where T : new()
{
 try
 {
 connection.CreateTableIfNotExists<T>();

 var model = ModelDefinition<T>.Definition;
 string tableName = model.Name; 
 string tableNameTmp = tableName + "Tmp"; 

 
 string getDbColumnsSql = sqlProvider.GetColumnNamesSql(tableName);
 var dbColumns = connection.SqlList<string>(getDbColumnsSql);

 
 var fkStatement = sqlProvider.MigrateTableSql(connection, tableName, tableNameTmp);
 connection.ExecuteNonQuery(fkStatement.DropStatement);

 
 connection.CreateTable<T>();

 
 if (!string.IsNullOrEmpty(fkStatement.CreateStatement))
 {
 connection.ExecuteNonQuery(fkStatement.CreateStatement);
 }

 
 string getModelColumnsSql = sqlProvider.GetColumnNamesSql(tableName);
 var modelColumns = connection.SqlList<string>(getModelColumnsSql);

 
 List<string> activeFields = dbColumns.Where(dbColumn => modelColumns.Contains(dbColumn)).ToList();

 
 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:

public interface ISqlProvider
{
 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

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:

public class MSSqlProvider : ISqlProvider
{
 public FKStatement MigrateTableSql(IDbConnection connection, string currentName, string newName)
 {
 var fkStatement = new FKStatement();
 
 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;
 }

 public string GetColumnNamesSql(string tableName)
 {
 return "SELECT name FROM syscolumns WHERE id = OBJECT_ID('" + tableName + "');";
 }

 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

LEAVE A REPLY