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.