如何进行交易?

问题描述

你好，

我创建了一个vb.net-mysql应用程序.其中有一个单独的连接类，例如:-

Hello,

I created a vb.net-mysql app. in which there is a separate connection class like this:-

Public Class mysql_con
    Public con As New MySqlConnection
Public Sub conn()
        str = "server=xxx;uid=xxx;pwd=xxx;database=xxx;"
        con = New MySqlConnection(str)
End Sub
Private Sub update(ByVal que As String)
        cmd = New MySqlCommand(que, con)
        con.Open()
        cmd.ExecuteNonQuery()
End Sub
End Class

然后在表单中，我这样调用更新函数:-

Then in forms i call the update function like this:-

Dim gc As New mysql_con

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        str = "insert into testing (id,name)VALUES(@id,@name)"
        Dim cmd As New MySqlCommand
        cmd.CommandText = str
        cmd.Parameters.AddWithValue("@id", TextBox1.Text)
        cmd.Parameters.AddWithValue("@name", TextBox2.Text)
        gc.conn()
        gc.update(cmd)
        str = "insert into testing2 (fname,sal)VALUES(@fname,@sal) where"
        cmd.CommandText = str
        cmd.Parameters.AddWithValue("@fname", TextBox1.Text)
        cmd.Parameters.AddWithValue("@sal", TextBox2.Text)
        gc.conn()
        gc.update(cmd)

    End Sub

现在，我需要在mysqltransaction中实现这两个插入.我该怎么办?

我已经像这样进行了，但是这不起作用.

Now I need to implement these two insert in 1 mysqltransaction. How can I do that?

I''ve proceeded like this, but this is not working..

Private Sub update(ByVal cmd As MySqlCommand)
        Dim mytransac As MySqlTransaction

        Try
            con.Open()
            mytransac = con.BeginTransaction
            cmd.Connection = con
            cmd.Transaction = mytransac
            cmd.ExecuteNonQuery()
            mytransac.Commit()
        Catch e As Exception
            Try
                mytransac.Rollback()
            Catch ex As Exception
                If Not mytransac.Connection Is Nothing Then
                    MsgBox("An exception of type " & ex.GetType().ToString() & _
                                      " was encountered while attempting to roll back the transaction.")
                End If
            End Try
            MsgBox("An exception of type " & e.GetType().ToString() & _
                      "was encountered while inserting the data.")
            MsgBox("Neither record was written to database.")
        Finally
            con.Close()
        End Try

    End Sub

在这种情况下，如果第二个插入中发生某些错误，那么它不会回滚第一个事务.这..我只能更改连接类...

Thanx

In this case if some error happens in the second insert then it''s not rollback the 1st transaction.. the problem is due to that i''m executing one one command at a time... but what can be the soln to this.. I can change only the connection class...

Thanx

推荐答案

mytransac = con.BeginTransaction
            cmd.Connection = con
            cmd.Transaction = mytransac
            cmd.ExecuteNonQuery()
            mytransac.Commit()

在开始两个插入操作之前和之后，您需要开始并提交事务.

如下图所示:

You need to begin and commit the transaction before and after you begin the two Inserts.

Something like one below:

Dim gc As New mysql_con

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim mytransac As MySqlTransaction
mytransac = con.BeginTransaction
Dim cmd As New MySqlCommand

     Try
        str = "insert into testing (id,name)VALUES(@id,@name)"
        cmd.CommandText = str
        cmd.Parameters.AddWithValue("@id", TextBox1.Text)
        cmd.Parameters.AddWithValue("@name", TextBox2.Text)

        gc.conn()
        gc.update(cmd)
        str = "insert into testing2 (fname,sal)VALUES(@fname,@sal) where"
        cmd.CommandText = str
        cmd.Parameters.AddWithValue("@fname", TextBox1.Text)
        cmd.Parameters.AddWithValue("@sal", TextBox2.Text)
        gc.conn()
        gc.update(cmd)
        mytransac.Commit()
        Catch
           mytransac.RollBack()
     End Try

这篇关于如何进行交易?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！