ORM 是用于迁移数据的正确工具吗? [英] Is a ORM the right tool to use for migrating data?

问题描述

背景

我们正在升级旧版导入工具，它的作用是将数据从连接到 SQL Server 的一个数据库移动到同一台服务器上的第二个数据库，并在此过程中使用不同的架构执行转换和映射.

We are in the process of upgrading a legacy import tool, what it does is it moves data from one database attached to SQL Server to a 2nd database on the same server with a different schema performing the translations and mapping along the way.

这是一个帮助解释发生了什么的例子

Here is a example to help explain what is happening

假设源数据库有一个名为 Client_Info 的表，目标表有两个名为 Clients 和 Cities

Say the source database has one table called Client_Info and the destination table has two tables named Clients and Cities

Source.dbo.Client_Info

+-----------+----------+----------+-------+
| FirstName | LastName |   City   | State |
+-----------+----------+----------+-------+
| John      | Smith    | Richmond | VA    |
| Jeff      | Walters  | New York | NY    |
+-----------+----------+----------+-------+

Dest.dbo.Clients

+-----------+-------------+--------------------------------------+
| FirstName |  LastName   |               CityGuid               |
+-----------+-------------+--------------------------------------+
| Scott     | Chamberlain | 07d954bf-3214-4df4-b640-48c27db2b1ed |
+-----------+-------------+--------------------------------------+

Dest.dbo.Cities

+--------------------------------------+----------+-------+
|               CityGuid               | CityName | State |
+--------------------------------------+----------+-------+
| 07d954bf-3214-4df4-b640-48c27db2b1ed | Richmond | VA    |
+--------------------------------------+----------+-------+

合并后我希望目的地看起来像这样

After the merge I would expect the destination to look like this

Dest.dbo.Clients

+-----------+-------------+--------------------------------------+
| FirstName |  LastName   |               CityGuid               |
+-----------+-------------+--------------------------------------+
| Scott     | Chamberlain | 07d954bf-3214-4df4-b640-48c27db2b1ed |
| John      | Smith       | 07d954bf-3214-4df4-b640-48c27db2b1ed |
| Jeff      | Walters     | 98a75f88-eeaa-49ba-b464-2ac988a7b093 |
+-----------+-------------+--------------------------------------+

Dest.dbo.Cities

+--------------------------------------+----------+-------+
|               CityGuid               | CityName | State |
+--------------------------------------+----------+-------+
| 07d954bf-3214-4df4-b640-48c27db2b1ed | Richmond | VA    |
| 98a75f88-eeaa-49ba-b464-2ac988a7b093 | New York | NY    |
+--------------------------------------+----------+-------+

目前它是一个 VB6 项目，我们只是使用硬编码的 SQL 语句，使用临时 #t 表来混洗记录，并根据需要使用现有值或新值填充 GUID 列.

Currently it is a VB6 project and we just use hard coded SQL statements shuffling records around using temporary #t tables and populate the GUID columns with the existing or new values as necessary.

我们已经成为 C# .NET 商店，并且现在被认为是将导入工具更新为 C# 的时候了，因为当我们的新版本的目标数据库发生变化时，对工具进行更改变得越来越困难.软件出来了(它只是为了安装 Visual Studio 6 并在 Windows 8 上工作).

We have since moved to being a C# .NET shop and it has been deemed time to update the import tool to C# due to it being harder and harder to make changes to the tool when the destination database changes when new versions of our software comes out (its a fight just to get Visual Studio 6 installed and working on windows 8).

我的问题

像 NHibernate 这样的 ORM 工具是否适合这项工作?我们之前没有人真正使用过 ORM(我们不是编写与新数据库对话以供日常使用的软件的开发团队，我们只是负责将旧数据库迁移到新数据库和真正的开发人员"会告诉我们他们对每个版本的架构进行了哪些更改).我不太确定使用 ORM，因为我认为 ORM 用于对客户端进行 CRUD 操作之类的事情，而不是像这样的服务器数据库迁移.

Is a ORM tool like NHibernate the right tool for the job? None of us have really used a ORM before (and we are not the development team that writes the software that talks to the new database for day to day use, we are just in charge of the migration of the old database to the new database and the "real developers" tells us what changes to the schema they make each version). I am not too sure about using a ORM as I think ORMs are used for things like CRUD operations to a client, not on server database migrations like this.

我认为正确"的方法是使用 SSIS，但我部门中没有人熟悉它我的主管觉得让每个人都学习另一种语言来维护它会花费太多时间和资源(这个迁移工具是在我在另一个问题中谈到的步骤之后运行的内容).

The thing I think that would be the "proper" way to do it is using SSIS, but no one in my department is familiar with it and my supervisor feels it would take too much time and resources to have everyone learn another language to maintain it (this migration tool is what is run after the step I was talking about in my other question).

我正在寻找的主要内容是一种进行此迁移的方法:

The main thing I am looking for is a way to do this migration with:

• 海量数据
• 允许在传输过程中设置某些列(例如重新使用外键 GUID)
• 易于随着目标数据库架构的变化而变化
• 最好使用 C# 或 SQL 完成.

对于这些要求，我应该寻找什么样的工具?

What kind of tools should I be looking for with those kinds of requirements?

我问这个问题是因为我认为 ORM 不是正确的做法，但我不确定我应该使用什么来代替.除了 SSIS(由于过于不同而从表中删除)之外，我不知道除了使用硬编码的 SQL 语句之外还能做什么，但这打破了我心中的易于更改"要求.

I ask this question as I don't think a ORM is the right thing to do, but I am not sure what I should use instead. Other than SSIS (that has been taken off the table for being too different) I don't know what to do other than just using hard coded SQL statements, but that breaks the "Easy to change" requirement in my mind.

推荐答案

我也不会使用完整的 ORM，但是像 Dapper 这样的微型 ORM 非常适合此类任务(以及其他任务).如果您熟悉 TSQL 和 C#，那么您可以非常快速地运行它，以实现高性能和易用性，它非常易于使用.(你可以在 15 分钟内提高效率)

I also wouldn't use a full blown ORM, but a micro ORM like Dapper is a great for tasks like this(among other things). Super fast and you run it all pretty close to the metal for high performance and ease of use if you are familiar with TSQL and c# it is a snap to use. ( you can be productive in 15 minutes)

刚刚完成了一个类似的项目，使用它在服务器之间移动数据，它的工作和表现就像冠军一样.

Just finished a similar project, using it to move data from server to server and it work and performed like a champ.

https://code.google.com/p/dapper-dot-net/

这篇关于ORM 是用于迁移数据的正确工具吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！