3
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.
Right. In short, the transaction is the application, not the business code. Eventually the business code may warn you that it requires a transaction, using AOP, or have a code that guarantees the transaction abstracting the existence or not of a prior transaction. The connection should also be managed by the application, outside of the business code, which should receive the connection as a parameter or some other dependency injection technique. + 1
– Caffé