我花了很多时间与 CommandBuilder的搞乱，包括手动插入ID到的CommandText和添加一个ID参数，但我无法得到它的工作。

I spent a lot of time messing with CommandBuilder, including manually inserting "Id" into the CommandText and adding an Id to the parameters, but I just couldn't get it to work.

所以，我决定放弃，对于时刻，刚刚推出的插入查询自己。下面的code似乎工作的若干示例场景，我把它，但我可能是缺少 ItemToSqlFormattedValue一些转换（）。

So I decided to give up on that for the moment and just roll the insert queries myself. The code below seems to work for several example scenarios I threw at it, but I could be missing some conversions in ItemToSqlFormattedValue().

评论/更正多少AP preciated，如果你有一个更好的解决方案，请张贴作为回答。

Comments/corrections much appreciated, and if you have a better solution, please post it as an answer.

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值：

I found a problem with this. Despite turning IDENTITY_INSERT back off, SQL Server CE can't seem to figure out what the next ID is supposed to be, so it fails with the following error if you try to do an insert without specifying an Id value:

一个重复的值不能插入唯一索引

A duplicate value cannot be inserted into a unique index

我猜的SQL Server CE不只是使用 MAX（ID）+ 1 搞清楚什么时候下一个ID应。我相信它试图基于过去插入跟踪它，但是当插入物进行与IDENTITY_INSERT接通时，这个跟踪不会发生。这是我的预感呢。

I guess SQL Server CE doesn't just use MAX(Id) + 1 when figuring out what the next Id should be. I believe it tries to track it based on past inserts, but when inserts are performed with IDENTITY_INSERT turned on, this tracking doesn't occur. That's my hunch anyway.

所以，在目前，这是真的，如果数据库是只读的，或者你总是生成你自己的ID唯一一个可行的解决方案。

So, at the moment, this is really only a viable solution if the database is read-only or you always generate your own Id's.

编辑2

我想/希望这个修复它：

I think/hope this fixes it:

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列。这似乎这样的伎俩。

The key difference is that I'm now reseeding the Id column. That seemed to do the trick.