Subscribe: Atom or RSS

The Two Devils of SQL

Charlie and I have a real love-hate relationship with SQL Server. Charlie loves how fast and how flexible it is. I hate how hard it is to use. I have been using ASP.NET for about three years now, and in that time I have tried to install innumerable free and commercial ASP.NET applications. I have succeeded only about ten percent of the time, with the problem always being the database. This triggered for a me a deep dislike for SQL Server.

When I first started thinking about creating a website framework, I was very tempted to use XML files as the data store. I like and understand XML, whereas I did not like and did not understand SQL Server. Fortunately, one company and one person came to my aid, and made SQL Server a viable data store for Charlie.

The company that came to my aid was Microsoft. Last year Microsoft deemed me worthy of an MVP award, and with that award came a subscription to the MSDN Network. I was able to download SQL Server 2000 and, with its Enterprise Manager, finally have a user interface to the database. The interface sucks, but at least I finally had one.

The person that came to my aid was Terri Morton. Poor Terri had to endure a hundred questions—and as many expletives—while I fumbled about with installing, configuring, and finally using SQL Server. Fortunately, Terri is both smarter and more patient than I am, and stuck with me until I finally had the SQL beast under control. Thank you, Terri.

It is the easy stuff that concerns me. Writing CRUD methods is so boring and so repetitive that I have given serious consideration to using the WilsonORMapper. This leads me to a choice between two devils. The devil I know is hand-coded CRUD methods. The devil I don’t know is Object-Relational Mapping.

Taking the advice of the aardvark—that simple is better than complicated—I have decided to stick with the devil I know, and just hand-code all CRUD methods. Sure it’s boring, sure it’s repetitive, but it is also simple and flexible. I have considered refactoring the database code, so that some of the repetitive code can be moved to either a base class or a utility class. However, I have decided that I will just keep the code simple, if repetitive. If my yet-to-be-engaged database specialist wants to introduce changes, that will be his or her prerogative.

I have however decided to give a friendly wink to the devil I don’t know. One of the nice features of Paul Wilson’s ORMapper is that it supports an IObjectHelper interface. Without this interface, object-relational mapping is performed by reflection, which is a relatively slow process. With this interface, the object-relational mapping is performed through a known indexer:

public interface IObjectHelper
{
    Object this[String memberName] { get; set; }
}

Here is a very simple business object that implements this IObjectHelper interface:

public class Person : IObjectHelper
{
    public Int32 ID
    {
        get
        {
            return this.id;
        }
    }
    private Int32 id;
    
    public String Name
    {
        get
        {
            return this.name;
        }
        set
        {
            this.name = value;
        }
    }
    private String name;
    
    public Object this[String memberName] 
    {
        get 
        {
            switch (memberName) 
            {
                case "id": return this.id;
                case "name": return this.name;
                default: throw new ArgumentException
                         ("Invalid Member", memberName);
            }
        }
        set
        {
            switch (memberName) 
            {
                case "id": this.id = (Int32)value; break;
                case "name": this.name = (String)value; break;
                default: throw new ArgumentException
                         ("Invalid Member", memberName);
            }
        }
    }
}

There are two great features here. First, the IObjectHelper interface is optional. If a business object does not implement this interface, then Paul’s WilsonORMapper will simply use reflection to populate the business object. If a business object does implement this interface, then the WilsonORMapper will use it to avoid the costs of reflection. You can read more about this in Paul’s weblog entry on O/R Mappers: Avoiding Reflection. The second great feature is that the interface provides a single point at which the Persistence layer interacts with the Business layer. This is of more benefit than is immediately apparent. To see why, have a look at a property of Charlie’s Article entity:

public String Title
{
    get
    {
        return this.title;
    }
    set
    {
        if (this.title != value)
        {
            this.title = value;
            MarkDirty();
        }
    }
}
private String title = String.Empty;

For reasons of performance and user experience, Charlie will not commit a business object to the database unless that object has actually changed (in which case its data is considered “dirty”). By using reflection, we could alter the private title field, so the object would not be marked as dirty. But reflection comes with the penalty of performance and complexity. Without relection, our Data Access code must work through the public Title property, so the object will be marked as dirty. But, when the object has been freshly retrieved from the database, it is not dirty, so this would an erroneous dirty flag.

The current solution in Charlie is to have the EntityManager “reset” the dirty flag on a freshly-retrieved object.

entity = this.Mapper.Retrieve(entity, criteria);
entity.MarkAfterLoad();
return entity;

This approach is a little clumsy, but it works just fine. And after all, a solution that works is a working solution. Even though this works, I have decided to replace this solution with the IObjectHelper interface. This way, my hand-coded CRUD methods have a single point of working with Charlie’s business objects. Later, if I switch to using the WilsonORMapper, the business objects will not need to change at all. That seems to be a good compromise between the devil I know and the devil I don’t.

by Alister Jones | Next up: Charlie has Cool URLs - Part 1 →

4 comments

----

Recent Posts

• Charlie’s Alarm Clock
• The EntityCache Object
• A Word on Lazy Loading
• The Cache is a Shadow, Not a Box
• Sunken Ships and Pirates
• The Provider Model, Unveiled
• The Magician and the Teacher
• The Practical Man
• Charlie. The Little Engine that Could
• The Monkey or the Gorilla?