Execute DELETE only when returning SELECT

1

I have a routine where I update the local database with data from another database.

I simply run a DELETE and then INSERT INTO tblX (SELECT * FROM tblY (tblY is a linked table)) , as below.

The problem is that there is a time between DELETE and INSERT because SELECT takes a long time in some cases and I wanted to decrease to the maximum the possibility of the user requesting registration of a table that is in the middle of this processing.

I would like to know if there is any mechanism for me to

asked by anonymous 25.04.2017 / 00:24

3 answers

1

After testing the Ismael response options and getting the return: "Neither the isolation level nor the strengthening of it is supported", I believe because it is MS Access, I ended up creating a local temporary table to receive data from the network and then transfer to the final table, so the transfer between local tables occurs almost instantaneously.

    
27.04.2017 / 17:18
2

I suggest that you use Transactions to perform this command sequence. This way, you will ensure single-table access:

OleDbConnection.BeginTransaction Home Starts a transaction in the database with the possibility of specifying the type of transaction isolation.

Isolation Mode Home   - IsolationLevel.Serializable = Prevents other users update, or insert rows in the range is being changed until the transaction completes.

Code

using (OleDbConnection conn = new OleDbConnection(Conexao.getConexaoPainelGerencialLocal())) {
    OleDbCommand cmd = new OleDbCommand();
    OleDbTransaction transaction = null;

    cmd.Connection = conn;

    // Abre a conexão e inicia a transação
    try {
        conn.Open();

        // Inicia uma transação
        transaction = conn.BeginTransaction(IsolationLevel.Serializable);

        cmd.Connection = conn;
        cmd.Transaction = transaction;

        cmd.CommandText = "DELETE * FROM tblClienteContato";
        cmd.ExecuteNonQuery();

        //nesse meio tempo, NINGUÉM poderá efetuar qualquer operação na tabela tblClienteContato até que a transação seja finalizada

        cmd.CommandText = " INSERT INTO tblClienteContato " +
                          " SELECT * FROM tblClienteContatoVinculada;";

        cmd.ExecuteNonQuery();

        // Efetiva a transação
        transaction.Commit();
    }
    catch (Exception ex) {
        Console.WriteLine(ex.Message); //exibe no console o erro
        try {
            // Se der algum erro (no delete ou insert) desfaz as alterações
            transaction.Rollback();
        }
        catch {

        }
    }
    //Aqui a conexão já estará fechada 
}

Highly Recommended Reading

Transactions and Concurrency

    
25.04.2017 / 14:38
0

The command after the method "cmd.ExecuteNonQuery ();" will only run after the ExecuteNonQuery method get the database response, then you can use the "try / catch" is to do the rollback if you have any exceptions.

Try {
    conn = new OleDbConnection(Conexao.getConexaoPainelGerencialLocal());

    conn.Open();

OleDbCommand cmd = new OleDbCommand(" DELETE * FROM tblClienteContato; ", conn);

    cmd.ExecuteNonQuery();

    cmd = new OleDbCommand(" INSERT INTO tblClienteContato " +
                       " SELECT * FROM tblClienteContatoVinculada;", conn);

cmd.ExecuteNonQuery();

   // comando de delete aqui...
}
catch (SqlException odbcEx) {
   // erro na sql...
}
catch (Exception ex) {
   // erro no driver erro...
}
    
25.04.2017 / 01:27