C# Mysql transaction in distinct classes and methods

Question

How do I interact a transaction between 2 methods that are in different classes?

In my method where I open the transaction is as follows:

public string InserirFuncionarioM(FuncionariosDTO Funcionario)
    {

        MySqlTransaction trans = null;

        try
        {
            connection.Open();

            trans = connection.BeginTransaction();

            MEnvolvido SalvarEnvolvido = new MEnvolvido();
            long codigo_envolvido = SalvarEnvolvido.InsereEnvolvido(Funcionario.envolvido);

            string query = "INSERT INTO tb_funcionario " +
                           "     (codigo_envolvidox, codigo_cargo, codigo_contrato, data_admissao, data_demissao, remuneracao, foto, estado_civil, naturalidade, nacionalidade) " +
                           "VALUES " +
                           "     (@codigo_envolvido, @codigo_cargo, @codigo_contrato, STR_TO_DATE(@data_admissao,'%m/%d/%Y 00:00:00'), STR_TO_DATE(@data_demissao,'%m/%d/%Y 00:00:00'), @remuneracao, @foto, @estado_civil, @naturalidade, @nacionalidade); ";

            MySqlCommand cmd = new MySqlCommand(query, connection);

            cmd.Parameters.Add("@codigo_envolvido", MySqlDbType.Int32).Value = codigo_envolvido;
            {...}
            cmd.Parameters.Add("@nacionalidade", MySqlDbType.VarChar).Value = Funcionario.nacionalidade;

            cmd.ExecuteNonQuery();

            trans.Commit();
            connection.Close();
            return "MSGs1";
        }
        catch
        {
            connection.Close();
            return "MSGa10";
        }
        finally
        {
            connection.Dispose();
            trans.Dispose();
        }
    }

See that I pass the information of my "involved" to another method that is in the class "Menvolvido", and in this class I insert the involved with the following method:

public long InsereEnvolvido(EnvolvidoDTO Envolvido)
    {
        string query = null;
        MySqlCommand cmd = null;
        long ultimo_id = 0;

        connection.Open();

        query = "INSERT INTO tb_envolvido " +
                "    (codigo_tipo_pessoa, cliente, fornecedor, transportador, nome_fantasia, razao_social, cpf_cnpj, rg_ie, im, sexo, data_nascimento, email, website, observacoes, usuario, senha) " +
                "VALUES " +
                "    (@codigo_tipo_pessoa, @cliente, @fornecedor, @transportador, @nome_fantasia, @razao_social, @cpf_cnpj, @rg_ie, @im, @sexo, STR_TO_DATE(@data_nascimento,'%m/%d/%Y 00:00:00'), @email, @website, @observacoes, @usuario, @senha);";

        cmd = new MySqlCommand(query, connection);

        cmd.Parameters.Add("@codigo_tipo_pessoa", MySqlDbType.Int32).Value = Envolvido.codigo_tipo_pessoa;
        {...}
        cmd.Parameters.Add("@senha", MySqlDbType.VarChar).Value = Envolvido.senha;

        cmd.ExecuteNonQuery();

        cmd = new MySqlCommand("SELECT last_insert_id()", connection);
        MySqlDataReader dataReader = cmd.ExecuteReader();
        if (dataReader != null && dataReader.Read())
        {
            ultimo_id = dataReader.GetInt64(0);
        }

        dataReader.Close();

        connection.Close();

        return ultimo_id;
    }

However the method that inserts the involved requires me to open a new Mysql connection, this makes me lose the transaction that was opened in the previous method. I would like to know the path of the stones to interact the 2 methods within a transaction, which are in different classes.

Answer 1

Ideally you would have a distinct class that would be responsible for controlling the transaction:

public class FuncionarioAplicacao
{
    public string InserirFuncionario(FuncionariosDTO funcionario)
    {
        MySqlTransaction trans = null;
        try
        {
            connection.Open();
            trans = connection.BeginTransaction();

            MEnvolvido meEnvolvido = new MEnvolvido(connection);//aqui você passa a connection como paramentro.
            long codigo_envolvido = meEnvolvido.InsereEnvolvido(funcionario.envolvido);

            MEFuncionario meFuncionario = new MEFuncionario(connection);//aqui você passa a connection como paramentro também.
            meFuncionario.InserirFuncionarioM(funcionario, codigo_envolvido);

            /*
             * ...
             */  
            trans.Commit();
        }
        catch
        {
            trans.RollBack();
            return "MSGa10";
        }
        finally
        {
            connection.Close();
            connection.Dispose();
            trans.Dispose();
        }
    }
}

In your existing classes, you have to modify to use the Connection passed in the constructor and did not close the connection within these methods, since the control class that would be responsible for this.