Rollback transaction does not work as expected

Question

I’ve recently been implementing a sequence of requests in Visual Basics, and would like to give a rollback when any fail. So I did it this way:

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 this one. The other classes have other methods, 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 make a mistake I undo everything:

I suppose a method at some other code location

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 of Close(), the method Rollback() of the DAO class, even when it is successful.

And by doing the test I don’t succeed at Rollback. Does anyone know how to shed any light on why?

Answer 1

This missing to give the Commit in the Inserts function after the line:

command.ExecuteNonQuery

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