Ensure that a set of functions function correctly

Question

Hey there, guys! I’ve got a problem eating at me and I don’t think I can handle it on my own... Here’s the thing: I have a form with a button; that button is responsible for selling a product.

The sale is made in 3 steps: Drop in stock; Add product money to box; Add product name to the list of products purchased by customer;

It would look more or less like this generically:

Function DarBaixaNoEstoque()
    'código aqui'
End Function

Function AdicionarDinheiroAoCaixa()
    'código aqui'
End Function

Function AdicionarProdutoAoCliente()
    'código aqui'
End Function

That’s where the problem is, all the functions will store the data in a Mysql database and, I need all of them to be right for the sale to be carried out successfully. However, just imagine if the internet were to drop just as soon as the user is selling... I need a means of safety for all to work, it would be a very big problem if 1 or 2 functions did not work properly.

What options do I have? Thank you in advance!

Answer 1

To ensure atomicity (1), you must use SQL transactions.

The specific syntax depends on the SQL platform. Here’s an example in Mysql:

START TRANSACTION;

UPDATE Inventory SET quantity = quantity - 1 WHERE productId = 123;
UPDATE Stores SET cash = cash + 500 WHERE storeId = 456;
INSERT OwnedProducts(clientId, productId) values(789, 123);

-- todas as operações foram executadas com sucesso -> commit
COMMIT;

If any of the operations fail, the transaction will be automatically canceled with a ROLLBACK when the customer disconnects.

(1) This ensures that all 3 operations are indivisible. Either they all work, or none at all.

Resources:

• Transactions and Atomic Operations
• START TRANSACTION, COMMIT, and ROLLBACK Syntax

Answer 2

My suggestion is the adoption of the standard Work Unit (Unit of Work). Its definition is basically:

Maintains a list of objects affected by a transaction of business objects and coordinates the writing of changes and the resolution of competition problems.

I’m going to give you a simplified example of implementing this pattern here, I know it’s much more complex and extensive, but I think it will meet your needs. I will be using the repository pattern along as well, but I believe it is easily adaptable to other data access standards like DAL.

We start by defining an interface for the Work Unit. This is because access to different databases or different forms of data access (direct ADO.Net or ORM) would change the concrete implementation of the Transfer Unit.

Public Interface IUnidadeTrabalho
    Inherits IDisposable
    Sub Commit()

    Sub Rollback()

    Function CreateCommand() As IDbCommand
End Interface

Then, to instantiate the Work Unit, we use a Factory, in which we have already defined the connection and make it ready for use.

Public Class UnidadeTrabalhoFactory
    Public Shared Function Criar() As IUnidadeTrabalho
        Dim conexao As MySqlConnection = New MySqlConnection("MinhaConexao")
        conexao.Open()

        Return New UnidadeTrabalhoAdo(conexao, True)
    End Function
End Class

And here the concrete implementation of the Work Unit, using ADO.Net. Its function is quite simple: It takes the connection by parameter and opens a transaction. From there, the control of what will or will not be executed in the database depends on it, with the actions Commit or Rollback. She is also responsible for returning the DbCommand which will be executed at the bank, which will already be linked to the open transaction:

Public Class UnidadeTrabalhoAdo
    Implements IUnidadeTrabalho

    Private _conexao As IDbConnection
    Private _transacao As IDbTransaction

    Public Sub New(ByRef conexao As IDbConnection)
        Me._conexao = conexao
        Me._transacao = conexao.BeginTransaction()
    End Sub

    Public Function CreateCommand() As IDbCommand Implements IUnidadeTrabalho.CreateCommand
        Dim command As IDbCommand = _conexao.CreateCommand()
        command.Transaction = _transacao

        Return command
    End Function

    Public Sub Commit() Implements IUnidadeTrabalho.Commit
        If (IsNothing(_transacao)) Then
            Throw New InvalidOperationException("Não há transação aberta.")
        End If

        _transacao.Commit()
        _transacao = Nothing
    End Sub

    Public Sub Rollback() Implements IUnidadeTrabalho.Rollback
        If (IsNothing(_transacao)) Then
            Throw New InvalidOperationException("Não há transação aberta.")
        End If

        _transacao.Rollback()
        _transacao = Nothing
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        If (Not IsNothing(_transacao)) Then
            _transacao.Dispose()
            _transacao = Nothing
        End If

        If (Not IsNothing(_conexao)) Then
            Me._conexao.Close()
            Me._conexao = Nothing
        End If
    End Sub
End Class

Here I am suggesting the repository implementation for your business. The work unit is injected via the manufacturer and the seat control must be made from it.

Public Class EstoqueRepositorio
    Private _unidadeTrabalho As IUnidadeTrabalho

    Public Sub New(ByRef unidadeTrabalho As IUnidadeTrabalho)
        Me._unidadeTrabalho = unidadeTrabalho
    End Sub

    Function DarBaixaNoEstoque()
        Dim command As IDbCommand = Me._unidadeTrabalho.CreateCommand()
        command.CommandText = "INSERT INTO ..."

        REM código aqui
    End Function
End Class

Public Class CaixaRepositorio
    Private _unidadeTrabalho As IUnidadeTrabalho

    Public Sub New(ByRef unidadeTrabalho As IUnidadeTrabalho)
        Me._unidadeTrabalho = unidadeTrabalho
    End Sub

    Function AdicionarDinheiroAoCaixa()
        REM código aqui
    End Function
End Class

Public Class ClienteRepositorio
    Private _unidadeTrabalho As IUnidadeTrabalho

    Public Sub New(ByRef unidadeTrabalho As IUnidadeTrabalho)
        Me._unidadeTrabalho = unidadeTrabalho
    End Sub

    Function AdicionarProdutoAoCliente()
        REM código aqui
    End Function
End Class

And here, finally, where everything really happens. When calling the sell action, a new instance of the Work Unit should be created and injected into each of the repositories. While the method Commit of the Work Unit is not called, nothing will be sent to the bank. If there is an error, the block Catch will be the place to do the Rollback. Finally, it should be called the Dispose to release the connection.

Public Sub Vender()
    Dim unidadeTrabalho As IUnidadeTrabalho = UnidadeTrabalhoFactory.Criar()

    Try
        Dim repositorioEstoque = New EstoqueRepositorio(unidadeTrabalho)
        Dim repositorioCaixa = New CaixaRepositorio(unidadeTrabalho)
        Dim repositorioCliente = New ClienteRepositorio(unidadeTrabalho)

        repositorioEstoque.DarBaixaNoEstoque()
        repositorioCaixa.AdicionarDinheiroAoCaixa()
        repositorioCliente.AdicionarProdutoAoCliente()

        unidadeTrabalho.Commit()
    Catch ex As Exception
        unidadeTrabalho.Rollback()
    Finally
        unidadeTrabalho.Dispose()
    End Try
End Sub