Best way to build a DAL?

If you have this kind of enterprise level requirements for a DAL then I recommend using NHibernate, which is build (and has been used) for the most demanding of db challenges.

It has a steep learning curve, but to get you quickly up and running I have found it easiest to teach people by first automating the process (several tools exist for this purpose) and only later dive into the details of NHibernate.

But since your requirements seem so high, the enterprise example (which can seamlessly be used with multiple databases) and the best practices explained in this celebrated article are a good start (assuming you know about DAL and DAO terminology it isn't even such a terse read): http://www.codeproject.com/KB/architecture/NHibernateBestPractices.aspx

In conjunction with generics (a MUST when you do DAL), you can achieve a very transparent yet very customizable and programmable layer.

I have researched other DAL tools but I always end up working with NHibernate. It has its shortcomings, but it's use of reflection and code extension make that you can use your entities and dao's right away without the need for building and linking: it is all done in runtime based on XML files containing the definitions.

Oh, and I forgot: there's excellent documentation available. More on the Java part then the .NET part, but the APIs are the same anyway and the differences are trivial. If you want to go that path I can give you some pointers on where to start and what to put on your bookshelf...

I wrote a code generator to build not just the DAL but also the BLL. If you would like a copy to play with email me nick@patchwork.co.uk - i'm not looking for anything for it, just be nice to know it's being used.

I've now used it on loads of projects including a commercial web site within our organisation.

As far as transactions are concerned, i've not had the need for these yet, but have looked into it, I might need to do some work on that area.

The tool is very simplistic, it has a design tool for desiging each table. You can then design SQL statement for qurying the data. That's about it really, the program then spits out a .VB file with the DAL and BLL code in it including a CreateTable function which is brilliant for deploying to a new remote web server. Note this only works with Microsoft SQL 2000 and 2005 databases at the moment.

You can use MyGeneration code generator to do that. It automatically creates DAL and BLL for you based on your database schema: http://www.mygenerationsoftware.com/portal/default.aspx

An alternative way is to create your own DAL and BLL with Strong Typed Dataset. Scott Mitchell wrote some excellent tutorials for this: http://www.asp.net/learn/data-access/

Good advice on the tutorials. My code gereator was writen with the help of the code examples from that site.

That's the way it will end up looking like in the code. The generators will make sure the right mapping files are created (i.e., where it says that "BirthDate" is of type DateTime, and NHibernate will automatically do the necessary conversion to and from the DB Server datatypes). NHibernate will notice when an object is new and when it is updated and will act accordingly. It will work with server-generated Ids seamlessly.

And, most importantly, it will map relationships onto IEnumerable and ICollection and IList generic types (one to one, one to many, many to many). These relationships will make it even easier to work with the objects, as you can loop, select, add, query etc these lists.

Ok, I admit, I am a bit of an NHibernate addict by now... but that's because -- apart from some annoying newbie misunderstandings of vague errors -- it just never failed me.

There are many O/R mapping tools out there, some are free and some are not, some are good and some are not so good. The reason I suggest MyGeneration is our developers are using it and have no problem with it, and it surely is the same reason you suggest NHibernate. I personally prefer to create my own DAL and BLL with Strong Typed Dataset, the reasons are simple: 1. It is part of Visual Studio 2005, so I don't have to rely on any thrid-party tool; 2. Maintanence. if a developer who used a code generator and left, whoever takes over the developer's project(s) will have to first learn how to use the code generator in order to maintain the project(s). 3. Support. What if there is a bug in the code generator tool? Where can I get the support and the bug fix?