Click here to Skip to main content
15,747,637 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,
In my application i want to insert multiple records at a time to mysql database. I am having a list of files in an array. I want to insert values of the files like id,name,path to mysql database. I tried the following, but it insert's only one file at a time. How to insert multiple records with one query?
My code:
MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection();
        MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand();

        string SQL;
        conn.ConnectionString = "server=localhost; userid=root;password=;database=test;";

            string[] files = Directory.GetFiles(@"E:\voices\", "*.wav");
foreach (string file in files)

                string filename = Path.GetFileName(file);
                string directory = Path.GetFullPath(file);
                cmd.Parameters.AddWithValue("@dwnfile_name", filename);
                cmd.Parameters.AddWithValue("@dwnfile_path", directory);

                SQL = "insert into sdwn_files(dwnfile_id,dwnfile_name,dwnfile_path) values(NULL, @dwnfile_name, @dwnfile_path)";
                cmd.Connection = conn;
                cmd.CommandText = SQL;

                MessageBox.Show("Files Inserted into database successfully!",
                    "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

        catch (MySql.Data.MySqlClient.MySqlException ex)
            MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
                "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);


The reason why it is inserting the data one by one is you do it inside the foreach loop. If you want it to do it just in one query you have to rewrite your query from the beginning but it will not be efficient enough. Check this link for available options: Multiple Ways to do Multiple Inserts[^]
Share this answer
use SqlBulckCopy Class
SQL Bulk Copy with C#.Net[^]

Good Luck
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900