i want to insert 10000+ rows in my database fast (I tried with linqtosql and it took me more than 2 minutes) and after a research on the internet I found out about SQL Bulk Copy, but I am don't really understand it. Can someone give me a good example in my case? I fill a list from a .csv file so all my data are in this
List<myTable> datalist = new List<myTable>();
and I also have a context in order to execute SQL transactions.
To use SqlBulkCopy you need either DataTable or an IDataReader. If you have a List<T>, that can still work - via tools like FastMember which makes an IDataReader from a sequence of objects, for example:
using(var bcp = new SqlBulkCopy(connection))
using(var reader = ObjectReader.Create(data, "Id", "Name", "Description"))
{
bcp.DestinationTableName = "SomeTable";
bcp.WriteToServer(reader);
}
(obviously use datalist in place of data; the strings afterwards are the members to map into columns - you could use nameof here in recent versions of C#, i.e. nameof(myTable.Id) in place of "Id")
If you want to work directly from the CSV without the List<T> in the middle, CsvReader would be more appropriate.
