如何在 ServiceStack.OrmLite 中使用代码优先和数据迁移
4.00/5 (1投票)
ServiceStack.OrmLite 是一个开源、快速、简单、类型安全的 .NET ORM，其性能优于 EntityFramework 且更灵活。您可以在其 GitHub 站点找到更多详细信息。在本文中，我将向您展示如何使用 OrmLite 进行代码优先，并保留数据。
引言
ServiceStack.OrmLite 是一个开源、快速、简单、类型安全的 .NET ORM，其性能优于 EntityFramework 且更灵活。您可以在其 GitHub 站点找到更多详细信息。
在本文中，我将向您展示如何使用 OrmLite 进行代码优先，并保留数据。
背景
因此，我想尝试使用 OrmLite 代替 EntityFramework，但默认情况下，OrmLite 的代码优先仅在首次初始化项目时有效，因为它会删除所有数据并每次都重新创建表结构，这意味着它无法在项目运行时迁移数据，但不用担心，我将向您展示如何解决此问题 :)
实际上，我的逻辑非常简单，如下所示
- 将数据复制到临时表
- 重命名旧表
- 通过 OrmLite 创建一个新表
- 将数据从临时表复制到新表
- 删除旧表
Using the Code
1. 创建一个独立的 Model 项目，并从 Manage NuGet Packages 安装 ServiceStack.OrmLite & ServiceStack.OrmLite.SqlServer
我只是创建一个 User 模型用于测试，如下所示
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; }
}
}
对于模型 DataAnnotations，我建议使用 System.ComponentModel.DataAnnotations，因为这将比 OrmLite DataAnnotations 支持更多功能。
2. 创建一个控制台项目并引用 Model 项目，不要忘记安装 ServiceStack.OrmLite（不需要 ServiceStack.OrmLite.SqlServer）
3. 我们已经创建了另一个独立的控制台项目用于数据迁移，因此我们需要通过 app.config 文件传递以下信息
UpdateAll：是否更新所有表UpdateTables：哪些表需要更新，用逗号分隔，并且只有在 UpdateAll 为 false 时才会执行。（有时我们可能只想更新几个表）ModelNamespace：Model 项目的命名空间，我们可以通过它动态更新表和字段，所以为什么需要一个独立的 Model 项目 app.config 文件
<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. 我们需要动态获取模型来创建表和列，因此我们可以使用 Assembly.Load 动态加载模型（这就是为什么我们需要创建一个独立的模型项目）
//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. 通过 OrmLite 创建一个 dbFactory，并动态地创建表
//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. Migration 类是如何工作的？如您所知，使用 OrmLite 的 Create Table API，您需要将模型对象（类）传递给它，如下所示
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}
}
但在我的例子中，我们需要动态地执行此操作，并且我们之前已经通过 Assembly 获取了模型对象，因此此时我们需要动态调用该方法。 我们创建了一个 Migration 类和一个用于 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. 为数据迁移生成 SQL 脚本。为了处理不同的数据库，我们需要创建一个接口来处理 SQL 脚本生成
/// <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);
}
以及一个用于在创建和删除数据时处理外键的类
/// <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; }
}
对于演示，我只是使用 MSSQLServer 提供程序来实现此接口，如下所示
/// <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. 创建一个用于数据迁移的外部工具
在控制台项目完成后，您可以在 visual studio 中创建一个外部工具，当您使用它时，它将自动帮助您进行代码优先和迁移
源代码
您可以在下面找到完整的源代码：https://github.com/coderblog-winson/CoderBlog.OrmLite.Demo
您可以在我的 博客中找到更多信息 :)