Rollback transaction does not work as expected

2

I've recently been implementing a request sequence in Visual Basics, and would like to rollback when some fail. So I did the following:

Public Class DAO
    Dim connection As New MysqlConnection
    Dim command As New MySqlCommand
    Dim dr As MySqlDataReader
    Dim transaction As MySqlTransaction

    Public Sub New()
        connection = New MySqlConnection('MYDATA_FROM_DATABASE')
        connection.Open()
        transaction = connection.BeginTransaction
        command.Connection = connection
        command.Transaction = transaction
    End Sub

    Public Sub Rollback()
        Try
            transaction.Rollback()
            connection.Close()
        Catch
        EndTry
    End Sub

    Public Sub Close()
        Try
            transaction.Commit()
            connection.Close()
        Catch
        EndTry
    End Sub
End Class

And with that I create other DAOS classes, inheriting from that. The other classes have other methods, for example:

Public Class UsuarioDAO
    Inherits DAO

    Public Function Insere(nome As String) As Boolean
        Try
            command.commandText = "INSERT INTO Usuario(Nome) VALUES('" & nome & "')"
            command.ExecuteNonQuery
            Return True
        Catch
            Return False
        End Try
    End Function 
End Class

And, for example, I want to insert 3 users, and if I give the error I undo everything:

Suppose a method in any other location of the code

Public Sub Insere3Usuarios
    Dim UsuarioDAO As New UsuarioDAO
    If UsuarioDAO.Insere("USUARIO_1") And UsuarioDAO.Insere("USUARIO_2") And UsuarioDAO.Insere("USUARIO_3") Then
        UsuarioDAO.Close()
    Else
        UsuarioDAO.Rollback()
    End If
End Sub

After calling this method Insere3Usuarios , I can force the rollback, calling instead Close() , the Rollback() method of the DAO class, even when it succeeds.

And taking the test I'm not successful in Rollback. Can anyone give me a glimpse of why?

    
asked by anonymous 18.01.2017 / 19:55

1 answer

1

This is missing giving the Commit in function Inserts after the line:

command.ExecuteNonQuery

Because without the commit, there is nothing to "Reverse" with Rollback.

    
19.05.2017 / 20:25