将 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结构，并且可以通过调用Guid.NewGuid在客户端轻松生成。
• 您可能希望有一个复合主键，该主键由batchId和table表的主键组成，以使即将执行的操作更加高效。

执行此操作后，可以使用 SqlBulkCopy 类从DataTable批量插入记录（WriteToServer的重载需要DataTable实例）。

最后，一旦记录位于 tableStaging 类中，就可以调用一个存储过程，该过程只对两个表之间存在batchId列和主键的所有项执行update，插入临时表中而不是目标表中存在的项。 或者，如果您使用的是 SQL Server 2008，则可以使用 merge 语句并一次性执行这两项操作。

然后，只需清理暂存表即可。 由于在调用存储过程/运行命令时具有batchId，因此可以在更新完主表后删除记录，或者，可以等到知道没有人会使用它并在表上调用truncate table（临时表不应有外键， 所以truncate table应该工作得很好）。

Answer 2

虽然我无法避免查询给定数据表中的每一行，但我认为这现在有效。 感谢您的回复;他们都非常乐于助人。

（甲骨文特定）：

internal override bool RecordExists(DataRow row)
{
    string query = string.Concat("SELECT 1 FROM ", row.Table.TableName, " ", GetDbTableFilter(row, row.Table.PrimaryKey), " AND ROWNUM = 1");
    using(OracleCommand cmd = new OracleCommand(query, (OracleConnection)_connectionLibrary.CurrentConnection))
        using(OracleDataReader reader = cmd.ExecuteReader())
            return (reader != null && reader.Read());
}
internal override string GetDbTableFilter(DataRow row, DataColumn[] columns)
{
    string filter = "";
    foreach (DataColumn column in columns)
    {
        if (!string.IsNullOrEmpty(filter))
            filter += " AND ";
        if (string.IsNullOrEmpty(row[column].ToString()))
        {
            filter += string.Concat("(", column.ColumnName, " IS NULL OR TRIM(", column.ColumnName, ") = '')");
            continue;
        }
        if (column.DataType.Equals(typeof(DateTime)))
        {
            filter += string.Concat(column.ColumnName, " = TO_DATE('", row[column].ToString(), "')");
            continue;
        }
        filter += string.Concat(column.ColumnName, " = '", row[column].ToString(), "'");
    }
    return string.Concat("WHERE ", filter);
}
public override void SaveDataTable(DataTable dataTable)
{
    dataTable.AcceptChanges();
    foreach (DataRow row in dataTable.Rows)
        if(RecordExists(row))
            row.SetModified();
        else
            row.SetAdded();
    using (OracleDataAdapter adapter = new OracleDataAdapter(string.Format("SELECT * FROM {0}", dataTable.TableName), (OracleConnection)_connectionLibrary.CurrentConnection))
        using (OracleCommandBuilder commandBuilder = new OracleCommandBuilder(adapter))
        {
            commandBuilder.SetAllValues = true;
            commandBuilder.ConflictOption = ConflictOption.OverwriteChanges;
            adapter.InsertCommand = commandBuilder.GetInsertCommand(true);
            adapter.UpdateCommand = commandBuilder.GetUpdateCommand(true);
            adapter.Update(dataTable);
        }
}