Patrice Pezillier Patrice Pezillier - 1 year ago 75
SQL Question

SqlBulkCopy from a List<>

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

Do I implement my custom IDataReader ?

Answer Source

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

You might find the following useful:

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.