How to ensure that all information was recorded at the bank?

Question

I’ve been realizing that I’m having trouble recording information in the bank because:

-I’m running a block of a method that records data in 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 method InsertData is a static class that manages 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 occurs that breaks the whole process (as described in the code comments), what should I do to not have information without logic within the database?

Answer 1

You will have to work with transactions, so that if nothing happens, do Commit changes and everything is stored consistently in the database, otherwise Rollback 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;
}