使用C#将值插入mysql [英] Inserting values to mysql using c#

问题描述

大家好，
在我的应用程序中，我想一次向mysql数据库插入多个记录.我有一个数组中的文件列表.我想插入id，name，mysql数据库路径之类的文件的值.我尝试了以下操作，但一次只能插入一个文件.如何通过一个查询插入多个记录?
我的代码:

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;";
        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);
        }


        
        }

推荐答案

之所以要一个接一个地插入数据，是因为要在foreach循环内进行操作.如果您希望它仅在一个查询中执行此操作，则必须从头开始重写查询，但效率不够.检查此链接以获取可用选项:执行多次插入的多种方法 [ ^ ]

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[^]

嘿，
使用SqlBulckCopy类
使用C#.Net进行SQL批量复制 [

Hey,
use SqlBulckCopy Class
SQL Bulk Copy with C#.Net[^]

Good Luck

这篇关于使用C#将值插入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！