Layers of Programming 2 - Data Access Layer

I love the data access layer. That may sound a little weird, but it really is my favorite layer. The reason for this is simple: with the data access layer, you set it, and forget it. The data access layer, if done right, I think, is the smallest layer, in terms of lines of code. My methods in the data access layer tend to get pretty big, and encompassing, but there's often only a few methods that I ever need to put into the data access layer.

Maybe it's just me, but being able to reuse a method or a class hundreds of times, and have it provide a wide array of functionality really epitomizes what programming is all about. I just love it whenever I can work smart, instead of working hard.

So, let me throw out an example of a data access layer I've used in the past.

It's a lot of code, so I'll break it up into logically separated sections:

public class DatabaseConnectionParameters
{
   private const string _ConfigurationFile = "config.ini";
   FileIO.FileIO _File = new FileIO.FileIO();

   private string _Server, _Database, _User, _Password;
   public string Server
   {
      get
      {
         _Server = _File.GetSetting(_ConfigurationFile, "Server");
         return _Server;
      }
      set { _Server = value; }
   }

   public string Database
   {
      get
      {
         _Database = _File.GetSetting(_ConfigurationFile, "Database");
         return _Database;
      }
      set { _Database = value; }
   }

   public string User
   {
      get
      {
         _User = _File.GetSetting(_ConfigurationFile, "User");
         return _User;
      }
      set { _User = value; }
   }

   public string Password
   {
      get
      {
         _Password = _File.GetSetting(_ConfigurationFile, "Password");
         return _Password;
      }
      set { _Password = value; }
   }
   
}

The above is basically a parameter storage class. It has access to a class called FileIO, that I use to get settings from a config file. I'll omit that class here, because it's fairly simple, yet space-consuming, and I'm kind of lazy :). But overall, the above class doesn't really do anything, except act as a more organized way for me to store settings.

Next comes the actual Data access layer class. In this class, I do what anyone would expect me to do, as far as incorporating .Net libraries. I use SqlConnection, SqlDataReader, and SqlCommand to allow me to interact with my database. I also instantiate my FileIO class again, to allow me to log events (notice that my FileIO, at least in this application serves 2 similar purposes - it reads from a text file to get settings and it writes to text files to log events. If one were really to follow a single responsibility principle, one may separate these two things (reading and writing). I happen to think that is overboard, but I'm just "some guy."

My main method in DAL is ProcessStoredProcedure(). Basically, all it does is:
1. Calls handleConnectionState() to ensure that the connection is not currently open to the database. This is a strategy I use, but not necessarily industry standard. There are a lot of strategies in OLTP datab ases that allow multiple things going on at the same time (perhaps with threads, or other neat-sounding things), but I don't need to be that extensible with my apps, and single database transactions at once is not necessarily a "kiss of death" when it comes to creating a good application.
2. Opens a connection to the database
3. Call a stored procedure
4. Attach parameters to the stored procedure based on the contents of the ArrayList I pass to it
5. Execute a DataReader, and attach it to a Datatable, and return it

public class DAL
{
   SqlConnection _Connection;
   SqlDataReader _Reader;
   SqlCommand _Command;
   DatabaseConnectionParameters _Parameters = new DatabaseConnectionParameters();
   FileIO.FileIO _File = new FileIO.FileIO();

   public DAL()
   {
      _Connection = new
         SqlConnection("Server=" + _Parameters.Server + ";" +
                   "DataBase=" + _Parameters.Database + ";" +
                   "uid=" + _Parameters.User + ";" +
                   "pwd=" + _Parameters.Password);
   }

   public DataTable ProcessStoredProcedure(string procedureName, ArrayList parameters)
   {
      DataTable assembledProcedureResults = new DataTable();
               
      if (arrayListCountIsEven(parameters))
      {            
         handleConnectionState();
         _Connection.Open();

         _Command = new SqlCommand(procedureName, _Connection);
         _Command.CommandType = CommandType.StoredProcedure;
         assembleStoredProcedureParameters(parameters);
         _Reader = _Command.ExecuteReader();
         assembledProcedureResults.Load(_Reader);
         _Connection.Close();
         _Command = null;
         return assembledProcedureResults;
      }
      else
      {
         _File.LogEvent(true, "Encountered an unexpected count of parameters in ProcessStoredProcedure()");
         return null;
      }
   }

   private void assembleStoredProcedureParameters(ArrayList parameters)
   {
      if (parameters != null)
      {
         int counter = 0;
         string parameterName = "";
         string parameterValue = "";
         foreach (string item in parameters)
         {
            if (counter == 0)
               parameterName = item;
            else
               parameterValue = item;
            counter++;
            if (counter > 1)
            {
               counter = 0;
               _Command.Parameters.Add(new SqlParameter(parameterName, parameterValue));
            }
         }
      }
      
   }

   private void handleConnectionState()
   {
      if (_Connection.State != ConnectionState.Closed)
      {
         try
         {
            _Connection.Close();
         }
         catch
         {
            try
            {
               _File.LogEvent(true, "Encountered error closing unexpected open connection. Retrying");
               _Connection.Close();
            }
            catch
            {
               _File.LogEvent(true, "Encountered error closing unexpected open connection. Unable to recover");
            }
         }
      }
   }

   private bool arrayListCountIsEven(ArrayList list)
   {
      if (list == null)
      {
         return true;
      }
      else if (list.Count == 0 || list.Count % 2 == 0 )
      {
         return true;
      }
      else
      {
         return false;
      }
   }

}

What this allows me to do is separate any database activity with any application logic or user interface stuff. This is the way I like to do things, because it keeps me closer to "Single Responsibility" and it ends up making for better looking code.