SqlBulkCopy from a List<>

Question

How can I make a big insertion with SqlBulkCopy from a List<> of simple object ?

Do I implement my custom IDataReader ?

Answer 1

With FastMember, you can do this without ever needing to go via DataTable (which, in my tests, more-than-doubles the performance):

using(var bcp = new SqlBulkCopy(connection)) using(var reader = ObjectReader.Create(data, "Id", "Name", "Description")) {     bcp.DestinationTableName = "SomeTable";     bcp.WriteToServer(reader); } 

Note that ObjectReader can also work with non-generic sources, and it is not necessary to specify the member-names in advance (although you probably want to use the ColumnMappings aspect of SqlBulkCopy if you don't specify them in the ObjectReader itself).

Answer 2

Simply create a DataTable from your list of objects and call SqlBulkCopy.WriteToServer, passing the data table.

You might find the following useful:

• Adding columns to a DataTable. Add a column for each property/field you wish to write.
• Adding rows to a DataTable. Add a row for each object in your list.

For maximum performance with SqlBulkCopy, you should set an appropriate BatchSize. 10,000 seems to work well - but profile for your data.

You might also observe better results when using SqlBulkCopyOptions.TableLock.

An interesting and informative analysis of SqlBulkCopy performance can be found here.