C # MySQL transaction in different classes and methods

Question

C # MySQL transaction in different classes and methods

Navigation

#1 by (1 votes)

3

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

In my method where I open the transaction it 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();
        }
    }

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 one 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, being that they are in different classes.

mysql c# .net ado.net

asked by anonymous 19.04.2015 / 14:54

1 answer

Drawable folders for different dpi do not appear in Android Studio How to index in google sites that use javascript? [duplicate]

score 1 · 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 connection in the constructor and do not close the connection within these methods, since the control class that would be responsible for this.