Efficient way to insert a list data into a mySQL database in C#

Question

I have csv files which I would like to dump into a db. so I crated a loop of the files, and inside the loop I have created a list called data for each line

StreamReader file = new StreamReader(itemChecked.ToString());//read the file

while ((line = file.ReadLine())  != null)
{
    if (start_flag == true) // start processing the numbers, get the real data
    {
        List<string> data = new List<string>();
        data.AddRange(line.Replace("\"", "").Split(',').AsEnumerable());
    }
}

so far so good.

Now I want to insert the list data into the database. The list is quite large. I don't want to type every single one of them like so:

insert into table1 (tablenames) values (a, b, c on and on)

How can I loop the list and insert the data into the database?

Have you tried anything? Have you executed any commands to mysql db? — empi, Apr 30 '12 at 16:36
Please [look at this question](http://stackoverflow.com/questions/5592196/mysql-bulk-insert-a-text-file), it seems exactly the response for you — Steve, Apr 30 '12 at 16:37
Why are you doing this with C#? If you don't have to do that, there are instructions [here](http://www.billsternberger.net/mysql/import-csv-file-to-mysql/) on how to do this natively for MySql. — Steve Townsend, Apr 30 '12 at 16:35
If you still want to roll your own data importer, at least try to use [FileHelpers](http://filehelpers.sourceforge.net/) — Michael Buen, Apr 30 '12 at 16:41
I must do this in c#. It's an application and inserting the data into the db is just the small part of the app. Btw, I have done numerous projects/apps with C# and mysql. No problem at all. If I had to switch to sql server the question would be the same. — John Ryann, Apr 30 '12 at 16:42
Then LinqToCSV should be helpful in encapsulating at least the read part http://linqtocsv.codeplex.com/. Seems like you need to directly read the input into something from ADO.Net that can just be flushed to your new DB, rather than a list of strings? — Steve Townsend, Apr 30 '12 at 16:49

score 0 · Answer 1 · answered Apr 30 '12 at 16:48

First, you will need to use the ADO.NET Driver for MySQL (Connector/NET) to connect to the database.

Second, you will want to open a connection to the database, and then insert some data:

var connection = new MySqlConnection();
connection.ConnectionString =
   "server=localhost;"
    + "database=DBNAME;"
    + "uid=USERNAME;"
    + "password=PASSWORD;";

connection.Open();

foreach(var datum in data) 
{
    var command = connection.CreateCommand();
    command.CommandText =
        "insert into table1 (tablenames)"
        + " values "
        + "(a, b, c on and on)";

    var result = command.ExecuteReader();
}

My example was based off this article. This isn't a perfect solution, but it should get you started. You may want to look into MySQL Transactions to batch your inserts into efficient groupings (depending on data size maybe 100-1000 at a time.)

I need to loop on the values not on the insert. I have a list which contain the values — John Ryann, Apr 30 '12 at 16:55
Maybe some sample data would be useful, because I don't think I understand what you're trying for. — Nate, Apr 30 '12 at 20:13

score 0 · Answer 2 · answered Apr 30 '12 at 16:52

0

I would use bulkcopy to import all the data from the csv file at once. You can find a sample right here:

http://www.codeproject.com/Articles/30705/C-CSV-Import-Export

I hope this is what you are looking for

Greetings

answered Apr 30 '12 at 16:52

MUG4N

19,377
11
56
83

Efficient way to insert a list data into a mySQL database in C#

2 Answers2