DataAccess - Revisiting Yet Again

Almost 6 years ago, I wrote a 3-part series of blog posts about DataAccess. You can find them here: Part I, Part II and Part III.  These posts are still relevant, even though they are old. But, I’ve been meaning to add two things to the BBDataAccess class that I posted in Part III and I just keep forgetting to do it. Well, today is the day …

Implementing IDisposable

First, I wanted to make the class implement IDisposable. Here’s the addition that needs to be added to the BBDataAccess class:

public class BBDataAccess : IDisposable
{
    // declarations & methods from Part III's previously published example
    // ...
    // ...
 
    #region IDisposable Members
 
    public void Dispose()
    {
        if (this.oConnection != null)
        {
            if (this.oConnection.State != ConnectionState.Closed)
                this.oConnection.Close();
            this.oConnection.Dispose();
        }
    }
 
    #endregion
}

When calling methods on this class before we implemented IDisposable, you would have done something like this:

// Without IDisposable
CustomerDataAccess da = new CustomerDataAccess();
CustomerDataSet ds = da.GetCustomer(1);
// rest of your code

Now that you’ve implemented IDisposable, you’d call it like this:

// When the base BBDataAccess class implements IDisposable
using (CustomerDataAccess da = new CustomerDataAccess())
{
    CustomerDataSet ds = da.GetCustomer(1);
    // rest of your code
}

Transactions via TransactionScope

I also wanted to introduce Transactions using TransactionScope. You can make many things transactional, not just database access, so TransactionScope can be quite useful. Before we begin, take a look at my blog post about TransactionScope.

First, let’s just show you an example of how you might use TransactionScope in your calling code:

// The Utils class is from my TransactionScope blog post ...
// you *did* read it already, didn't you?  ;0)
bool IsOK = false;
using (TransactionScope scope = Utils.GetTransactionScope())
{
    using (CustomerDataAccess da = new CustomerDataAccess())
    {
        CustomerDataSet ds = da.GetCustomer(1);
        
        // Do some stuff with the CustomerDataSet.
        // Then call a method to create an Order.
        // And then call another method that creates an Invoice.
        // Then let's get the changes and save everything.
 
        CustomerDataSet dsChanged = (CustomerDataSet)ds.GetChanges();
        CustomerDataSet dsDeleted = (CustomerDataSet)ds.GetChanges(DataRowState.Deleted);
 
        IsOK = da.SaveCustomerOrder(dsChanged, dsDeleted);
        if (IsOK)
            IsOK = da.SaveCustomerInvoice(dsChanged, dsDeleted);
 
        if (IsOK)
            scope.Complete();
    }
}

We could also add TransactionScope to the CustomerDataAccess and/or to the base BBDataAccess class from Part III. If we did that, you would end up with nested Transactions, each one enlisting in the previous Transaction. This is usually a good thing.

For the CustomerDataAccess class, you could add the TransactionScope in each method like this:

public bool SaveCustomerOrder(CustomerDataSet dsChanged, CustomerDataSet dsDeleted)
{
    this.DoIt = delegate()
    {
        // To use TransactionScope be sure to add the System.Transactions namespace
        // If there is already a current ("ambient") TransactionScope, this one
        // will enlist in the same transaction
        using (TransactionScope scope = Utils.GetTransactionScope())
        {
            this.SaveTable(dsChanged.Customer, "csp_CustomerPut");
            this.SaveTable(dsChanged.Orders, "csp_OrderPut");
 
            if (dsDeleted != null)
            {
                this.SaveTable(dsDeleted.Orders, "csp_OrderDelete");
                this.SaveTable(dsDeleted.Customer, "csp_CustomerDelete");
            }
 
            scope.Complete();
        }
    };
    this.UndoIt = delegate()
    {
        dsChanged.RejectChanges();
        dsDeleted.RejectChanges();
    };
 
    return this.Start();
}

You could also utilize TransactionScope in the Start method of the base class, like this:

public virtual bool Start()
{
    bool success = false;
 
    // Enter the retry loop
    while (!success)
    {
        try
        {
            // Start the transaction and execute the methods. Any failures from here
            // to the Complete() may result in an exception being thrown and caught which
            // may lead to a retry or an abort depending on the type of error
            using (TransactionScope scope = Utils.GetTransactionScope())
            {
                this.m_DoIt();

                success = true;
                scope.Complete();
            }
        }
        catch (Exception e)
        {
            // Typically we'll want a Retry if the database error was the
            // result of a deadlock, but you can Retry for any reason you wish.
            if (this.RetryRequested(e) && this.attempt < this.maxRetries)
            {
                // Try to undo any changes before we retry
                // If we can't, just abort
                if (!this.UndoChanges())
                {
                    this.AddToErrorMessage(e);
                    this.ForceCleanup();
                    break;
                }

                this.attempt++;

                // Might want to sleep for random period
                //System.Random rand = new System.Random();
                //System.Threading.Thread.Sleep(rand.Next(MinSleepTime, MaxSleepTime));
            }
            else
            {
                // Max number of retries exceeded or an unrecoverable error 
                // - fail and return error message
                this.AddToErrorMessage(e);
                this.ForceCleanup();
                break;
            }
        }
    }

    return (success);
}

That’s it for now. I hope this all makes sense to you, dear Reader. If not, please don’t hesitate to ask questions in the comments!