Ensure that a set of functions work correctly

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 client disconnects.

(1) This ensures that the 3 operations are indivisible. Either they all work, or it does not work at all.

Resources:

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

My suggestion is to adopt the Unit of Work pattern ( Unit of Work ) . Its definition is basically:

  

Maintains a list of objects affected by a business object transaction and coordinates writing changes and resolving competition issues.

I'll give you a simplified example of implementing this pattern here, I know it's a lot more complex and extensive, but I think it will suit your needs. I will be using the repository pattern together, but I believe it is easily adaptable to other data access patterns like DAL.

We started by defining an interface for the Work Unit. This is because access to different databases or different forms of data access (ADO.Net direct or ORM) would change the concrete implementation of the Handle 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 leave 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 is the concrete implementation of the Unit of Work, using ADO.Net. Its function is very simple: It receives 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 . It is also responsible for returning the DbCommand that will be executed in the bank, which will already be connected 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 constructor and the bank command 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 must be created and injected into each of the repositories. While the Commit method of the Work Unit is not called, nothing will be sent to the bank. If there is an error, Catch will be the place to Rollback . Finally, it should be called 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