How to ensure that all information has been recorded in the bank?

2

I've come to realize that I'm having trouble writing information to the bank because:

-I am running a block of a method that writes data to the database:

//codigo aqui
MySQLCallDB.InsertData("PEDIDOS", columns, values); //gravando informações
//codigo quebra por alguma Exception aqui ou seja, terei um Pedido sem Detalhes
MySQLCallDB.InsertData("PEDIDOS_DETALHES", columns, values); //gravando informações
//Restante do código
The InsertData method is of a static class that manages the queries in my application ( MySQLCallDB ) and the code and the way I call MYSQL in InsertData is:

public static string InsertData(string table, List < string > columns, List < string > values) {
    string Query = "QUERY";
    Query += "SELECT LAST_INSERT_ID() as ID;";
    MySqlCommand comm = new MySqlCommand("", conexao);
    comm.CommandText = Query;
    try {
        conexao.Open();
        MySqlDataReader reader = comm.ExecuteReader();
        reader.Read();
        return reader.GetString("ID");
    } 
    catch (Exception e) {
        Debug.WriteLine(Query);
        Debug.WriteLine(e);
        return "Error";
    } 
    finally {
      conexao.Close(); //Fechando a conexão}}
    }
}

The question is: in cases where an Exception that breaks the whole process (as described in the code comments), what should I do to not have information without logic inside the bank?

    
asked by anonymous 08.08.2018 / 20:11

1 answer

1

You will have to work with transactions, so that if nothing happens, make% changes to the changes and everything will be recorded in a coherent way in the database, and otherwise do Commit so that nothing is recorded so as not to cause inconsistencies.

// aqui pressupomos que a variável "conexao" está acessível
void GravaDadosBD()
{
    MySqlTransaction myTrans = null;

    try
    {
        conexao.Open();
        myTrans = conexao.BeginTransaction();

        MySQLCallDB.InsertData("PEDIDOS", columns, values);
        MySQLCallDB.InsertData("PEDIDOS_DETALHES", columns, values);

        // correu tudo bem, sem erros, fazer Commit()
        myTrans.Commit();
    }
    catch(Exception ex)
    {
        myTrans?.Rollback();
    }
    finally
    {
        // fechar a conexão apenas depois de executar tudo
        conexao?.Close();
    }
}

public static string InsertData(string table, List <string> columns, List <string> values) 
{
    string Query = "QUERY SELECT LAST_INSERT_ID() as ID;";
    string strResult = "Error";
    MySqlCommand comm = new MySqlCommand(Query, conexao);

    try 
    {
        MySqlDataReader reader = comm.ExecuteReader();

        reader.Read();
        strResult = reader.GetString("ID");

        reader.Close();
    }
    catch (Exception e) 
    {
        Debug.WriteLine(Query);
        Debug.WriteLine(e);
    }

    return strResult;
}
    
09.08.2018 / 11:13