一次将整个 DataTable 插入数据库而不是逐行插入? [英] Insert entire DataTable into database at once instead of row by row?

问题描述

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

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 插入数据库而不是逐行插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！