MySQL mysql事务（提交和回滚）

mysql transaction (commit and rollback)

提问by neerajMAX

below is the code i am using for MySqlTransaction and it is running perfectly.... but problem is as i am using single instance of mysqlcommand i have to use unique PARAMETER in it. which is ruining my query. is there any other way around to do....

下面是我用于 MySqlTransaction 的代码，它运行完美......但问题是我使用的是 mysqlcommand 的单个实例，我必须在其中使用唯一的 PARAMETER。这破坏了我的查询。有没有其他方法可以做....

i tried to dispose cmd after each query but of no use.. :(

我试图在每次查询后处理 cmd 但没有用.. :(

---

---

          con.Open()
            Dim sqlTran As MySqlTransaction = con.BeginTransaction()
            Dim cmd As MySqlCommand = con.CreateCommand()
            cmd.Transaction = sqlTran
            Dim str As String = Nothing
            Try
                cmd.CommandText = "SELECT myid FROM memaster where    Adate=@adate and ANo=@ano and ASource=@asrc"
                cmd.Parameters.AddWithValue("@adate", txt_bpass_adate.Text)
                cmd.Parameters.AddWithValue("@ano", txt_bpass_af.Text)
                cmd.Parameters.AddWithValue("@asrc", txt_bpass_asource.Text)
                str = cmd.ExecuteScalar()
                'cmd.Dispose()'
                If str Is Nothing Then

                    cmd.CommandText = "Insert into memaster (ADate,ANo,ASource) values (@aDate,@aNo,@aSRC)"
                    cmd.Parameters.AddWithValue("@aDate", txt_bpass_adate.Text)
                    cmd.Parameters.AddWithValue("@aNo", txt_bpass_af.Text)
                    cmd.Parameters.AddWithValue("@aSRC", txt_bpass_asource.Text)

     cmd.ExecuteNonQuery()
    End If
     sqlTran.Commit()

            Catch ex As Exception
                Try
                    sqlTran.Rollback()
                Catch ex1 As Exception

                End Try
            End Try

---

---

i actually want to fire more then 4 queries in single transaction so that if anything go wrong i can rollback it...

我实际上想在单个事务中触发 4 个以上的查询，以便如果出现任何问题，我可以回滚它...

if any onebody have any other method of it kindly share the concept wid me...

如果有人有任何其他方法，请与我分享这个概念......

---

---

     For index As Integer = 1 To 5
                    cmd.CommandText = "Insert into detail (ID,BNos,SNo) values (@FID1,@BNo,@SeqN1)"
                    cmd.Parameters.AddWithValue("@FID1", str)
                    cmd.Parameters.AddWithValue("@BNo", str1)
                    cmd.Parameters.AddWithValue("@SeqN1", txt_bpass_sqn1.Text)
                    cmd.ExecuteNonQuery()


                Next

回答by C.Evenhuis

To execute multiple commands within the same transaction, ensure that you assign the transaction object to each command individually:

要在同一个事务中执行多个命令，请确保将事务对象分别分配给每个命令：

Dim selectCmd As MySqlCommand = con.CreateCommand()
Dim insertCmd As MySqlCommand = con.CreateCommand()

selectCmd.CommandText = "SELECT ..."
insertCmd.CommandText = "INSERT ..."

Dim sqlTran As MySqlTransaction = con.BeginTransaction()
Try
  selectCmd.Transaction = sqlTran
  insertCmd.Transaction = sqlTran

  ...selectCmd.ExecuteScalar()...
  ...insertCmd.ExecuteNonQuery()...

  sqlTran.Commit()
Catch
  sqlTran.Rollback()
End Try

As others have mentioned, it is generally a good idea to Dispose()objects (that are IDisposable) as soon as you're done working with them. After disposing objects, they can no longer be used.

正如其他人所提到的，一旦您完成对Dispose()对象的处理，IDisposable就立即处理它们通常是一个好主意。处置对象后，它们将无法再使用。

回答by Surendra Chauhan

You can use Using keyword for auto dispose objects. i don't know VB but I know C#. Please convert the code into VB.

您可以将 Using 关键字用于自动处置对象。我不知道 VB，但我知道 C#。请把代码转换成VB。

using(MySqlConnection con= new MySqlConnection("connectionString"))
{
    con.Open();
    using(MysqlTransaction trans=con.BeginTransaction())
    {
        try
        {
            //command to executive query
            using(MysqlCommand cmd= new MySqlCommand("query", con, trans))
            {
                cmd.Parameters.AddWithValue("@parameter1", parametervalue1);
                cmd.Parameters.AddWithValue("@parameter2", parametervalue2);
                cmd.ExecutenonQuery();
                cmd.Parameters.Clear();
            }
            //command to execute query
            using(MysqlCommand cmd= new MySqlCommand("query", con, trans))
            {
                cmd.Parameters.AddWithValue("@parameter1", parametervalue1);
                cmd.Parameters.AddWithValue("@parameter2", parametervalue2);
                cmd.ExecutenonQuery();
                cmd.Parameters.Clear();
            }
            //command to execute query
            using(MysqlCommand cmd= new MySqlCommand("query", con, trans))
            {
                cmd.Parameters.AddWithValue("@parameter1", parametervalue1);
                cmd.Parameters.AddWithValue("@parameter2", parametervalue2);
                cmd.ExecutenonQuery();
                cmd.Parameters.Clear();
            }
            trans.Commit();
        }
        catch(Exception ex)
        {
            trans.Rollback();
        }
    }
}