Run DELETE only when you return SELECT

Question

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

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

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

I’d like to know if there’s any mechanism for me to perform the DELETE only when the SELECT return.

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();

Answer 1

I suggest you use Transactions to perform this sequence of commands. This way you will ensure a unique access to table:

OleDbConnection.BeginTransaction
Starts a transaction in the database with the possibility to specify the transaction isolation type.

Isolation Mode
- Isolationlevel.Serializable = Prevents other users from updating or entering lines in the range of being changed until the transaction is completed.

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
Error message "Exceeding share lock count..." while processing a large transaction

Transactions and simultaneity

Answer 2

After testing Ismael’s response options and getting the return: "Neither the Isolation level nor a Strengthening of it is supported", I believe it is MS Access, I ended up creating a local temporary table to receive the data from the network and then transfer to the definitive table, thus, the transfer between local tables occurs almost instantaneously.

Answer 3

The command after the "cmd.Executenonquery();" method will only be executed after the Executenonquery method gets the response from the database, so you can use "Try/catch" to do the rollback if you have an exception.

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...
}