Perform transactional database operations asynchronously in .NET 4.5.1

Question

I have written a program to perform database operations in asynchronous fashion. I try to run it in a database transaction using BeginTransaction() or TransactionScope but have no luck. I stepped through the code in VS2012 and noticed the execution would jump out the foreach loop when it sees the await for ExecuteNonQueryAsync.

public delegate void CallBackMethod();

public async Task MyFunction(List<string> items, CallBackMethod functionToCallBeforeCommitTransaction)
{   
    using (TransactionScope trans = new TransactionScope(
                                               TransactionScopeOption.RequiresNew, 
                                               TransactionScopeAsyncFlowOption.Enabled ))
    {
        using (SqlConnection conn = new SqlConnection(myConnectionString))
        {
            await conn.OpenAsync();
            foreach (string item in items)
            {
                SqlCommand cmd = new SqlCommand("MySproc", conn);
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ItemID", item);
                await cmd.ExecuteNonQueryAsync();  
                // ^^^ Execution jumps out here and returns back to the calling function. 
                //     No exception was raised.  It did not continue the loop.
            }
        }
        functionToCallBeforeCommitTransaction();
        trans.Complete();
    }
}

Edited: Includes method header/signature.

Could you show the method signature... – i3arnon Feb 28 '14 at 07:55 — i3arnon, Feb 28 '14 at 07:55
How did you call `MyFunction`? Did you `await` it? – John Saunders Feb 28 '14 at 09:02 — John Saunders, Feb 28 '14 at 09:02
What behavior do you see? Exception? Deadlock? – Stephen Cleary Feb 28 '14 at 11:31 — Stephen Cleary, Feb 28 '14 at 11:31

Tony · Answer 1 · 2014-02-28T22:48:22.693

0

Thanks John! You got it right!!

I forgot to use await or .Wait() in the caller. I was so focus into the asynchronous support issue for transaction prior to .NET 4.5.1

await MyFunction();

or

MyFunction().Wait();

By the way, .NET 4.5.1 Developer Pack (KB2878632) needs to be installed so you can change the project target from .NET Framework 4.0 to 4.5.1

edited Feb 28 '14 at 22:48

answered Feb 28 '14 at 18:12

Tony

1,827
1
22
23

1

Depending on your environment (your blocking function is running on the single thread the `SyncroizationContext` will marshal it's calls to) doing `MyFunction().Wait()` will cause your code to deadlock. – Scott Chamberlain May 01 '14 at 19:22

Perform transactional database operations asynchronously in .NET 4.5.1

1 Answers1