mysql - 将数据表中的数据插入到MySql表中

Question

我的代码将数据从数据表插入到Mysql表中，但是过程需要很长时间。无论如何，我是否可以修改我的方法以对我的数据值进行“一次插入”？如果能在交易中拥有它就好了。谢谢。

 Dim result As Boolean = False
        Dim MysqlConn As MySqlConnection
        MysqlConn = New MySqlConnection()
        MysqlConn.ConnectionString = "Server=localhost;Database=duo;Uid=root;Pwd=pass1;"
        Using con As New MySqlConnection(MysqlConn.ConnectionString)

            For Each r As DataRow In _dtSir.Rows

                Using cmd As New MySqlCommand("INSERT INTO " & _serializer.GetTableName(_reportId) & " (SR, IDcol, System) 
                                                VALUES (@SR, @IDcol, @System)", con)

                    cmd.CommandType = CommandType.Text
                    cmd.Parameters.AddWithValue("@SR", r("SR#"))
                    cmd.Parameters.AddWithValue("@IDcol", CInt(r("ID")))
                    cmd.Parameters.AddWithValue("@System", r("System"))

                    If Not con.State = ConnectionState.Open Then
                        con.Open()
                    End If

                    cmd.ExecuteNonQuery()
                    result = True
                End Using
            Next

        End Using

        Return result
    End Function

Answer 1

试试这个看看是否更快:

Public Function test()
    Dim result As Boolean = False
    Using con As New MySqlConnection("Server=localhost;Database=duo;Uid=root;Pwd=pass1;")
        con.Open()
        For Each row As DataRow In_dtSir.Rows
        Using cmd = con.CreateCommand()
                cmd.CommandType = CommandType.Text
                cmd.CommandText = "INSERT INTO " & _serializer.GetTableName(_reportId) & " (SR, IDcol, System) VALUES ('" & row("SR#") & "', '" & CInt(row("ID")) & "','" & row("System") & "'", con)
                cmd.Connection = con
                cmd.ExecuteNonQuery()
                result = True
            End Using
        Next
        con.Close()
        SqlConnection.ClearPool(con)
    End Using
    Return result
End Function

由于我从未使用过MySqlConnection，因此可能有一些需要调整的地方