事务作用域未使用Async/Await回滚

Question

我在使用Async/Await时遇到了一个问题，无法使事务范围回滚。在没有事务作用域的情况下，一切都正常工作，但只要我有意造成异常（第二次迭代时插入的主键重复），就不会发生回滚（更新时）或任何与事务相关的错误。

• 我还应该注意，除非连接字符串中包含“OLE DB Services=4”，否则我将收到错误：“ITransactionLocal接口不受'Microsoft.ACE.OLEDB12.0'提供程序支持。本地事务不可用。”

下面的按钮事件处理程序中的代码只是测试事务范围的一个示例。主要目标是能够异步更新事务中包含的循环中的多个表，这样我就可以避免UI死锁，并对循环期间可能发生的任何异常执行回滚。对我的问题的任何替代方案或建议都表示感谢，谢谢：）

private async void button1_Click(object sender, EventArgs e)
        {
            try
            {
                int customerCount = 150;  // First 150 rows of customer table
                TransactionScope transaction = null;

                using (OleDbConnection dbConn = new OleDbConnection(Provider = Microsoft.ACE.OLEDB.12.0; OLE DB Services=-4; Data Source = " + filePath))
                {
                    dbConn.Open();
                    using (transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
                    {
                        for (int i = 0; i < customerCount; i++)
                        {

                            // Update field indicating customer made an invoice
                            var taskName = sql.executeAsync("UPDATE Customer SET lastInvoiceDate = @date WHERE customerID = @custID", dbConn,
                                new OleDbParameter("@date", DateTime.Today),
                                new OleDbParameter("@custID", i));

                            // Insert new invoice - Breaks here
                            var taskInsert = sql.executeAsync("INSERT INTO Invoice VALUES (1, 'thisisatestinvoice', '$100.50')", dbConn);


                            await Task.WhenAll(taskName, taskInsert);
                        }
                    }

                    // All updates executed properly
                    transaction.Complete();
                }
            }
            catch (AggregateException exception)
            {
                foreach (Exception ex in exception.InnerExceptions)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

public async Task executeAsync(string dbQuery, OleDbConnection dbConn, params OleDbParameter[] parameters)
        {
            var dbComm = new OleDbCommand(dbQuery, dbConn);

            if (parameters != null)
                dbComm.Parameters.AddRange(parameters);

            await dbComm.ExecuteNonQueryAsync().ConfigureAwait(false);
        }

Answer 1

我无法使事务作用域工作，我不完全确定是什么问题，我认为是由于我在ACE.OLEDB12.0上，但我找到了OleDbTransaction的另一个替代方案，如果发生任何故障，它将回滚。

private async void button1_Click(object sender, EventArgs e)
    {
        try
        { 
            using (OleDbConnection dbConn = new OleDbConnection(SQLWrapper.CONNECT_STRING))
            {
                dbConn.Open();
                OleDbTransaction dbTrans = dbConn.BeginTransaction();

                var taskName = sql.executeAsync("UPDATE Config SET Busname = @name", dbConn, dbTrans,
                     new OleDbParameter("@name", "name"));

                var taskInsert = sql.executeAsync("INSERT INTO Callout VALUES (16, 'ryanistesting')", dbConn, dbTrans);

                await Task.WhenAll(taskName, taskInsert);
                dbTrans.Commit();
            }
        }
    }

    public async Task executeAsync(string dbQuery, OleDbConnection dbConn, OleDbTransaction dbTrans, params OleDbParameter[] parameters)
    {
        using (var dbComm = new OleDbCommand(dbQuery, dbConn))
        {
            if (parameters != null)
                dbComm.Parameters.AddRange(parameters);

            if (dbTrans != null)
                dbComm.Transaction = dbTrans;

            await dbComm.ExecuteNonQueryAsync().ConfigureAwait(false);
        }
    }