<< Home
Dot Net Object-relational mapping, the why, what and how!
I worked on a contract around two years ago building some web applications that had a closely coupled object layer to the underlying database.
In short, there were classes created for every non-reference table and an accompanying manager class which handled persisting the associated objects. After a while as the application evolved I decided that I'd write some code to generate the classes and manager classes, or at least a large part of them, mainly the CRUD functionality, to save time and increase my productivity and to stop the boredom of the repetitive task!
I started by doing this in T-SQL, generating manager classes when I needed to. As I moved onto using the same practices in other projects I eventually turned this into a C# console application.
About a year later, after I'd refactored and rebuilt a lot of my code using the new features in C#2.0 (generics made my life a lot easier)
I started to look into some Open source alternatives. I began to realise that there were plenty, doing a similar thing, Object-relational mapping or ORM. This was something, at the time, I didn't even realise had a defined term!
How I approached it
I used a Data Access Layer and some base classes, I'd already written and just generated the code, that derived from them, using the console applicaton I built, that uses the database defintion described in the system tables in SQL server.
My aims were that:
- I wanted to use data readers, not data tables or data sets for extracting data, for speed and efficiency
- I wanted to use stored procedures that I could adapt, when necassary, for the persistence layer
- I wanted to generate objects, not just strongly typed datasets
Doing things this way seems practical and scalable but maybe not as efficient as some of the real ORM tools out there. I'm building classes, their associated manager classes and the stored procedures used to persist objects. I have always been using SQL server and if I am ever going to use a different database I plan to replace the underlying DAL and update the ORM / code generation tool that generates the classes - I built it with this in mind using changeable text based templates!
However, I realise that what I'm doing requires a re-run of the code generation tool everytime there is a change to the database, there's no run-time SQL generation trickery! This is far from ideal and I'm looking into ways of generating the code as and when things change. At the moment I have just put some practices in place that get around this!
Aside from the re-generation issue, all of this suits what I need. Each of the classes I generate derives from a common base class and the manager classes derive from a base manager class which, in turn, derives from my Data Access Layer class! All of which can be removed replaced or updated at any point or used with dot net remoting for scalability!
If I want to extend any of the classes, with additional data provided from another database table, I could easily create new stored procedures and create new manager classes which extend existing ones. It might not suit everyone, but that's the way I do it... at least for the moment!
So I'm happy to use my generated classes in a production environment, because they are exactly what I would have coded by hand, no ORM overheads.
What the other tools are doing
So, what my ORM tool doesn't do is all the cool stuff that some of the open source or commercial tools do, it pretty much only generates CRUD functionality through the manager classes, so I guess what I have created is more of a code generation tool than a strict ORM tool.
I'm always on the lookout to adapt the way I do things or adopt new patterns and practices. So here are a few of the alternatives I've been looking into. I don't claim to have looked at any of them in any depth and there are probably plenty that I have missed, but here goes:
A port of the original Java Hibernate tool. Probably being used by a lot of companies who have java developers and dot netters!
You build configuration files that tie the object to their underlying data table and it does the rest! I think this can be used with several underlying databases.
A flexible ORM tool that works with SQL server and plenty of support through other Open source projects including a Visual studio 2005 Snap-in and GUI. You can use stored procedures and views with subsonic, as an alternative to it generating the SQL for you.
This sounds quite promising, no GUI as far as I'm aware but it maps C# classes to database tables though run-time generated SQL.
..And finally as part of looking at Orcas
Whilst I have been writing this article I came across what I think is the most comprehensive list of ORM tools I've seen so far, it covers more than just the dot Net ORM tools:
http://www.answers.com/topic/list-of-object-relational-mapping-software
If you want to know more, if I can help you with anything mentioned in this article, get in contact using the form on the home page