Subscribe: Atom or RSS

The Valley of Data Access - Part 2

Once I had decided to refactor the data access code for Charlie, the first thing I did was look for a guide. I am frightfully inexperienced with SQL, so I wanted to see if I could find an article, or project, or generator, or anything else, that would guide me.

I started with Google. But every article I found presented the same technique: code a single method that will create a database connection, create a command, add the parameters, open the connection, execute the command, get the data, and close the connection. Yet this everything-in-one-method approach is prone to error and prone to duplication, and is precisely what I wanted to avoid.

I next looked as some open-source projects, including some rather expensive ones, hoping they would take a more considered approach to data access. But no. Each of the projects I looked at took the same everything-in-one-method approach, with an occasional variation being the use of stored procedures in place of hard-coded queries.

I then trialed a commercial code generator. Unfortunately, I could not for the life of me work out how to use it. So I opened a sample project, comprising just four business objects, and used it to generate a data access layer. I don’t know, maybe it’s just me, but I think 3,000 lines of data access code per business object is a tad unnecessary. And then there was the fudgy business object code needed to support the whopping data access code.

After casting about, looking for a guide but failing to find one, I made the increasingly-common decision, “To hell with it, I’ll do it myself.”

I started by looking at the following two pieces of code. The first is the hard-coded query:

String query =
      @"SELECT
            s.Note_Id, 
            Note_CreationDate, 
            Note_UpdateDate, 
            NoteLocalized_Title, 
            NoteLocalized_Content
        FROM
            Charlie_Note s
        INNER JOIN
                Charlie_NoteLocalized sc
            ON
                s.Note_Id = sc.Note_Id
        WHERE
            s.Note_Id = @noteid
        AND
            sc.NoteLocalized_Culture = @culture";

The second was the method that accepts the returned data reader, and populates a business object.

note.CreationDate = Convert.ToDateTime(reader["Note_CreationDate"]);
note.UpdateDate =   Convert.ToDateTime(reader["Note_UpdateDate"]);
note.Title =        Convert.ToString(reader["NoteLocalized_Title"]);
note.Content =      Convert.ToString(reader["NoteLocalized_Content"]);

To put the code into words, the first query string defines the source of the data, while the second method defines the destination of that data. My first thought was, “It would be great if I could create a method that described the mapping between the source table and column names, and the destination property names.” I scribbed down the following code on a piece of paper:

protected Mappings GetMappings()
{
    return new Mappings(
        // Table        // Column            // Property
        "Charlie_Note", "Note_CreationDate", "CreationDate",
        "Charlie_Note", "Note_UpdateDate",   "UpdateDate",
        // and so on
        );
}

My mind then started thinking about how my new data access code would use this mapping information to automate the process of retrieving data from the database and applying it to the business object. I spent a few minutes thinking about this before the little devil on my shoulder whispered, “Alister, you’re talking about creating your own little O/R Mapper here, and we both know you’re not smart enough for that.” The angel on my other shoulder then whispered, “Well, you may be smart enough, but it’s still a dumb idea.”

I went back to looking at the code in my data access methods. I scratched my head a bit. What bothered me is that while each of the methods were very similar, each one had enough little quirks to make it hard to extract any common code. I scratched my head a little more. I then remembered a key design principle from my favourite book for nerds, Head First Design Patterns:

“Identify the aspects of your application that vary
and separate them from what stays the same.”

With that principle in mind, I looked at each of the methods and noted which parts varied and which parts stay the same. In the following listing, the red code is what stays the same within all Retrieve methods.

public override Entity Retrieve(Entity entity, EntityCriteria crit)
{
    Document document = (Document)entity;
    DocumentCriteria criteria = (DocumentCriteria)crit;
    SqlConnection connection =
       new SqlConnection(this.ConnectionString);
    SqlCommand command = new SqlCommand();
    String query =
       @"SELECT
             d.Document_Id,
             d.Document_ParentId,
             d.Document_Name,
             d.Document_FriendlyUrl,
             d.Document_Position,
             d.Document_CreationDate, 
             d.Document_UpdateDate, 
             c.DocumentLocalized_Culture,
             c.DocumentLocalized_Title
         FROM
                Charlie_Document d
             JOIN
                Charlie_DocumentLocalized c
             ON
                c.Document_Id = d.Document_Id
         WHERE ";
    if (criteria.LoadById == true)
    {
       query += " d.Document_Id = @documentid";
       command.Parameters.AddWithValue("@documentid", criteria.Id);
    }
    else if (criteria.LoadByUrl == true)
    {
       query += " d.Document_FriendlyUrl = @friendlyurl";
       command.Parameters.AddWithValue("@friendlyurl", criteria.Url);
    }
    else
    {
       throw new ArgumentException("Invalid criteria.");
    }
    command.CommandText = query;
    command.Connection = connection;
    SqlDataReader reader = null;
    try
    {
       connection.Open();
       reader = command.ExecuteReader();
       if (reader.Read())
       {
          document = NewDocumentFromReader(reader);
       }
       reader.Close();
       base.AddRolesToEntity(document, criteria, connection);
       connection.Close();
    }
    catch (Exception exception)
    {
       throw new DataAccessException(
          "Could not load document.", exception);
    }
    finally
    {
       if (reader != null && reader.IsClosed == false)
          reader.Close();
       if (connection.State != ConnectionState.Closed)
          connection.Close();
    }
    return document;
}

I decided that whatever parts stayed the same would be moved out to a helper class. That would leave the mapper to concentrate on its unique requirements.

What is good about this approach is that by concentrating all of the common code in a single helper class, I would have one point at which to enhance that common code. When I had previously discovered that I was not properly rolling back transactions, I had to go into every one of ten data access classes and make the correction. This way, I would have just one point at which to correct the transaction-based code.

Because this approach had been inspired by the Head First Design Patterns book, my mind started thinking about whether any patterns would help me here. But the little devil on my shoulder whispered in my ear, “Keep it simple, stupid.” The angel on my other shoulder whispered, “You’re not stupid, but keep it simple, sweetheart.”

I decided then that the Mapper class would simply create a query string, create a collection of parameters, and pass them all into the new helper class to be executed. No pattern there, just a clean separation of preparing the database command from executing the database command. Simple.

by Alister Jones | Next up: The Valley of Data Access - Part 3 →

2 comments

----

Recent Posts

• The Valley of Data Access - Part 1
• The WebHandler Object
• Static File Handlers
• Charlie’s Birth
• Think Big
• Story Time
• Charlie has Cool URLs - Part 4
• Charlie has Cool URLs - Part 3
• Charlie has Cool URLs - Part 2
• Charlie has Cool URLs - Part 1