How can I make a big insertion with SqlBulkCopy from a List<> of simple object ?
Do I implement my custom IDataReader ?
Advertisement
Answer
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.