c# - 将 DataTable 的所有行插入到 SQL Server 表中，包括 Id 值

Question

我正在尝试使用 ADO.NET 将 DataTable verbatim 的内容插入到 SQL Server 数据库中。 (注意:我目前正在使用 SQL Server CE，但我想要一个既能在常规环境中运行，也能在 CE 环境中运行的解决方案。)

使用以下代码会产生不包含 Id 列的命令:

var builder = new SqlCeCommandBuilder();
builder.DataAdapter = adapter;
builder.SetAllValues = true;
var command = builder.GetInsertCommand(true);

这会导致自动生成 Id，这不是我想要的。我需要将每个 Id 完全按照它在 DataTable 中的显示进行复制。

有什么方法可以强制 SqlCeCommandBuilder 在使用 GetInsertCommand() 时在插入命令中包含 Id 字段？如果没有，还有其他解决方案吗？我愿意使用 ADO.NET 或 EntityFramework。

请注意，Id 列是 AutoIncrement = true 的主键。

编辑

我想出了我认为可以解决这个问题的方法，但最终却造成了另一个问题(请参阅下面我的回答)。所以，我仍在寻找一个好的答案。

编辑2

我刚刚编辑了我的答案。我想我找到了解决方法。仍然笨拙且特定于数据库。更喜欢适当的 ADO.NET 或 EntityFramework 解决方案。

Answer 1

我花了很多时间来弄乱CommandBuilder ，包括手动将“Id”插入 CommandText 并向参数添加一个 Id，但我无法让它工作。

所以我决定暂时放弃，自己滚动插入查询。下面的代码似乎适用于我提出的几个示例场景，但我可能会在 ItemToSqlFormattedValue() 中遗漏一些转换。 .

非常感谢评论/更正，如果您有更好的解决方案，请将其作为答案发布。

public virtual void FillDatabaseTable(DataTable table)
{
    var connection = _dbContextLocator.Current.Database.Connection;
    connection.Open();
    SetIdentityInsert(table, connection, true);
    FillDatabaseRecords(table, connection);
    SetIdentityInsert(table, connection, false);
    connection.Close();
}

private void FillDatabaseRecords(DataTable table, DbConnection connection)
{
    var command = GetNewCommand();
    command.Connection = connection;
    var rawCommandText = string.Format("insert into {0} values ({1})", table.TableName, "{0}");
    foreach (var row in table.AsEnumerable())
        FillDatabaseRecord(row, command, rawCommandText);
}

private void FillDatabaseRecord(DataRow row, DbCommand command, string rawCommandText)
{
    var values = row.ItemArray.Select(i => ItemToSqlFormattedValue(i));
    var valueList = string.Join(", ", values);
    command.CommandText = string.Format(rawCommandText, valueList);
    command.ExecuteNonQuery();
}

private string ItemToSqlFormattedValue(object item)
{
    if (item is System.DBNull)
        return "NULL";
    else if (item is bool)
        return (bool)item ? "1" : "0";
    else if (item is string)
        return string.Format("'{0}'", ((string)item).Replace("'", "''"));
    else if (item is DateTime)
        return string.Format("'{0}'", ((DateTime)item).ToString("yyyy-MM-dd HH:mm:ss"));
    else
        return item.ToString();
}

private void SetIdentityInsert(DataTable table, DbConnection connection, bool enable)
{
    var command = GetNewCommand();
    command.Connection = connection;
    command.CommandText = string.Format(
        "set IDENTITY_INSERT {0} {1}",
        table.TableName,
        enable ? "on" : "off");
    command.ExecuteNonQuery();
}

---

编辑

我发现了一个问题。尽管关闭了 IDENTITY_INSERT，SQL Server CE 似乎无法弄清楚下一个 ID 应该是什么，因此如果您尝试在不指定 Id 值的情况下进行插入，它会失败并出现以下错误:

A duplicate value cannot be inserted into a unique index

我想 SQL Server CE 不只是使用 MAX(Id) + 1在确定下一个 ID 应该是什么时。我相信它会尝试根据过去的插入来跟踪它，但是当在打开 IDENTITY_INSERT 的情况下执行插入时，不会发生这种跟踪。无论如何，这是我的直觉。

因此，目前，如果数据库是只读的或者您始终生成自己的 ID，这实际上只是一个可行的解决方案。

---

编辑2

我认为/希望这能解决问题:

public virtual void FillDatabaseTable(DataTable table)
{
    var connection = _dbContextLocator.Current.Database.Connection;
    connection.Open();
    ReseedIdColumn(table, connection);
    SetIdentityInsert(table, connection, true);
    FillDatabaseRecords(table, connection);
    SetIdentityInsert(table, connection, false);
    connection.Close();
}

private void ReseedIdColumn(DataTable table, DbConnection connection)
{
    if (table.Rows.Count == 0) return;
    var command = GetNewCommand();
    command.Connection = connection;
    command.CommandText = string.Format(
        "alter table {0} alter column Id IDENTITY ({1}, 1)",
        table.TableName,
        table.AsEnumerable().Max(t => t.Field<int>("Id")) + 1);
    command.ExecuteNonQuery();
}

// note: for SQL Server, may need to replace `alter table` statement with this:
// "dbcc checkident({0}.Id, reseed, {1})"

private void FillDatabaseRecords(DataTable table, DbConnection connection)
{
    var command = GetNewCommand();
    command.Connection = connection; //todo combine this sequence
    var rawCommandText = string.Format("insert into {0} values ({1})", table.TableName, "{0}");
    foreach (var row in table.AsEnumerable())
        FillDatabaseRecord(row, command, rawCommandText);
}

private void FillDatabaseRecord(DataRow row, DbCommand command, string rawCommandText)
{
    var values = row.ItemArray.Select(i => ItemToSqlFormattedValue(i));
    var valueList = string.Join(", ", values);
    command.CommandText = string.Format(rawCommandText, valueList);
    command.ExecuteNonQuery();
}

private string ItemToSqlFormattedValue(object item)
{
    const string quotedItem = "'{0}'";
    if (item is System.DBNull)
        return "NULL";
    else if (item is bool)
        return (bool)item ? "1" : "0";
    else if (item is Guid)
        return string.Format(quotedItem, item.ToString());
    else if (item is string)
        return string.Format(quotedItem, ((string)item).Replace("'", "''"));
    else if (item is DateTime)
        return string.Format(quotedItem, ((DateTime)item).ToString("yyyy-MM-dd HH:mm:ss"));
    else
        return item.ToString();
}

private void SetIdentityInsert(DataTable table, DbConnection connection, bool enable)
{
    var command = GetNewCommand();
    command.Connection = connection;
    command.CommandText = string.Format(
        "set IDENTITY_INSERT {0} {1}",
        table.TableName,
        enable ? "on" : "off");
    command.ExecuteNonQuery();
}

主要区别在于我现在正在重新播种 Id 列。这似乎可以解决问题。