使用C#实现TSQL数据库间数据迁移并自动创建目标表

一、自动创建目标库Person表（保证结构完全匹配） #

基于已有的Person实体类和DestinationDatabase DbContext，直接用EF Core迁移功能就能自动生成结构匹配的表：

1. 打开Visual Studio的包管理器控制台
2. 执行命令生成迁移文件：

   Add-Migration InitialCreate -Context DestinationDatabase -OutputDir Migrations\DestinationMigrations
   

3. 执行迁移创建表：

   Update-Database -Context DestinationDatabase
   

后续源库结构变更时，只需更新Person实体类，重复上述两步就能同步目标库结构。

二、模块化数据迁移实现（基于SqlDataReader逐行读写） #

把迁移逻辑拆分为读取、写入、协调三个独立模块，便于维护和扩展。

1. 源数据读取模块 #

public class SourceDataReader
{
    private readonly string _sourceConnString;

    public SourceDataReader(string sourceConnString)
    {
        _sourceConnString = sourceConnString;
    }

    public IEnumerable<Person> ReadPersons()
    {
        using var conn = new SqlConnection(_sourceConnString);
        conn.Open();
        using var cmd = new SqlCommand("SELECT Id, Name, Age, Email FROM Person", conn);
        using var reader = cmd.ExecuteReader();
        
        while (reader.Read())
        {
            yield return new Person
            {
                Id = reader.GetInt32(reader.GetOrdinal("Id")),
                Name = reader.GetString(reader.GetOrdinal("Name")),
                Age = reader.GetInt32(reader.GetOrdinal("Age")),
                Email = reader.IsDBNull(reader.GetOrdinal("Email")) ? null : reader.GetString(reader.GetOrdinal("Email"))
            };
        }
    }
}

2. 目标数据写入模块 #

public class DestinationDataWriter
{
    private readonly DestinationDatabase _dbContext;

    public DestinationDataWriter(DestinationDatabase dbContext)
    {
        _dbContext = dbContext;
    }

    public async Task WritePersonsAsync(IEnumerable<Person> persons, int batchSize = 1000)
    {
        var batch = new List<Person>();
        foreach (var person in persons)
        {
            // 避免主键冲突，可根据需求改为更新逻辑
            var existing = await _dbContext.Persons.FindAsync(person.Id);
            if (existing == null)
            {
                batch.Add(person);
            }

            if (batch.Count >= batchSize)
            {
                await _dbContext.Persons.AddRangeAsync(batch);
                await _dbContext.SaveChangesAsync();
                batch.Clear();
            }
        }

        // 处理剩余数据
        if (batch.Count > 0)
        {
            await _dbContext.Persons.AddRangeAsync(batch);
            await _dbContext.SaveChangesAsync();
        }
    }
}

3. 迁移协调器（整合模块） #

public class PersonDataMigrator
{
    private readonly SourceDataReader _sourceReader;
    private readonly DestinationDataWriter _destinationWriter;

    public PersonDataMigrator(SourceDataReader sourceReader, DestinationDataWriter destinationWriter)
    {
        _sourceReader = sourceReader;
        _destinationWriter = destinationWriter;
    }

    public async Task MigrateAsync()
    {
        var persons = _sourceReader.ReadPersons();
        await _destinationWriter.WritePersonsAsync(persons);
    }
}

4. 调用示例 #

var sourceConnString = "你的源数据库连接字符串";
var destinationConnString = "你的目标数据库连接字符串";

var sourceReader = new SourceDataReader(sourceConnString);
var destinationContext = new DestinationDatabase(new DbContextOptionsBuilder<DestinationDatabase>()
    .UseSqlServer(destinationConnString)
    .Options);
var destinationWriter = new DestinationDataWriter(destinationContext);

var migrator = new PersonDataMigrator(sourceReader, destinationWriter);
await migrator.MigrateAsync();

三、优化建议 #

• 若源库数据量极大，给读取模块添加分页逻辑，修改SQL为SELECT Id, Name, Age, Email FROM Person ORDER BY Id OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY，循环读取直到无数据返回。
• 在写入模块中添加事务处理，用using var transaction = await _dbContext.Database.BeginTransactionAsync();包裹批量写入逻辑，确保数据一致性。

内容的提问来源于stack exchange，提问作者Leonard Vidican