views:

54

answers:

0

I need to import 100.000 rows from CSV file into SQL Server database. I use CsvReader to read the file. File contains flat data and child entities data like so:

No Name    Age ... Address1 Address2 ... AddressN
1  Alex    20      London    Paris
2  Brian   30      New York   

Records will be inserted into main Clients table and child 'Addresses' table and some others.

Currently I use LINQ2SQL: iterate each row, map row to object (and child objects), validate and then insert all into database but inserting huge amount of data is very slow.

I consider using SqlBulkCopy class but I am facing problem with child entities that must be inserted as well. I also want to customize import like adding DateAdded field (that csv does not contain) or transform some fields.