The Valley of Data Access - Part 1
I am reporting live to you from the valley of data access. All around me I see towering mountains of SQL. Rising in front of me is the DocumentMapper mountain, upon which grows putrid-smelling commands and parameters. To its left is the ContainerMapper mountain, with more foul-smelling stuff. To its right is the RoleMapper mountain, and to the right of that is the UserMapper mountain. I am surrounded by these mountains of SQL.
I hate this place. It is dark and hostile, and I do not have a map or a torch. I need to find a way out. I need to find a way. Out.
I look into my toolkit, and I see three tools at my disposal. One is a grappling hook labelled “consultant,” one is an unopened box labelled “O/R Mapper,” and one is a knife labelled “refactor.”
I’ve thrown the grappling hook to a consultant who stands on top of these mountains. She wraps the hook around a rock, on which she has etched the word “experience,” and I start to climb out. But a man approaches the consultant. He says his name is Shane, and he has something to show her. She must make a choice, between the man dangling at the end of a rope, or the stranger standing before her. She cuts the rope. I fall, crashing back into the valley.
After the pain of the fall subsides, I look at the box labelled “O/R Mapper.” I open it and read the instructions. “For use only by those who know what they’re doing.” That’s not me. I close the box and think, “Maybe some other day.”
I take out the knife labelled “refactor.” I like this knife. I have used it before.
Enough of the story? I thought so too.
Right now, I have ten Mapper classes within Charlie. Each Mapper contains a method for Create, Retrieve, Update, and Delete. Each method is very long, yet each method is not much different from any other. So I have forty large, half-redundant data access methods. Every time I add a feature to Charlie, each method gets a little larger, a little more redundant. Every time I make a change to the database schema, I need to update many, if not all, of the forty separate methods. I need to take control—to reduce the size of the methods and eliminate the redundancy. I have decided to refactor this code before it explodes beyond a maintainable size.
To provide a working sample, here is one of the shortest of the unweildy 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;
}As I opened by saying, this report is coming to you live. I have printed out a few of these dastardly SQL methods, to look at how I might refactor them. I have not yet started to refactor this code. I’ll update this weblog as I do so.
by Alister Jones | Next up: The Valley of Data Access - Part 2 →
----
The iBATIS Data Mapper framework does something similiar for Java and .NET. Its been around since 2002:
http://ibatis.apache.org/
SQL statements are stored in XML files (they can be embedded resources) and support dynamic sql via tags like:
<isNotNull property="FirstName">
AND szFirstName = #FirstNAme#
</isNotNull>
Post a Comment