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 it's like this:

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 from my "involved" to another method that is in the "MEInvolved" class, 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 forces me to open a new MySQL connection, this causes me to 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, as they are in different classes.

Answer:

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 it to use the connection passed in the constructor and not close the connection inside these methods, since the control class would be responsible for that.