mysql - VB中如何同时执行两个查询？ (MySql)

Question

同时执行两个查询的最有效方法是什么？我想在单击提交按钮时同时执行两个 INSERT sql 查询(不同的表)。这可能吗？

这就是我所做的:

    Dim conn As New MySqlConnection("Server = localhost; user id = root;password = ; database = ddap_hr")
    Dim sqlQuery1 As String = "INSERT INTO applicants VALUES ( '" & lblID.Text & "' , '" & txtLName.Text & "','" & txtFName.Text & "','" & txtMName.Text & "','" & cmboGender.Text & "','" & mtxtAge.Text & "','" & dtpBdate.Value & "','" & cmboStatus.Text & "','" & txtSSS.Text & "','" & txtTin.Text & "','" & txtReligion.Text & "','" & txtAddress.Text & "','" & mtxtContactNum.Text & "','" & txtEmail.Text & "')"
    Dim sqlQuery2 As String = "INSERT INTO appli_idgen(Lzero) VALUES ('" & lblNum.Text & "')"
    Dim cmd1 As New MySqlCommand(sqlQuery1)
    Dim cmd2 As New MySqlCommand(sqlQuery2)
    Dim rdr As MySqlDataReader

    Dim ConfirmMsg = MessageBox.Show("Are all the datas correct?" & Environment.NewLine & "   • Last Name:  " & txtLName.Text & Environment.NewLine & "   • First Name:  " & txtFName.Text & Environment.NewLine & "   • Middle Name:  " & txtMName.Text & Environment.NewLine & "   • Gender:  " & cmboGender.Text & Environment.NewLine & "   • Age:  " & mtxtAge.Text & Environment.NewLine & "   • Date of Birth:  " & dtpBdate.Text & Environment.NewLine & "   • Status:  " & cmboStatus.Text & Environment.NewLine & "   • SSS:  " & txtSSS.Text & Environment.NewLine & "   • TIN:  " & txtTin.Text & Environment.NewLine & "   • Religion:  " & txtReligion.Text & Environment.NewLine & "   • Address:  " & txtAddress.Text & Environment.NewLine & "   • Contact Number:  " & mtxtContactNum.Text & Environment.NewLine & "   • E-mail:  " & txtEmail.Text & Environment.NewLine, "", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, 0, False)

    If ConfirmMsg = MsgBoxResult.Yes Then
        Try
            Try
                cmd1.Connection = conn
                conn.Open()
                cmd1.ExecuteNonQuery()
                rdr = cmd.ExecuteReader
                rdr.Read()
            Catch ex1 As MySqlException
                MsgBox(ex1.Message.ToString)
            Finally
                conn.Close()
            End Try

            Try
                cmd2.Connection = conn
                conn.Open()
                cmd2.ExecuteNonQuery()
                rdr = cmd.ExecuteReader
                rdr.Read()
            Catch ex2 As MySqlException
                MsgBox(ex2.Message.ToString)
            Finally
                conn.Close()
            End Try
        Catch ex As MySqlException
                MsgBox(ex.Message.ToString)
        Finally
            Dim addAnother = MessageBox.Show("Do you want to add another applicant?", "", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1, 0, False)
            If addAnother = MsgBoxResult.No Then
               Me.Close()
               Main_home.Refresh()
            End If
        End Try

    End If

我想尽可能减少代码行。我需要你的帮助。顺便说一句，我正在使用 MySql。抱歉，因为我是 VB 新手。提前致谢。

Answer 1

如果您向我们展示您尝试过的方法，将会有所帮助， friend 。

就目前情况而言，您可以创建一个子例程，它将对 MySQL 执行非查询，然后在任何触发器(按钮、复选框等)上调用该子例程两次:

注意:这需要您知道如何使用适用于 .NET 的 MySQL 连接器您可以查找更多相关信息 here

   Public Sub MyNonQuery(ByVal SQCommand as String)
      Dim conn As New MySqlConnection("server=your server ip; port=the server port; uid='your user id';password ='your password';")

      Dim SQLCMD as new MySqlCommand(SQCommand, conn)

      conn.open()
      SQLCMD.ExecuteNonQuery()
      conn.close()
    End Sub

现在您可以通过以下方式使用此子程序:

1. 调用两次

   Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
   
       MyNonQuery("Insert into db.tbl1 values(...);")
       MyNonQuery("Insert into db.tbl2 values(...);")
   
    End Sub
   

2. 在一次调用中，发送两个 SQL 命令

     Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
   
       MyNonQuery("Insert into db.tbl1 values(...); Insert into db.tbl2 values(...);")
   
   
    End Sub
   

请记住正确清除用户的数据，以防止 SQL 注入(inject)攻击。最后我建议你研究一下Stored Procedures