Inserting all rows of a DataTable into a SQL Server table, including Id values

Question

I'm trying to insert the contents of a DataTable verbatim into a SQL Server database using ADO.NET. (Note: I'm using SQL Server CE at the moment, but I'd like a solution that works in either regular or CE.)

Using the following code results in a command that does not include the Id column:

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

This causes the Id to be auto-generated, which is not what I want. I need each Id to be copied exactly as it appears in the DataTable.

Is there any way to force SqlCeCommandBuilder to include the Id field in the insert command when using GetInsertCommand()? If not, is there another solution? I'm open to using ADO.NET or EntityFramework.

Note that the Id column is a primary key with AutoIncrement = true.

Edit

I came up with what I thought was a solution to this, but that ended up just creating another problem (see my answer below). So, I'm still looking for a good answer to this.

Edit 2

I just edited my answer. I think I found a workaround. Still clunky and database specific. Would prefer a proper ADO.NET or EntityFramework solution.

Answer 1

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.

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().

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();
}

---

Edit

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

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.

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.

---

Edit 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();
}

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

Answer 2

You will not be able inserting the Id although you will prepare the insert statement manually until the table schema (AutoIncrement=false) has been changed.