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
    #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) { }
}

Why and How to Sub-class Base Classes

One of the “basic rules of programming” that I always live by (and I’m not alone), is that one should always sub-class many of the native base class controls.  This is one of the first things you should do before undertaking any serious development, in any language. There are always little quirks in behavior in some of the base class controls  that you want to get around for every instance of an object.

Say, as an example, that you've developed a few forms for your application. You've used the base class controls. At some point, you find something's not  quite acting right and you want to change that behavior or you may just want to add something (some properties or something) to every ComboBox, for example, that you use. Now, since you used the base class controls to begin with ... guess what? You've outta luck!! You've got a lot of work ahead of you to change all those. Whereas, if you had sub-classed all the controls first (even if you haven't as yet put code in those sub-classes) and used your sub-classed controls on your forms, then you'll have no extra work to do when you decide that you need to make changes to your sub-classed control (and yes, even the form should be sub-classed).

Basically, you'll want a class library that contains your sub-classed UI controls, like textbox, button, etc. Something like this:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;

namespace MyCompany.MyFramework.WinUI.MyClasses
{
    public class MyComboBox : System.Windows.Forms.ComboBox
    {
        // code here
    }

    public class MyTextBox : System.Windows.Forms.TextBox
    {
        // code here
    }

    public class MyButton : System.Windows.Forms.Button
    {
        // code here
    }
}

That's it. These controls can't be sub-classed visually, but as you can see, it's easy enough to do it in code. I have all the basic controls sub-classed in one class library file. Once they're added to the ToolBox, then can be dragged onto any design surface in the IDE.

Dealing with Carriage Returns in Web Service Methods

When returning data from a database through a Web Service (as a DataSet, or as an XML representation of the data in the DataSet), you may have issues with new lines (carriage return/line feed) in your string data. 

I use a SQL Server database. The "new line" data must be stored in the database like this: "\r\n". But, when passing this through a web service, it must be passed like this: "&#13". Consequently, to address this discrepancy in format, I use two methods in my Web Service base class: a ConvertCarriageReturnForGets() method and a ConvertCarriageReturnForSaves() method.

[WebService(Namespace = "http://mycompany.com/")]
public class BBWebServices : System.Web.Services.WebService
{
    #region Declarations

    private string CRLF    = "
";
    private string NewLine = "\r\n";

    #endregion

    #region Convert CarriageReturn Methods

    /// <summary>
    /// Converts CarriageReturn/LineFeed characters so they are passed correctly through the Web Service
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="ColumnName"></param>
    protected void ConvertCarriageReturnForGets(DataTable dt, string ColumnName)
    {
        if (dt.Columns.Contains(ColumnName))
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][ColumnName] != DBNull.Value)
                    dt.Rows[i][ColumnName] = dt.Rows[i][ColumnName].ToString().Replace(this.NewLine, this.CRLF);
            }
        }
    }
    /// <summary>
    /// Converts CarriageReturn/LineFeed characters so they are correctly saved to the database
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="ColumnName"></param>
    protected void ConvertCarriageReturnForSaves(DataTable dt, string ColumnName)
    {
        if (dt.Columns.Contains(ColumnName))
        {
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                if (dt.Rows[i][ColumnName] != DBNull.Value)
                    dt.Rows[i][ColumnName] = dt.Rows[i][ColumnName].ToString().Replace(this.CRLF, this.NewLine);
            }
        }
    }

    #endregion
}

Use the methods like this in your Web Service classes:

[WebService(Namespace = "http://mycompany.com/")]
public class Customer : BBWebServices
{
    [WebMethod(Description="Returns Customer Info for one customer")]
    public string GetCustomer(long CustomerKey)
    {
        CustomerBiz oBiz = new CustomerBiz();
        CustomerDataSet ds = oBiz.GetCustomer(CustomerKey);

        this.ConvertCarriageReturnForGets(ds.CustomerNotes, "notes");
        
        string XML = ds.GetXml();
        return XML;
    }
    [WebMethod(Description="Saves info for one customer")]
    public bool SaveCustomer(string ChangeXML, string DeleteXML)
    {
        CustomerDataSet dsChanged = new CustomerDataSet();
        CustomerDataSet dsDeleted = new CustomerDataSet();

        try
        {
            // FillWithXml() is a method I have defined in my DataSet classes
            // It simply uses the DataSet's ReadXml() method with a string reader
            dsChanged.FillWithXml(ChangeXML);
            if (DeleteXML != "")
                dsDeleted.FillWithXml(DeleteXML);
        }
        catch (Exception ex)
        {
            Message = ex.Message;
            return false;
        }

        CustomerBiz oBiz = new CustomerBiz();
        this.ConvertCarriageReturnForSaves(dsChanged.CustomerNotes, "notes");

        bool IsOK = o.SaveCustomer(dsChanged, dsDeleted);
        return IsOK;
    }
}

DataAccess – Part II

In Part I of this topic, I tried to explain the basic functionality you should strive for in a DataAccess layer in your applications:

• Keep the DataAccess totally separate from your UI layer.
• Pass data objects between the layers.

In Part II, I will attempt to show you how to abstract your DataAccess layer one more step. The key is to program to the IDb interfaces, as I will show below.

My DataAccess layer has two base classes: BBConnection and BBDataAccess. All the methods are coded to the various DataAccess Interfaces (the IDb interface objects). DataSets are used simply for transporting data back and forth. I know that there are plenty of camps who advocate Biz objects for this, but I'm a believer of Typed DataSets for transporting my data between front-end and back-end.

The BBConnection class basically has a GetConnection() method that finds the Connection String from the app's config info and returns an instantiated Connection. It returns it as an interface, the IDbConnection.

/// <summary>
/// For another type of database, replace the SqlConnection in the constructor
/// </summary>
public class BBConnection
{
    #region Declaration
    // Use a variable to store the connection string, if you try and get it from the SqlConnection object, it strips the password
    public string ConnectionString = "";
    #endregion
    #region Methods
    public IDbConnection GetConnection()
    {
        return this.GetConnection("");
    }
    public IDbConnection GetConnection(string tConnectionString)
    {
        string cConnection = tConnectionString;
        if (tConnectionString == "")
            cConnection = ConfigurationSettings.AppSettings["ConnectionString"];
        SqlConnection conn = new SqlConnection(cConnection);
        this.ConnectionString = cConnection;
        return conn;
    }
    #endregion
}

Probably a slightly better design than what I show below, would be to use a Factory Pattern ... but my code was initially refactored from more tightly-coupled-to-SQL code, and at the time was easier done the way I did it. I'll leave that as an exercise for the reader! ;-)

The BBDataAccess class, simplified, is something like this:

public class BBDataAccess
{
    #region Declarations
    private IDbConnection oConnection;
    private IDbCommand oCommand;
    private IDbDataAdapter oAdapter;
    private IDataParameterCollection oParms;
    public string ConnectionString = "";
    protected string ErrorMessage = "";
    #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 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
    // 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)
    {
        try
        {
            if (ParmName.StartsWith(this.AtSign) == false)
                ParmName = this.AtSign + ParmName;
            if (ParmValue != DBNull.Value)
                this.oCommand.Parameters.Add(this.GetIDbDataParameter(ParmName, ParmValue));
        }
        catch (Exception ex)
        {
            this.ErrorMessage += ex.Message;
        }
    }
    protected void AddParms(string ParmName, object ParmValue, ParameterDirection direction)
    {
        this.AddParms(ParmName, ParmValue);
        this.SetParmDirection(ParmName, direction);
    }
    protected bool FillData(DataSet ds, string StoredProcName)
    {
        try
        {
            this.oCommand.CommandText = StoredProcName;
            this.oAdapter.Fill(ds);
        }
        catch (Exception ex)
        {
            this.ErrorMessage += ex.Message;
            this.CloseConnection();
            return false;
        }
        return true;
    }
}

In this way, your DataAccess sub-classes are totally "disassociated" from knowing what your back-end data is (could be SqlServer, as the above class is, but if you also have an Oracle customer, you have another base class exactly the same as the above class, but it uses Oracle-specific stuff instead and you use the appropriate DLL depending on your which database your customer has ... however, you're still programming to the interface (the IDb Interface objects), so your sub-classes, which are in different projects (and thus different DLLs) don't care and don't need to be changed.

So, an example of a sub-class might be this:

public class CustomerAccess : BBDataAccess
{
    #region Public Get Methods
    public DataSet GetCustomerInfo(long CustomerKey)
    {
        CustomerDataSet ds = new CustomerDataSet();
        this.ClearParameters();
        this.AddParms("CustomerKey", CustomerKey);
        this.FillData(ds, "csp_Customer_Get");
        return ds;
    }
    // more methods
    #endregion
}

UPDATE: Since posting my 2-part DataAccess "article", I've realized that it should have been 3-part. There's another, better methodology for DataAccess that I've used and I'd like to show you all. So, see Part III of my series.