In the last few months I have been working on our biggest Saleslogix ASP.NET module to date. As such I have decided to take a good look at the best way to design these in the future in order to make them easily adaptable to other customer’s web sites.

The first step I need to take is evaluate the data access strategy. The schema will almost always have slight variation from customer to customer so it is important we get that one right so that it is flexible enough.

Criteria for evaluating data access methods (in order of importance, the first 5 being strictly required):

1. Can’t break polymorphism: if I need to add a field to the data object, the existing methods on the service should still work with the new object, without modification. This is because I will need “customized” versions of the service for various customers, but want to keep a common trunk for the shared functionality. For example, using the VS interface to generate typed dataset won’t work, because I will end up with completely different types. Basically this means the mapper needs to support inheritance, which pretty much makes it a requirement for it to be a full fledged ORM framework like nhibernate.
2. Must be switchable with another data access method without a major hassle (ie, without affecting the service objects)
3. Allows for custom generation of the primary key… ya that one would be a show stopper unfortunately since I have to generate Saleslogix ids
4. Not requiring the presentation layer to deviate from the ASP.NET standard practices (e.g. monorail will be out, sadly)
5. No or little code needed (or, automatic generation generation of that code)
6. Standard, widespread use – we’ll have to stay away from some interesting but not well known frameworks, this may sounds silly but a name like dOOdads does not look too serious on a resume (I will still be evaluating dOOdads, though, I think it looks very promising)
7. Simple concepts to grasp (I don’t mind spending a week learning how to use a particular framework but I need to be able to get my coworker up to speed fast enough!)
8. Allows for the “special” Saleslogix cases: for example the link Contact.Addressid pointing to Address, and Address.Entityid pointing to contact. If this is not possible out of the box then we’ll have to be able to emulate it in the DAO.
9. Performance (running, not startup)
10. Weight (e.g. NHibernate is great but may be a bit heavy to deploy on a desktop application… of course this is where the switch-ability can come into play to offset this) – this would mainly affect the startup performance for web apps, which is important since we usually runs on a shared server)
11. Weight of DTO (would prefer not to have a Dataset there as they are not great to store in a session)
12. A package that also exists in Java would be nice

Here are the systems I would like to evaluate, I don’t know yet if I will have time to check them all out, but I would like to at least see if they pass #1 to 5:

1. nhibernate (I actually already implemented the site with this one). It is a very complete framework, the biggest argument I have against it is that it is fairly heavy and complex, but it will do all we need. It is not very flexible, it is hard to do the mapping when the objects have a different structure than the database (which will almost always be the case when we work with the SLX db), but we can work around that with code at the DAO layer. It also has a few rigidities that get in the way when dealing with legacy data (for example, missing foreign keys cause an exception, obviously the ideal solution would be to implement constraints all over the place, but this is not always practical when we have to deal with remotes or non-customizable code). Also has the advantage of being a pretty cool “resume” item.
2. dOOdads. Ya the name is stupid but it looks like a simple, honest mapping tool – it won’t support inheritance OOB though, so I have to see if there is a way to get around that
3. DAAB (from Microsoft) – this is probably a bit too heavy for what I want to do, and seems to be still changy/experimental, but I might take a look
4. Straight ADO.NET – problem is I can’t use the built-in interface because the objects are not extensible, and it won’t support the Saleslogix ids. So I guess I could use MyGeneration to write a code template for the DAO and DTO, but without a whole lot of hassle it will break #1 above (since the code generator will produce 2 different types). Of course it would not be that much work to just hand-code everything, but still seems like a waste of time compared to nhibernate. Here is a description of how to get it done in a somewhat organized way: http://www.microsoft.com/belux/msdn/nl/community/columns/hyatt/ntier2.mspx. Advantages of this approach: no 3rd party component to deploy, easier to debug, easier to tweak, don’t have to worry about a massive startup time.
5. ibatis – this is lighter weight than nhibernate (it is really just a data mapper rather than a full fledged ORM framework), but would still do all that we need. The SQL statement are usually hand-crafted so we can have them perform any little trick needed on the SLX db. There is a DAO framework (in addition to the datamapper itself) but it seems like it might be limited when dealing with objects distributed between separate assemblies, because it will create one manager per assembly rather than having the possibility to share them (to avoid the problem of nested transactions, I think). All in all, there doesn’t seem to be much advantage of using that rather than straight ADO.NET for small projects, and I am not convinced it can scale well to more complex projects.
6. ActiveRecord – looks really cool, basically this is like nhibernate but you stick attributes on the properties instead of writing a mapping file. Of course I use mygeneration to generate the mapping file anyway so this is not that big of a deal. I am mostly concerned about the weight added by this, and also the fact that it is not as standard as nhibernate – between this and nhibernate I would probably stick with nhibernate.

At this point I think ibatis and nhibernate are the most promising since they claim to support object inheritance mapping (well, I know that nhibernate does, at least). ActiveRecord is based on nhibernate and it does look really easy to use. I may still give a shot to the dataset approach since it has the advantage of being simple, easy to deploy, and also be applicable to the Pocket PC.

I have prepared a simple test case consisting of the Contact with multiple Addresses domain, a simple “service” which returns a concatenation of the addresses within a contact, and then an “extended” service which adds a few fields to the contact: Salutation (stored in the Address table, in the primary address), Notes (stored in C_CONTACT_EXT) and Birthday (stored in Contact). All fields (except the keys) are nullable. I am going to write the test app for ibatis and nhibernate and post a followup with the results.

Update (12-28-2006)

Here are my conclusions on the solutions above:

NHibernate

This is massive. It works well, but it is very complex, the documentation is not great, although I have found the doc in the most recent version (1.2.0 beta 2) to be a lot better. I think this will be the best option for bigger projects. Besides it is the best option if I want to upgrade to monorail later on.

iBatis

I can see how this one would be useful if you had to have DBA prepare all the SQL statements. Otherwise, the benefits are fairly minimal compared to the amount of extra work.

Straight dataset

This starts to break down a bit once you want to do complex relationships, inheritance, etc. Might still be OK for a quick app.

d00dads

I felt this didn’t give much benefit over using a straight dataset so I didnt really look at it deeper.

SubSonic

Had to mention that one because it looks so cool, unfortunately the rules are not as configurable so I don’t think it can handle all of the Saleslogix legacy schema weirdness. Too bad! I listened to the presentation from Rob Conery on .netrocks today and it sounds awesome, so I definitely want to try that, just maybe not on a Saleslogix project…