c# - Insert entire DataTable into database at once instead of row by row?

Question

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

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?

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

Answer 1

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