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?

Why are you doing this with C#? If you don't have to do that, there are instructions here on how to do this natively for MySql. — Steve Townsend
– Steve Townsend, Commented Apr 30, 2012 at 16:35
Have you tried anything? Have you executed any commands to mysql db? — empi
– empi, Commented Apr 30, 2012 at 16:36
Please look at this question, it seems exactly the response for you — Steve
– Steve, Commented Apr 30, 2012 at 16:37
If you still want to roll your own data importer, at least try to use FileHelpers — Michael Buen
– Michael Buen, Commented Apr 30, 2012 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
– John Ryann, Commented Apr 30, 2012 at 16:42

Nate · Accepted Answer · 2012-04-30 16:48:18Z

0

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.)

answered Apr 30, 2012 at 16:48

Nate

30.7k24 gold badges121 silver badges187 bronze badges

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

2 Comments

John Ryann Over a year ago

I need to loop on the values not on the insert. I have a list which contain the values

Nate Over a year ago

Maybe some sample data would be useful, because I don't think I understand what you're trying for.

MUG4N · Accepted Answer · 2012-04-30 16:52:11Z

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, 2012 at 16:52

MUG4N

19.8k11 gold badges58 silver badges86 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related