sql bulk insert c# in asp.net

Question

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.

Check a tutorial, create a new project using new database and do the bulk copy. After understanding the tutorial start implementing it in your case. — mybirthname, Jul 07 '17 at 12:26

score 1 · Answer 1 · answered Jul 07 '17 at 12:26

1

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.

answered Jul 07 '17 at 12:26

Marc Gravell

1,026,079
266
2,566
2,900

"The name 'ObjectReader' does not exist in current context" – L. Achilles Jul 07 '17 at 12:53
@L.Achilles yeah, that's an example of using FastMember - to use that you'll need to install FastMember from nuget – Marc Gravell Jul 07 '17 at 13:29
Oh, unfortunately I can't download new nuget packages because I can't have any internet access with that pc. – L. Achilles Jul 07 '17 at 13:52

sql bulk insert c# in asp.net

1 Answers1