Search code examples
c#linq-to-sqlinsertonsubmit

Importing large text file into sql database

I read txt files and saving rows from this file to local database.The Problem is that program reads 700 000 rows and it takes long time to read whole file. I use linq to sql, firs I read the row, then i split it in to the Table object and then I submit into DB.

For example The row has format 

2014-03-01 00:08:02.380 00000000000001100111

this row is splited into DateTime and 20 columns (each column represents 1 channel (CH1 - CH20))

Is there a better (faster) way?

Solution

  • You can use the FileHelpers http://filehelpers.sourceforge.net/ to feed directly into SqlBulkCopy. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx That is by far the easiest and fastest approach.

    You can still use Linq-2-sql for read/non-batch writes but for bulkinsert is is simply too slow.