Tuesday, June 30, 2015

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!