c# - 在不依赖行状态准确性的情况下将 DataTable 中的行保存/合并到数据库？

Question

我需要在 C# 中编写一个方法来将给定数据表中的所有行保存回数据库，但我不能依赖于每一行的行状态。如果不大量循环遍历数据库表数据或不断查询数据库，我想不出如何最好地做到这一点。

我想对数据表中的每一行执行以下插入/更新操作，但效率更高:

INSERT INTO table
(   col1,
    col2    )
SELECT
    'value1',
    'value2'
FROM dual
WHERE NOT EXISTS ( SELECT * FROM table WHERE col1 = 'value1' );

UPDATE table
SET col2 = 'value2'
WHERE col1 = 'value1';

我认为这样的结构是最有效的:

private void SaveDataTable(DataTable dataTable)
{
    //Remove data from the dataTable where it already exists in the database

    //Determine if the values are to be inserted or updated

    //Save relevant data
}

关于如何最好地实现这一点有什么想法吗？

Answer 1

我强烈建议您让数据库来管理它，而不是从应用程序的角度来处理它。 SQL Server 针对集合操作进行了优化，您基本上想要执行两个集合操作:

• 更新表中与您的 DataTable 中也存在的记录不同的所有记录
• 将表中不存在的所有记录插入 DataTable

为此，我建议创建一个新表 tableStaging:

create table tableStaging (
    batchId uniqueidentifier not null, col1 int not null, col2 int not null)

几个要点:

• batchId 列允许您识别应用程序的不同调用者同时执行的多个操作。您希望为执行此操作的每个集合一次生成此值，而不是为每个记录。
• batchId 列是 uniqueidentifier因为它转换为 Guid struct并且可以通过调用 Guid.NewGuid 在客户端轻松生成.
• 您可能希望拥有一个由 batchId 和 table 表的主键组成的复合主键，以使即将进行的操作更有效率。

一旦你这样做了，你就可以使用 SqlBulkCopy class从您的 DataTable ( there's an overload of WriteToServer that takes a DataTable instance ) 批量插入记录。

最后，一旦记录在 tableStaging 类中，您就可以调用一个存储过程来简单地执行 update。对于两个表之间存在 batchId 列和主键的所有项目，insert对于存在于登台表中但不存在于目标表中的项目。或者，如果您使用的是 SQL Server 2008，则可以使用 merge statement一次完成。

然后，只需清理临时表即可。由于您在调用存储过程/运行命令时拥有 batchId，因此您可以在完成更新主表后删除记录，或者，您可以等到您不知道的时候一个人会用这个调用truncate table在表上(登台表不应该有外键，所以truncate table应该工作得很好)。