一次将整个 DataTable 插入数据库而不是逐行插入数据库?

问题描述

我有一个 DataTable，需要将整个内容推送到数据库表中.

I have a DataTable and need the entire thing pushed to a Database table.

我可以用一个 foreach 把它全部放在那里，一次插入每一行.由于有几千行，这会非常缓慢.

I can get it all in there with a foreach and inserting each row at a time. This goes very slow though since there are a few thousand rows.

有没有什么方法可以更快地一次性完成整个数据表?

Is there any way to do the entire datatable at once that might be faster?

DataTable 的列数少于 SQL 表.其余的应为空.

The DataTable has less columns than the SQL table. the rest of them should be left NULL.

推荐答案

我发现 SqlBulkCopy 是一种简单的方法，并且不需要在 SQL Server 中编写存储过程.

I discovered SqlBulkCopy is an easy way to do this, and does not require a stored procedure to be written in SQL Server.

这是我如何实现它的示例:

Here is an example of how I implemented it:

// take note of SqlBulkCopyOptions.KeepIdentity , you may or may not want to use this for your situation.

using (var bulkCopy = new SqlBulkCopy(_connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity))
{
      // my DataTable column names match my SQL Column names, so I simply made this loop. However if your column names don't match, just pass in which datatable name matches the SQL column name in Column Mappings
      foreach (DataColumn col in table.Columns)
      {
          bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
      }

      bulkCopy.BulkCopyTimeout = 600;
      bulkCopy.DestinationTableName = destinationTableName;
      bulkCopy.WriteToServer(table);
}

这篇关于一次将整个 DataTable 插入数据库而不是逐行插入数据库?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！