从datagridview到mysql数据库中插入值的最快方法是什么？ [英] What is the fastest way to insert values from datagridview to mysql database

问题描述

我有一个vb.net系统，我想从datagridview插入10,000或更多的记录到mysql数据库。但是当我尝试这个时，需要8分钟的10,000条记录。

  For i As Integer = 0 To DataGridView1.Rows.Count  -  1 
 
 Dim queryInsert As String =INSERT INTO tbl_shipdetails（ship_date，item_type，item_code，imei1，imei2）& _ 
VALUES（'& DataGridView1.Rows（i）.Cells（1）.Value&'，'& DataGridView1.Rows（i）.Cells（2）.Value& '，'& DataGridView1.Rows（i）.Cells（3）.Value&'，'& DataGridView1.Rows（i）.Cells（4）.Value&'，'& DataGridView1.Rows（i）.Cells（5）.Value&'）
 MySqlCmd = New MySqlCommand 
 MySqlCmd.Connection = Myconnect 
 MySqlCmd.CommandText = queryInsert 
 MySqlCmd .ExecuteNonQuery（）
下一个
  

我想知道有人知道什么是最快的方式去做这个？谁能帮帮我吗。我正在考虑在datagridview中将整个值插入到mysql中，只需一个插入（像批量），而不是使用循环，但是我不知道是否可能。

提前谢谢

解决方案

这将是更快的，但是有一个简单的优化您的查询

  Dim queryInsert As String =INSERT INTO tbl_shipdetails（ship_date， & _ 
item_type，item_code，imei1，imei2）& _ 
VALUES（@ p1，'@ p2，@ p3，@ p4，@ p5）
 Dim cmd = New MySqlCommand（queryInsert，Myconnect）
 cmd.Parameters.Add（ @ p1，MySqlDbType.VarChar）
 cmd.Parameters.Add（@ p2，MySqlDbType.VarChar）
 cmd.Parameters.Add（@ p3，MySqlDbType.VarChar）
 cmd.Parameters.Add（@ p4，MySqlDbType.VarChar）
 cmd.Parameters.Add（@ p5，MySqlDbType.VarChar）
对于i As Integer = 0 To DataGridView1.Rows.Count -  1 
 cmd.Parameters（@ p1）。Value = DataGridView1.Rows（i）.Cells（1）.Value 
 cmd.Parameters（@ p2）。Value = DataGridView1.Rows （i）.Cells（2）.Value 
 cmd.Parameters（@ p3）。Value = DataGridView1.Rows（i）.Cells（3）.Value 
 cmd.Parameters（@ p4 ）.Value = DataGridView1.Rows（i）.Cells（4）.Value 
 cmd.Parameters（@ p5）。Value = DataGridView1.Rows（i）.Cells（5）.Value 
 cmd.ExecuteNonQuery（）
下一个
  

使用参数允许您构建MySqlCommand只有一个时间在循环之外也避免连接字符串所需的工作。 （更不用说Sql注入的问题）

请注意，我已经在sql文本中跟踪了你的提示，其中所有的字段似乎都是字符串（VarChar）类型。如果您的字段具有不同的数据类型，那么您应该将MySqlDbType枚举调整为正确的数据类型（并转换输入值=

I have a vb.net system and I want to insert 10,000 or more records from datagridview to mysql database. But it takes 8mins for 10,000 records when i tried this

For i As Integer = 0 To DataGridView1.Rows.Count - 1

                        Dim queryInsert As String = "INSERT INTO tbl_shipdetails (ship_date, item_type, item_code, imei1, imei2)" & _
                                                    "VALUES('" & DataGridView1.Rows(i).Cells(1).Value & "','" & DataGridView1.Rows(i).Cells(2).Value & "','" & DataGridView1.Rows(i).Cells(3).Value & "','" & DataGridView1.Rows(i).Cells(4).Value & "','" & DataGridView1.Rows(i).Cells(5).Value & "')"
                        MySqlCmd = New MySqlCommand
                        MySqlCmd.Connection = Myconnect
                        MySqlCmd.CommandText = queryInsert
                        MySqlCmd.ExecuteNonQuery()
                    Next

I want to know if someone knows what is the fastest way to do this? can anyone please help me. I'm thinking of inserting the whole values in datagridview to mysql with just one insertion (like as bulk) and not using a loop but I don't know if it's possible.

thanks in advance!

解决方案

I can't tell how much faster this will be, however there are simple optimizations to your query

Dim queryInsert As String = "INSERT INTO tbl_shipdetails (ship_date, " & _
                             "item_type, item_code, imei1, imei2)" & _
                             "VALUES(@p1,'@p2,@p3,@p4,@p5)"
Dim cmd = New  MySqlCommand(queryInsert, Myconnect)
cmd.Parameters.Add("@p1", MySqlDbType.VarChar)
cmd.Parameters.Add("@p2", MySqlDbType.VarChar)    
cmd.Parameters.Add("@p3", MySqlDbType.VarChar)    
cmd.Parameters.Add("@p4", MySqlDbType.VarChar)    
cmd.Parameters.Add("@p5", MySqlDbType.VarChar)
For i As Integer = 0 To DataGridView1.Rows.Count - 1
    cmd.Parameters("@p1").Value = DataGridView1.Rows(i).Cells(1).Value
    cmd.Parameters("@p2").Value = DataGridView1.Rows(i).Cells(2).Value 
    cmd.Parameters("@p3").Value = DataGridView1.Rows(i).Cells(3).Value
    cmd.Parameters("@p4").Value = DataGridView1.Rows(i).Cells(4).Value
    cmd.Parameters("@p5").Value = DataGridView1.Rows(i).Cells(5).Value
    cmd.ExecuteNonQuery()
Next

Using parameters allows you to build the MySqlCommand just one time outside the loop and avoids also the work needed to concatenate the strings. (Not to mention the problem of Sql Injection)

Notice that I have followed your hint in the sql text where all of your fields seems to be of string(VarChar) type. If your fields are of different datatype then you should adjust the MySqlDbType enum to your correct datatype (and convert the input values=

这篇关于从datagridview到mysql数据库中插入值的最快方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！