Inserting values to mysql using c#

Question

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:

Collapse | Copy 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;";
        conn.Open();
 
        
        
        try
        {
            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;
                cmd.ExecuteNonQuery();
 

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

 

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

        
        }

Thanks in advance

---

psgviscom

Answer 1

:):)

---

JP Cowboy Coders Unite!

Answer 2

Nothing wrong with the way you are doing it except I would take the messagebox out of the loop and have it post when it has completed entirely. If you were using SQL Server you could have used SqlBulkCopy or simply use SSIS. But since you are doing a loop, do this right before the loop:

1. open the connection before the loop

2. add this --> cmd.Prepare(); (This will make the process go faster)

Then add the conn.Close in a finally block

---

John Grove, Senior Software Engineer http://www.digitizedschematic.com/

Answer 3

Also, I would add the Parameter objects before the loop then simply change the value upon each iteration. Maybe something like so:

using (MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection())
{
      using (MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand())
      {
          string SQL = "insert into sdwn_files(dwnfile_id,dwnfile_name,dwnfile_path) values(NULL, @dwnfile_name, @dwnfile_path)";             
          MySqlParameter file = new SqlParameter("@dwnfile_name", MySqlType.String);
          cmd.Parameters.Add(file);
          MySqlParameter dir = new SqlParameter("@dwnfile_path", MySqlType.String);
          cmd.Parameters.Add(dir);    
      
          conn.ConnectionString = "server=localhost; userid=root;password=;database=test;";
          conn.Open();
          cmd.Connection = conn;
          cmd.Prepare();      
          try
          {
             String[] files = Directory.GetFiles(@"E:\voices\", "*.wav");
             foreach (String file in files)
             {                 
                String filename = Path.GetFileName(file);
                String directory = Path.GetFullPath(file);
                file.Value = filename;
                dir.Value = directory;                
                cmd.ExecuteNonQuery();                
             }            
           }
           catch (MySql.Data.MySqlClient.MySqlException ex)
           {
              MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
              "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
           }
           finally
           {
              MessageBox.Show("Files Inserted into database successfully!",
                  "Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
              conn.Close();
           }
      }
}       

---

John Grove, Senior Software Engineer http://www.digitizedschematic.com/

Answer 4

Hi psgviscom,

  There are some other ways to meet your actual requirements.You can use following syntax to implement it:

  insert into `table`(col1,col2,col3)values($v1,$v2,$v3),($val1,$val2,$val3);

  $val2 and $val3 that you can replace them with  file name and directory name. The second way is to use the following syntax to implement it,but you need to  select two dimentions data to insert your target table.

insert into `table`(col1,col2,col3) select col1,col2,col3 form `table2` where *****

  I hope it will solve your problem.

Sincerely,

Jason Wang

---

orichisonic http://blog.csdn.net/orichisonic If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

Answer 5

Could you please explain me in detail

---

psgviscom

Answer 6

sqlparameter belongs to sql server know.

---

psgviscom

Answer 7

Hello psgvicom,

for to insert data in mySql follow this thread http://stackoverflow.com/questions/4187202/insert-multiple-row-using-the-same-mysql-connection

Regards.

---

• Carmelo La Monica

• Blog
• WordPress
• Twitter

Answer 8

Hi,

either follow JohnGrove`s aproach by just changing the value of the parameters or just call cmd.parameters.clear() before you call AddWithValue().

---

Hannes

If you have got questions about this, just ask.

In a perfect world,
users would never enter data in the wrong form,
files they choose to open would always exist
and code would never have bugs.

C# to VB.NET: http://www.developerfusion.com/tools/convert/csharp-to-vb/

Answer 9

Hi psgviscom,

Welcome to the MSDN forum!

Please check out the following link:

Connecting to MySQL Database using C# and .NET
http://www.codeproject.com/Articles/71346/Connecting-to-MySQL-Database-using-C-and-NET 

// Set the UPDATE command and parameters.
adapter.UpdateCommand = new MySqlCommand(
  "UPDATE items SET ItemName=@ItemName, Price=@Price, _
	AvailableQuantity=@AvailableQuantity, Updated_Dt=NOW() _
	WHERE ItemNumber=@ItemNumber;",connection);
adapter.UpdateCommand.Parameters.Add("@ItemNumber", _
	MySqlDbType.Int16, 4, "ItemNumber");
adapter.UpdateCommand.Parameters.Add_
	("@ItemName", MySqlDbType.VarChar, 100, "ItemName");
adapter.UpdateCommand.Parameters.Add_
	("@Price", MySqlDbType.Decimal, 10, "Price");
adapter.UpdateCommand.Parameters.Add_
	("@AvailableQuantity", MySqlDbType.Int16, 11, "AvailableQuantity");
adapter.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;

// Set the INSERT command and parameter.
adapter.InsertCommand = new MySqlCommand(
    "INSERT INTO items VALUES (@ItemNumber,@ItemName,_
	@Price,@AvailableQuantity,NOW());",connection);
adapter.InsertCommand.Parameters.Add("@ItemNumber", _
	MySqlDbType.Int16, 4, "ItemNumber");
adapter.InsertCommand.Parameters.Add("@ItemName", _
	MySqlDbType.VarChar, 100, "ItemName");
adapter.InsertCommand.Parameters.Add("@Price", _
	MySqlDbType.Decimal, 10, "Price");
adapter.InsertCommand.Parameters.Add_
	("@AvailableQuantity", MySqlDbType.Int16, 11, "AvailableQuantity");
adapter.InsertCommand.UpdatedRowSource = UpdateRowSource.None;

// Set the DELETE command and parameter.
adapter.DeleteCommand = new MySqlCommand(
   "DELETE FROM items " + "WHERE ItemNumber=@ItemNumber;", connection);
adapter.DeleteCommand.Parameters.Add("@ItemNumber", _
	MySqlDbType.Int16, 4, "ItemNumber");
adapter.DeleteCommand.UpdatedRowSource = UpdateRowSource.None;

Meanwhile, you may try the MYSQL forums for MySQL DB issue:

http://forums.mysql.com/list.php?47

Hope it helps

yoyo

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.