Fastest way Store data in database using c#

Question

I have a csv file with 3 million lines and want to stored it in a database using c#. The csv file looks like "device;date;value".

Shall I write it into an array or directly into a System.Data.DataTable? And what is the fastest way to store this DataTable into a Database (SQL-Server for example).

I tried to store the lines using 3 million insert into statements but it was too slow :)

thanks

possible duplicate of Fastest way to load data from text file then store it into database — Henk Holterman
– Henk Holterman, Commented Feb 26, 2013 at 11:24
Is this a one-off operation or do you need to do it repeatedly? — Polyfun
– Polyfun, Commented Feb 26, 2013 at 11:26

Habib · Accepted Answer · 2013-02-26 11:24:31Z

4

You can load the data in a DataTable and then use SqlBulkCopy for copying the date to the table in sql server

The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. .

answered Feb 26, 2013 at 11:24

Habib

224k30 gold badges420 silver badges446 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ash Burlaczenko · Accepted Answer · 2013-02-26 11:26:28Z

0

I'd guess BCP would be pretty fast. Once you have the data in a DataTable you can try

using (SqlBulkCopy bcp= new SqlBulkCopy(yourConnectionString))
{
    BulkCopy.DestinationTableName = "TargetTable";
    BulkCopy.WriteToServer(dataTable);
}

answered Feb 26, 2013 at 11:26

Ash Burlaczenko

25.6k16 gold badges70 silver badges103 bronze badges

Comments

JD11 · Accepted Answer · 2013-02-26 11:26:59Z

0

I think the best way is to open stream reader and create row line by line. Use ReadLine in a while loop and split to find differents parts.

answered Feb 26, 2013 at 11:26

JD11

3144 silver badges12 bronze badges

Comments

M K. Price · Accepted Answer · 2013-02-26 11:27:41Z

0

Sending 3 millions insert statements is bordering on crazy slow!

Buffer it by using transactions and reading in for example 200-1000 lines at a time (the smaller your data, the more you can read in at a time) then, after reading in these lines, commit your inserts to the database directly.

answered Feb 26, 2013 at 11:27

M K. Price

2283 silver badges10 bronze badges

Collectives™ on Stack Overflow

Fastest way Store data in database using c#

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related