.NET BackgroundWorker and SQLTransactions

Question

Hi, Where can I find information or how can I handle SQL Server transactions in a BackgroundWorker thread? It's my understanding that error handling should not be set in the "DoWork" event and that the error is handled internally and passed to the 'RunWorkerCompleted".

I am currently using SubSonic as my DAL and passing some lengthy inserts and updates via a BackGroundWorder thread. The problem I have is that when something fails nothing is rolledback. I was deciding on using a transactionscope but couldn't find information on using transactions with a BackgroundWorker thread.

Answer 1

Transaction with TransactionScope are pretty straightforward.

1. Create a new TransactionScope (prefferable within a using block)

2. Create a new Sql Connection (if you do it the other way round it won't work)

3. Run some crud operations

4. Complete the transaction

5. ???

6. PROFIT!!!

In the terms of subsonic this is what you have to do:

using (var ts = new TransactionScope())
using (new SubSonic.SharedDbConnectionScope())
{

    DoSomethingWithYourData();

    ts.Complete();
}

That's what happens in the background:

If you create a new TransactionScope, the static property Transaction.Current is set to your transaction. Now, if you create a new DbConnection, the connections itselfs looks if Transaction.Current is not null and suscribes to the TransactionCompleted event.

If you call ts.Complete() before the Connection is disposed, the transaction is commited otherwise it will be rolled back an an exception is throw in the TransactionScopes Dispose() method.

SubSonic itself will create an new Connection for every statement you execute (no bug, it's by desing) but not practical for Transactions. This is the reason why the SharedDbConnectionScope() exists. It works similar to the TransactionScope (if a new AutomaticConnectionScope detects that there is a current SharedDbConnectionScope() it's connection will be used. Otherwise a new connection will be created.

By the way, the using syntax is nothing else than this:

var obj = new ObjectThatImplementsIDisposable();
try
{

}
finally
{
    obj.Dispose();
}

Long story short: It does not affect your transaction if you run it in a thread or BackgroundWorker. However, you should keep in mind that, if you use a SharedDbConnecionScope() even queries from other threads will use them an I don't think the SqlClient library is thread safe (but I can be wrong, but MySqlClient definitely isn't.

Conculstion: I would switch to the TransactionScope because it is easy to use, flexible, generic (and if you decide one day to switch to mysql or oracle with your app, you don't have to worry)

Reading you first part of the question: - I think it is a bad desing to let the BackGroundWorker's DoWork method throw an Exception. But you can use this approach:

private void worker_DoWork(object sender, DoWorkEventArgs e)
{
    try
    {
         ExecuteATransaction()
    }
    catch (Exception ex)
    {
         RollBackTransaction();
         e.Result = ex;
    }
}

private void worker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    if (e.Result && e.Result.GetType() == typeof(Exception))
       throw ((Exception)e.Result);
    else
       Console.WriteLine("Everything went better than expected ;-)");
}