DataAccess - Part III

If you’ve already read my previous posts about DataAccess (Part I and Part II), you’ll notice that I said nothing about writing this in 3 parts. However, I realized that I left out another, better methodology for writing DataAccess classes. So, I thought I better address that pronto.

This better methodology involves classes using anonymous delegates. These classes can live directly in your DataAccess base class, or you can put them in your Business base class (so that your Business classes can effectively manage multiple “transactions” involving multiple DataAccess classses, rather than having it all be done at the DataAccess layer). To simplify matters, I will show this methodology only in the DataAccess base class and leave it as an exercise to the reader to extend the concepts to the Business layer.

What follows is a very simplified, but workable, version … I’ve left out some of the “nice-to-have” features that don’t affect the basic usability of the class (such as timeouts & transactions). Some of my method names might seem a bit “crude”, but I didn’t want it to resemble actual code in use. You know, copyright and all that. The point is to get the concepts across and let you expand on those concepts on your own.

So, first let’s look at the few key points. Here are the basic additions to the DataAccess class I showed in Part II that make this whole thing work:

public delegate void TheMethod();
private TheMethod m_DoIt;
private TheMethod m_UndoIt;
public TheMethod DoIt
{ 
    set { m_DoIt = value; } 
}
public TheMethod UndoIt
{
    set { m_UndoIt = value; }
}
public virtual bool Start()
{
    bool success = false;
    // Enter the retry loop
    while (!success)
    {
        try
        {
            // Commented out because I haven't included transactions in my example
            //this.BeginTransaction();
            this.m_DoIt();
            // Here is where you'd commit your database transactions
            //this.CommitTransaction();
            success = true;
        }
        catch (Exception e)
        {
            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);
}
protected bool UndoChanges()
{
    try
    {
        if (this.m_UndoIt != null)
            this.m_UndoIt();
    }
    catch (Exception e)
    {
        this.AddToErrorMessage(e);
        return false;
    }
    return true;
}

I’ll explain how it works in a minute, but first let’s see the code you would use in your sub-classes to utilize the delegates:

public CustomerDataSet GetCustomer(long customerkey)
{
    CustomerDataSet ds = null;
    this.DoIt = delegate()
    {
        ds = new CustomerDataSet();
        this.ClearParameters();
        this.AddParms("CustomerKey", customerkey); 
        this.FillData(ds, "csp_CustomerGet");
    };
    this.Start();
    return ds;
}
public bool SaveCustomer(CustomerDataSet dsChanged, CustomerDataSet dsDeleted)
{
    this.DoIt = delegate()
    {
        // you'd also want to wrap all this in a transaction, I just haven't
        // shown transactions in this example
        this.SaveTable(dsChanged.Customer, "csp_CustomersPut");
        this.SaveTable(dsChanged.Orders, "csp_OrdersPut");
        if (dsDeleted != null)
        {
            this.SaveTable(dsDeleted.Orders, "csp_OrdersDelete");
            this.SaveTable(dsDeleted.Customer, "csp_CustomerDelete");
        }
    };
    this.UndoIt = delegate()
    {
        dsChanged.RejectChanges();
        dsDeleted.RejectChanges();
    };
    return this.Start();
}

Now, I obviously have not shown all the methods in the class (I’ll post the entire simplified version of the class at the end of this post). But, let me explain how this works. Basically, you set up two anonymous delegates, DoIt and UndoIt, then run the Start() method which in turn runs the code defined in the delegates. The beauty of it all is that the Start() method contains the only try/catch stuff you need and therefore handles any and all exceptions. It completely does away with the necessity of writing try/catch blocks in all your DataAccess methods, and having to have the developer remember to check return values and or ErrorMessages from every method call.

In the Start() method, the DoIt delegate code runs first. If there are any exceptions thrown, it checks to see if we want to Retry the operation. If so, first we run the UndoIt delegate code, if any exists, and then run through the loop and try again. Note that the GetCustomer() method doesn’t have any UndoIt code, whereas the SaveCustomer() method does. This is because the dsChanged or the dsDeleted DataSets could possibly be changed in the process of attemping to Save, and thus we want to put them back to their original state before attempting the Save again. This isn’t an issue in the GetCustomer() method.

Now, as I mentioned, this greatly simplifies the original DataAccess class that I previously posted (no more try/catch stuff needed), so I’ll post the entire class as it now stands:

public class BBDataAccess
{
    #region Declarations associated with the new delegate approach
    public delegate void TheMethod();
    private TheMethod m_DoIt;
    private TheMethod m_UndoIt;
    public TheMethod DoIt
    { 
        set { m_DoIt = value; } 
    }
    public TheMethod UndoIt
    {
        set { m_UndoIt = value; }
    }
    // you can make these into properties, or make them protected
    // so the defaults can be changed
    private int maxRetries = 3;
    private int attempt;
    #endregion
    #region Declarations from the original DataAccess class
    private IDbConnection oConnection;
    private IDbCommand oCommand;
    private IDbDataAdapter oAdapter;
    private IDataParameterCollection oParms;
    public string ConnectionString = "";
    public string ErrorMessage = "";
    // Tweak, if necessary, for other database servers (Oracle)
    private string AtSign = "@";
    private static int DeadlockRollbackError = 1205; 
    #endregion
    #region Constructor/Destructor
    public BBDataAccess()
    {
        BBConnection o = new BBConnection();
        this.oConnection = o.GetConnection();
        this.ConnectionString = o.ConnectionString;
        this.oCommand = this.GetIDbCommand();
        this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
        this.oCommand.Connection = this.oConnection;
        this.oCommand.CommandType = CommandType.StoredProcedure;
    }
    #endregion
    // Tweak, if necessary, for other database servers (Oracle)
    private string AtSign = "@";
    private static int DeadlockRollbackError = 1205; 
    #endregion
    #region Constructor/Destructor
    public BBDataAccess()
    {
        BBConnection o = new BBConnection();
        this.oConnection = o.GetConnection();
        this.ConnectionString = o.ConnectionString;
        this.oCommand = this.GetIDbCommand();
        this.oAdapter = this.GetIDbDataAdapter(this.oCommand);
        this.oCommand.Connection = this.oConnection;
        this.oCommand.CommandType = CommandType.StoredProcedure;
    }
    #endregion
    #region Methods associated with the new delegate approach
    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 final commit may result in an exception being thrown and caught which
                // may lead to a retry or an abort depending on the type of error
                // Here is where you would start your database transactions
                // It's commented out because I haven't included it in my example
                //this.BeginTransaction();
                this.m_DoIt();
                // Here is where you'd commit your database transactions
                //this.CommitTransaction();
                success = true;
            }
            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);
    }
    protected bool RetryRequested(Exception e)
    {
        bool retryRequested = false;
        if (e is BBRetryException) retryRequested = true;
        while (e.InnerException != null && !retryRequested)
        {
            e = e.InnerException;
            if (e is BBRetryException) retryRequested = true;
        }
        return retryRequested;
    }
    /// <summary>
    /// Try to undo any changes to the operation parameters so that we can retry 
    /// If the undo fails, the operation is aborted instead
    /// </summary>
    /// <returns></returns>
    protected bool UndoChanges()
    {
        try
        {
            if (this.m_UndoIt != null)
                this.m_UndoIt();
        }
        catch (Exception e)
        {
            this.AddToErrorMessage(e);
            return false;
        }
        return true;
    }
    // You can handle error message differently if you wish
    // For this example, I simply set up a public ErrorMessage string
    // that will be an empty string if there were no errors.
    protected void AddToErrorMessage(Exception e)
    {
        this.AddToErrorMessage(e.Message);
        if (e.InnerException != null)
            this.AddToErrorMessage(e.InnerException.Message);
    }
    protected void AddToErrorMessage(string msg)
    {
        this.ErrorMessage += msg + "\r\n";
    }
    /// <summary>
    /// Force abort the transaction and cleanup the database connection without raising exceptions
    /// Any problems are still logged to the error messages list. Used when cleaning up during
    /// exception processing where we already know there may be some problem with the database
    /// connection
    /// </summary>
    protected void ForceCleanup()
    {
        try
        {
            // commented because I haven't included Transactions in my example
            //this.RollbackTransaction();
        }
        catch (Exception e)
        {
            this.AddToErrorMessage(e);
        }
        try
        {
            this.CloseConnection();
        }
        catch (Exception e)
        {
            this.AddToErrorMessage(e);
        }
    }
    #endregion
    #region Get/Use IDb Interface Objects
    protected IDbCommand GetIDbCommand()
    {
        return new SqlCommand();
    }
    protected IDbDataAdapter GetIDbDataAdapter(IDbCommand command)
    {
        return new SqlDataAdapter((SqlCommand)command);
    }
    protected IDbDataParameter GetIDbDataParameter(string ParmName, object ParmValue)
    {
        return new SqlParameter(ParmName, ParmValue);
    }
    protected void DeriveParameters(IDbCommand command)
    {
        SqlCommandBuilder.DeriveParameters((SqlCommand)command);
    }
    #endregion
    #region Parameter Methods
    
    // Then there are the various protected methods for adding and setting Parameters, filling a DataSet, etc.    
    // It's these various methods that get used in the classes that are sub-classed from this "base" class.    
    // Here's just a few of them:    
    protected void ClearParameters()
    {
        this.oCommand.Parameters.Clear();
        this.ErrorMessage = "";
    }
    protected void AddParms(string ParmName, object ParmValue)
    {
        if (ParmName.StartsWith(this.AtSign) == false)
            ParmName = this.AtSign + ParmName;
        if (ParmValue != DBNull.Value)
            this.oCommand.Parameters.Add(this.GetIDbDataParameter(ParmName, ParmValue));
    }
    protected void AddParms(string ParmName, object ParmValue, ParameterDirection direction)
    {
        this.AddParms(ParmName, ParmValue);
        this.SetParmDirection(ParmName, direction);
    }
    protected void SetParmDirection(string ParmName, ParameterDirection direction)
    {
        if (ParmName.StartsWith(this.AtSign) == false)
            ParmName = this.AtSign + ParmName;
        if (this.oCommand.Parameters.IndexOf(ParmName) > -1)
            ((IDbDataParameter)this.oCommand.Parameters[ParmName]).Direction = direction;
    }
    protected void SetAllParameters(DataRow Row)
    {
        this.ClearParameters();
        for (int i = 0; i < Row.Table.Columns.Count; i++)
        {
            this.AddParms(Row.Table.Columns[i].ColumnName, Row[i]);
        }
    }
    #endregion
    #region Database Methods, using the IDb interface members
    protected void FillData(DataSet ds, string StoredProcName)
    {
        this.oCommand.CommandText = StoredProcName;
        this.oAdapter.Fill(ds);
    }
    /// <summary>
    /// In order to reduce programmer error and to mimic the native functionality of the
    /// DataAdapter.Fill() command, we will open a connection if it has not already 
    /// been opened, Execute the Command and then leave it in the state that it was in
    /// prior to that (close it if needed or leave it open).
    /// </summary>
    /// <param name="StoredProcName"></param>
    /// <returns></returns>
    protected void ExecuteCommand(string StoredProcName)
    {
        bool IsAlreadyOpen = (this.oConnection.State == ConnectionState.Open);
        this.OpenConnection();
        this.oCommand.CommandText = StoredProcName;
        // 
        try 
        { 
            this.oCommand.ExecuteNonQuery(); 
        }
        catch (SqlException ex)
        {
            // Check if the sql exception is because of a deadlock rollback
            // If so we can attempt a retry
            if (ex.Number == DeadlockRollbackError)
                throw new BBRetryException("Deadlock", ex);
            else
                throw ex;
        }
        catch (Exception ex)
        {
            throw ex;
        }

        if (IsAlreadyOpen == false)
            this.CloseConnection();
    }
    protected void SaveTable(DataTable Table, string SaveProc)
    {
        // save each row
        foreach (DataRow row in Table.Rows)
        {
            this.SetAllParameters(row);
            this.ExecuteCommand(SaveProc);
        }
    }
    #endregion
    protected void OpenConnection()
    {
        if (this.oConnection.State != ConnectionState.Open)
        {
            this.ErrorMessage = "";
            this.oConnection.Open();
        }
    }
    public void CloseConnection()
    {
        if (this.oConnection.State != ConnectionState.Closed && this.oCommand.Transaction == null)
            this.oConnection.Close();
    }
}
public class BBRetryException : Exception
{
    public BBRetryException() { }
    public BBRetryException(string error) : base(error) { }
    public BBRetryException(string error, Exception e) : base(error, e) { }
}