Whats the best way to build a DAL? In my latest projects I used the Strongly Typed Dataset, using the designer in Visual Studio, but that's difficult to get transactions on, without using TransactionScope which is resource intensive? Is it best to build your own objects and simulate the functionality?
Secondly, can one transaction be attached to two db connections? I have a transaction that spans databases.
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.