将整个数据表到数据库中一次，而不是一行一行的？ [英] Insert entire DataTable into database at once instead of row by row?

问题描述

我有一个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?

该数据表比SQL表少列。他们的休息应保留为空。

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

感谢。

推荐答案

我发现SqlBulkCopy的是一个简单的方法来做到这一点，并不需要storeprocedure要对SQL的。

I discovered SqlBulkCopy is an easy way to do this, and does not require a storeprocedure to be made on SQL.

下面是我如何实现它的一个例子：

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

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