I first mentioned SubSonic, a .NET DAL framework, almost a year ago in a post discussing my decision regarding a DAL framework for use in an ASP.NET project I was working on. When I made my decision Subsonic was in it’s very early stages of development but peaked my interest nonetheless. I think it’s interesting that a little over a week ago I decided to use SubSonic for a project at work and now Rob Conery, Subsonic’s primary developer, has joined Microsoft to continue development as part of the new MVC framework.

If you haven’t looked at SubSonic, it’s well worth a try particularly if your database consistently makes use foreign keys allowing for the generated object model to provide deep navigation capabilities for lookup field values etc. I’m working on a large ERP system with a database of 300+ tables and generating a SubSonic assembly took only a few minutes. At any rate, it’s always nice when an OS project you’ve chosen receives this sort of backing offering at least some reassurance.

[UPDATE: Jan 10, 2007] Fix spelling of Rob's name.

Recently, I blogged about some of the Open Source .NET DAL implementations I've worked with and mentioned Adapdev whose primary developer is Sean McCormack. Sean had posted a message to the Adapdev blog about slowing development but this morning while I was reading through my blog feeds I found this post explaining that the slow down didn't really materialize and mentioning some nice updates to several of the projects related to Adapdev.NET. Sean also mentioned that he's nearly completed a new ORM framework called Elementary which I'll be very interested to look at.

A Data Access Layer, commonly referred to as a "DAL" is code that provides the ability to view, edit, update and otherwise manipulate persistent data within an application. In many cases, a DAL can be generated from a database schema and alleviates developers from the task of writing highly repetitive code. All of the DAL's I've looked at have benefits and detractors so the task of actually selecting the perfect implementation isn't straight forward and will depend on the application requirements as well as the developers ability work with the given DAL implementation.

There are lot's of DAL implementations available like:  

• .nettiers
• nHibernate which is a port of Hibernate core
• SPOIL
• SubSonic - the zero code DAL
• Adapdev
• DBMethods

There are many others but the above list represents ones I've either experimented with or actually used with the one exception being CLSA.NET which I've seen mentioned in numerous places and thus why included it.

A few months ago SPOIL implementation in particular caught my eye and seems quite interesting. In fact, I did some digging on this one and found that a BorCon speaker Steve Scott presented the same idea at BorCon 2003 and even before that Atif Aziz had developed DBMethods though there is no mention of it in the MSDN article (referenced above) but I did find an interesting comment from Atif. For a website I've been working on over the past few years I chose to use CodeSmith 2.6 which is a freeware code generator specifically for it's "collection of database templates" which are included in the download. These templates provide several pieces of the DAL puzzle including:

• Stored Procedure generation for all CRUD operations
• Command wrappers for the above stored procs
• Typed datasets
• Business Objects
• Typed collections

Of course, lots of things have changed since I made my choice, CodeSmith is up to version 4.0 and now sold commercially and AFAICT garnered quite a following not to mention .NET 2.0 was new at the time and there wasn't a whole lot of support for it. Additionally, the templates were subsequently updated to utilize C# 2.0 features like generics. CodeSmith is an easy to use code generation tool that has an interesting template technology that borrows techniques from ASP.NET. I'll admit it took me awhile to convince myself that I needed to go this route at all but eventually I was satisfied with the decision. I'd have probably gone in a different direction today though I don't really feel locked into the decision I made since the code is fairly boiler plate and it wouldn't be that difficult to switch to another DAL.

A couple of months ago I spent quite a bit of time looking into Adapdev and it's supporting tools Codus, a code generator and Zanebug a unit testing tool. It's really a nice combination and allows you to quickly generate a DAL and immediately test it allowing you to quickly locate problems. The developer Sean McCormack has been very responsive to bugs and even quickly turned around a fix for a significant problem I had run into. Unfortunately, he has recently blogged about a slowdown in the development though I think it's still well worth a look.

I don't want to ramble on too long so I'll wrap it up by saying that I've had some good success finding very useful DAL implementations though I have yet to find one that has really addressed all of my needs. If you've been doing similar work I'd be interested to know what's worked for you.

Update: Paul Hectors made the comment that CSLA.NET is a Business Object framework and not a DAL implementation. He's right. Here is a link that explains what CSLA.NET is from the author himself. Thanks for the correction Paul.

[UPDATED: April 25, 2008] Related post:

• Subsonic DAL now backed by Microsoft