c# - 仅当 SELECT 返回时才执行 DELETE

Question

我有一个用其他数据库数据更新本地数据库的例程。

我只执行一个DELETE，然后执行一个INSERT INTO tblX (SELECT * FROM tblY (tblY is a linked table))，如下所示。

问题是，在某些情况下，SELECT 在 DELETE 之后需要很长时间，我想减少用户发出请求的可能性在处理过程中添加到此表。

我想知道是否有某种机制可以仅在 SELECT 返回后执行 DELETE。

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

听起来您需要做的是将这两个命令包装在一个事务中。

事务的妙处在于它要么是ALL WORKS，要么是ALL FAILS，这意味着如果有什么事情阻止了 select 语句，数据库将不会最终确定删除语句。

这看起来是一个很好的例子: https://msdn.microsoft.com/en-us/library/93ehy0z8(v=vs.110).aspx

请注意，它们只有一个命令对象，并替换 CommandText，而不是每次都创建一个新对象。这可能很重要。

尝试这样的事情:

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

try {

    conn.Open();
    transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted);

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



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

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

    cmd.ExecuteNonQuery();

    // The data isn't _finally_ completed until this happens
    transaction.Commit();


}
catch (Exception ex)
{
    // Something has gone wrong.
    // do whatever error messaging you do
    Console.WriteLine(ex.Message);
    try
    {
        // Attempt to roll back the transaction.
        // this means your records won't be deleted
        transaction.Rollback();
    }
    catch
    {
        // Do nothing here; transaction is not active.
    }
}