Contents
ntroduction
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:
- Copy the data to a temp table
- Rename the old table
- Create a new table by OrmLite
- Copy the data from temp table to a new table
- 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 [su_label type=”warning”]Assembly.Load[/su_label](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 [su_label]Migration[/su_label] 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:
![](https://www.coderblog.in/wp-content/uploads/2019/03/20170717_01.png)
![](https://www.coderblog.in/wp-content/uploads/2019/03/20170717_02.png)
Demo
Below screen just show you how the result after run the code:
1. Create the user table and the UserName length is 20
![](https://www.coderblog.in/wp-content/uploads/2019/03/20170717_03.png)
2. I changed the UserName length to 50 and it also keep the data
![](https://www.coderblog.in/wp-content/uploads/2019/03/20170717_04.png)
Source Code
You can find the full source code in below :
CoderBlog.OrmLite.Demo_.zip (737 downloads )