The task was simple and sounded like a decent technical challenge and an interesting opportunity.  Build an ASP.NET 2.0 with SQL Server 2005 transactional website that the financial industry can use to list special events and webinars so traders and investors can find it all in one location.  I worked with the business owner and came up with the high level requirements.  They are listed next and will look familiar to most of you.

1.    Provide ability for users to register.

2.    Allow registered users to list their events and webinars.

3.    Allow registered users to perform CRUD actions to entered data (create, read, update, delete).

4.    Provide administration interface for CRUD to all application data.

5.    Display all events on the website in a format that allows sorting.

Of course, there were more requirements than what is listed above, but this is the core that we focus on moving forward in this article.

I have my own technical requirements that I bring to projects I lead, namely, that it should be as object oriented as possible and utilize the tools and technology available.  ASP.NET is object oriented itself, so extending this concept into the other layers of the application is logical and advantageous.  The second requirement is that the design should not only promote code reuse, it should provide for it.  Repetitive code and functionality is a waste and a design “smell” that I try to avoid. 

This project is the type that could easily end up with repetitive and hard to maintain code, especially in the data layer.  Let us take a look at the options for data access.  First, we could write inline SQL for all data access.  We can rule that one out easily, as it ends up being difficult to maintain and makes OOP concepts difficult to apply.  Let us not jump in the tank with that octopus.

The second option is to use stored procedures and wrap them with their own methods.  This is without a doubt the most popular way to build a data driven solution with any of the .NET languages and technologies.  If you ask any group of developers if they use stored procedures for their data access at any point in time you will see that four out of five reply with an affirmative.  Take that to the next logical step and ask why they use stored procedures; the answer is almost consistently – (a) it is faster because it is compiled and (b) that it is more secure.  I will accept (b) as a technically sound and savvy answer, but will question (a) all night long.  Without rehashing this impassioned argument, I will simple say you can get the speed of stored procedures within .NET without writing a single stored procedure.  Plus, you can get that with a better, cleaner and more object oriented manner.

What is Object Relational mapping?  In a nutshell, it is a technique to provide automatic retrieval and persistence of data.  Usually this involves using a configuration file, often in XML, to map fields in your custom coded objects to fields and tables in a database.  In this way the OR Mapper can then take ownership of the best way to retrieve, update, insert and delete data and you can focus on building the application and not mess with the plumbing. 

Most professional developers use third-party components at some time and OR mapping products are an example of the acceptance and reliance on these types of components.  The reality of our work is that almost every application has to touch a database and this data access work is error prone, tedious and honestly, just plain boring after a while.  This type of work lends itself to mistakes.  I am reminded of the classic software developers axiom, “if you can reduce risk, then do so.”  This is one reason to use an OR mapper.

Another OR mapper advantage is the speed of development.  Using an OR mapper can drastically reduced the amount of code that needs to be written and debugged, plus it can quicken how you develop.  If you are mapping data directly to your objects, essentially making them entity objects, there is not data conversion between Datasets, data readers, structs or anything else.  This reduces the complexity and makes working with your data essentially the same as working with your objects.  Now your focus is on your domain model and not how ADO.NET is doing this or that task.  Please note that I am not saying that understanding ADO.NET is not important – you should understand these concepts to be a productive and knowledgeable developer – but now that you understand the core concepts you can step beyond it and really take advantage of this knowledge.

True OR mappers, which I consider different than code generators, do all their database access dynamically.  So, they are not generating files with code in them, and they are not using stored procedures.  In the .NET world, the mapper will do all its database access using parameterized queries, which as controversial as it may sound, are just as fast as stored procedures.  Thus, you now have the flexibility of dynamically generated SQL with the speed of a stored procedure.

Use of an OR mapper also provides a level of abstraction between your code libraries and the data source.  Let us say that you are using MS Access as your application data source and you are using an OR mapper.  You can now easily switch from MS Access to SQL Server 2005 without too much trouble because you do not have anything hard coded into your application that ties you to a platform.  Extrapolate this out and you can migrate from Oracle to SQL Server or Access to MySQL, or really any way you wish.

The biggest concept to understand here is that the OR Mapper is providing flexibility.  Flexibility in how you write your objects, flexibility in how you spend your development time and flexibility in what your data source is.  And of course, they are fast at the same time.

There are many different OR mappers available.  The three products that I consider when thinking about the object relational mapping category are NHibernate, LLBL Gen Pro, RapTier and Wilson ORM.

NHibernate is the open source port of the popular OR mapper Hibernate for java.  I am not a java developer so I can not speak to how good of a port it is.  I have not used NHibernate myself, mainly because I have found the documentation of the product to be poor so far.  There is a very active community around this product, but for my project I did not want to participate in their project, I wanted to focus on building mine.  I have read and heard of success stories from colleagues along with their accompanying horror stories with NHibernate.  I do consider NHibernate to be a true mapper because it does not generate code for the developer to manage.  It does all its work dynamically once configured.

The second mapper is LLBL Gen Pro, which comes with its own environment for mapping objects with database tables and fields.  This product does generate code files, so it is not something that I wanted to use this time around.

The third product is called RapTier.  I have used this on a couple different projects earlier this millennium with success.  RapTier does create code files though, so you have to maintain those and is something I would not consider doing now.  But at the time, I experienced this product to be a great time saver because it allowed me to focus on the application and not the data access. The company also provided good support and an aggressive price. 

The last product is WilsonORMapper and is ultimately what I decided to use on this project.

When push came to shove the choice for this project really came down to the merits of WilsonORMapper versus NHibernate.  They are the two true, non-code generating mappers that I investigated and each had their own pluses and minuses.  I choose WilsonORMapper for a couple key reasons; starting with that Paul Wilson, the creator, provides direct support for the product and the support site includes an active user forum where other users help each other and share advice and support.  Beyond this, Wilson provides staid examples explaining how to use the different features of WilsonORMapper at a very good price.

Compared to NHibernate, these features were much more important to me during this project than the open source community behind NHibernate.  I made the conscious choice to focus on a tool that could help me build my project as opposed to contributing to the tool’s project and would have support behind it.

Part of the elegance of using an ORM product is that you can continue to use the domain (or call them entity) objects that you might design before event deciding on using ORM.  This allows you to keep your custom objects and other steps towards a domain model and OOP principles intact while at the same time simplifying your database access.  See Figure 1 for a part of the domain model used on this project.

Figure 1

In the above figure you can see part of the domain model.  Here, we have objects describing a Listing, Feedback, Owner, Event and an interface for Event named IEventDetail. 

Configuration File

The configuration file is what ties together the database tables and fields with your objects as shown in Listing 1.

Listing 1

<?xml version="1.0" encoding="utf-8" ?>
<mappings version="4.2" defaultNamespace="">
    <entity type="Owner" table="Owner"
    keyMember="Id" keyType="Auto" sortOrder="LastName ASC" autoTrack="false">
    <attribute member="Id" field="OwnerId" alias="Id" />
    <attribute member="FirstName" field="FirstName" alias="FirstName" />
    <attribute member="LastName" field="LastName" alias="LastName" />
    <attribute member="Street" field="Street" alias="Street" />
    <attribute member="Street2" field="Street2" alias="Street2" />
    <attribute member="Number" field="Number" alias="Number" />
    <attribute member="City" field="City" alias="City" />
    <attribute member="State" field="State" alias="State" />
    <attribute member="Postal" field="Postal" alias="Postal" />
    <attribute member="Country" field="Country" alias="Country" />
    <attribute member="Company" field="Company" alias="Company" />
    <attribute member="Email" field="Email" alias="Email" />
    <attribute member="UserName" field="UserName" alias="UserName" />
    <attribute member="Salt" field="Salt" alias="Salt" />
    <attribute member="PasswordEncrypted" field="Password" alias="Password" />
    <attribute member="Role" field="Role" alias="Role" />
    <relation relationship="OneToMany" member="Listings" field="OwnerId"
    type="Listing" alias="Listings" lazyLoad="true" cascadeDelete="true" />
    <relation relationship="OneToMany" member="Events" field="OwnerId"
    type="Event" alias="Events" lazyLoad="true" cascadeDelete="false" />
  </entity>

The configuration file is in XML, so the hierarchy of data is simple to read and just as easy to understand after you use it for a short while.  The entire file is wrapped in the mappings element and each table-to-object relationship is defined in an entity element.  The Listing 1 example above ties the object Owner to the table Owner.  It sets the object key, Id, to the tables' primary key via the keymember attribute.  It then sets the default sort order and a attribute named autoTrack which lets the ORM framework know whether or not to watch this object and perform SQL changes on its own.

Each property of the object is then mapped to a database field via an attribute element and member attribute. 

Notice the element relation.  This provides foreign key relationships to other tables.  In this example the field OwnerId in the table Owner is linked to the table Listing with a synonymous field name.  From here we can set the Listing object to be lazy loaded inside of the Owner object.  So, to summarize this, each object is mapped to a table in the configuration file.  Furthermore, a related object can be lazy loaded as a property in another object via foreign key relations.

ObjectSpace Manager

The ObjectSpace object is part of the WilsonORMapper libraries and is the main coordinator that is used by the developer to access data through the framework.  It is best to only have one instance of the ObjectSpace object in an application.  Doing so will help manage concurrency in the application between the data source and the mapper and secondary; the object loads up the xml configuration file from disk.  So, obviously you do not want to repeatedly read from disk or recreate the object over and over.  See Listing 2 for an example of how to create the object, in our case and by example from Wilson, the object is static and named Manager.

Listing 2

public class Global
{
    private static ObjectSpace _Manager;
    public static ObjectSpace Manager
    {
        get
        {
            if (_Manager == null)
            {
                CreateORMapper();
            }
            return _Manager;
        }
    }
 
    private static void CreateORMapper()
    {
string mappingFile =    System.Web.HttpContext.Current.Server.MapPath("~/trader.config");
      _Manager = new ObjectSpace(mappingFile, ConnectString, Provider.Sql2005);
    }
}

Select

Let us take a look at a method that retrieves data from our source by doing a simple SELECT in Listing 3 below.

Listing 3

public Event GetEvent(int Id)
{
   return (Event)Global.Manager.GetObject(typeof(Event), Id);
}

This method simply returns a filled Event object via the Id passed into the method.  We use the GetObject method on the Manager object, which is of type ObjectSpace, and tell it the type of object to return.  The mapper then knows what table the object type is mapped to and does the translation for us. The GetObject method can also then take in the parameter here, which is simply doing a query via the primary key setup in the configuration file.

Notice here how everything is strongly typed.  The code does not have to know anything about ADO.NET or even the internals of the Event object.  The mapping loads the right result set fields into the created Event object properties and fields. 

On a side note, it is a matter of preference if you map your table fields to properties of the object or fields.  The more draconian OOP bylaws would say that if you’re setting data in an object it should be via properties. But here, the mapper can access everything inside of the object anyway – so, it is a matter of preference.

Returning one object is simple, but the world works in large numbers, so let us look at returning a collection of objects. See Listing 4 below.

Listing 4

public IList GetEvents(string sortOrder)
{
    ObjectQuery oq = new ObjectQuery(typeof(Event), "", sortOrder);
    ObjectSet allEvents = Global.Manager.GetObjectSet(oq);
    return allEvents;
}

The GetObjects method of the Manager object returns a collection object called an ObjectSet, which is part of the WilsonORMapper libraries.  Being a well behaved collection it implements the IList interface.  So our GetEvents above can be typed to IList to add some flexibility. 

Insert, Update and Delete

Doing the other parts of the CRUD cycle (create, update and delete) are very similar.  Listing 5 below shows an insert and Listing 6 shows an update.  These are all working against that same Event type.

 Listing 5

public void Insert(Event theEvent)
{
Global.Manager.StartTracking(theEvent, Wilson.ORMapper.InitialState.Inserted);
Global.Manager.PersistChanges(theEvent, Wilson.ORMapper.PersistDepth.ObjectGraph);
Global.Manager.EndTracking(theEvent);
}

Listing 6

public void Update(Event theEvent)
{
Global.Manager.StartTracking(theEvent, Wilson.ORMapper.InitialState.Updated);
Global.Manager.PersistChanges(theEvent, Wilson.ORMapper.PersistDepth.ObjectGraph);
Global.Manager.EndTracking(theEvent);
}

The basics of this operation are that the methods first call a WilsonORMApper method called StartTracking on the Manager object and then does the persistence.  StartTracking lets the mapper know that this object and its associated row in the database are going to have work done to it.  I am not sure if WilsonORMapper locks the row then or not, but it does add safety to the operation.  Part of StartTracking is telling the mapper if we will be inserting and updating data.  Then we call the PersistChanges method and lastly end our tracking of that object and database row. 

Performing a delete is very similar and can be seen in Listing 7.

Listing 7

public void Delete(Event theEvent)
{
Global.Manager.StartTracking(theEvent, Wilson.ORMapper.InitialState.Unchanged);
Global.Manager.MarkForDeletion(theEvent);
Global.Manager.PersistChanges(theEvent, Wilson.ORMapper.PersistDepth.ObjectGraph);
Global.Manager.EndTracking(theEvent);
}

The only addition with the delete is we call MarkForDeletion which is another trigger letting the mapper know what we are about to do to that object and its data.

An aspect of object oriented programming that is simpler to do with an object relational mapper is lazy loading one object type from within another.  For example, if we want to load up the Owner object of an Event object, for instance, the company hosting the event, we can write our lazy load property in the Event object and then simply create an Owner object with the owner id key returned from our Event object load.  In this way your object model can grow and objects maintain a high level of encapsulation.

An object relational mapper is a powerful tool to use and can save days - and possibly weeks depending on the size of the project - of tedious coding and debugging.  Within a short time you can get up and running and use the object model that you created for the project without having to rework a lot to run stored procedures or other ADO.NET queries.  In the end you can focus on the core business logic part of the application and consider the mapper and your data layer as plumbing that came with the house.  WilsonORMapper simply makes this much easier with better support than other free or complicated OR mapping solutions out there.