When designing business objects I have tried several different methods of writing the data access layer. Some have worked out better than others but I have always felt there must be a "better" way.
I would really just like to see the different ways people have handled the DAL in different situations and their opinon of how the technique worked or didn't work well.
Unfortunately I don't think there is a "better way", it's too dependent on the specific situation as to what DAL approach you use. A great discussion of the "state of the art" is Patterns of Enterprise Application Architecture by Martin Fowler.
Chapter 10, Data Source Architectural Patterns specifically talks about most of the most commonly used patterns for business applications.
In general though, I've found using the simplest approach that meets the basic maintainability and adaptability requirements is the best choice.
For example on a recent project a simple "Row Data Gateway" was all I needed. (This was simply code generated classes for each relevant database table, including methods to perform the CRUD operations). No endless debates about ORM versus stored procs, it just worked, and did the required job well.
There are several common patterns. 'The patterns of enterprise architecture' book is a good reference for these:
If you use an ORM, such as llblgen, you get the choice of self-servicing or adaptor.
I've relied heavily on Billy McCafferty's NHibernate Best Practices article / sample code for many Web / WinForms applications now. It's a wonderfully written article that will provide you with a good solid sample architecture -- in addition to teaching you basic NHibernate and TDD. He tries to give you an overview of his architecture and design decisions.
He creates a very elegant DAL using generic DataAccessObjects which you can extend for each domain object -- and its very loosely coupled to the BL using interfaces and a DAOFactory. I would recommend looking at the BasicSample first, especially if you haven't worked with NHibernate before.
Note, this article relies heavily on NHibernate, but I think the general approach it could be easily altered to suit other ORMs.
If you're going down the NHibernate route (good article link BTW from @Watson above), then I'd strongly recommend that you checkout the suvius-flamingo sample project from codebetter. He has a very nice, succinct, sample project which shows MVC and NHibernate in action.
Here's the suvius-flamingo link.
In our open source project Bunian, we concluded that the Business Objects (the whole component) is the core of the system, and everything should revolve around it including that data access layer.
The Business component will dictate to others what it needs, implying that through itnerfaces. For example Business Object Person will have an interface member called IRepositoryForPerson, this member will be assigned an instance through Dependency Injection container when needed.
For more details check my blog post here:
http://www.emadashi.com/index.php/2008/11/data-access-within-business-objects-bunian-design//
and check Bunian's code here (although it's amateur yet):
http://www.codeplex.com/Bunian
Of course there will be emerging new things with this approach like the life cycle of the data access session (if you are using NHibernate for example). but that would be for another question i guess :)
i hope you find this useful
I am going to assume you mean writing a DAL that is accessing SQL, because this is the most common part today. ONe if the biggest problems in writing a DAL against SQL is the ORM part. That is, there is a fundamental impedance mismatch between OO programming and relational database schemas. There have been many great, succesful even, attempts at writing ORMs. But they all suffer from the same problem that is their benefit: they abstract you away from the underlying SQL being generated. Why this is a problem, is that the performance of your database is a critical compponent of how well your system functions overall. Many ORMs (perhaps most) not only have less-than-stellar performance for many standard queries, but actually encourage patterns of usage that will degrade performance considerably (traversing relationships repeatedly within loops when querying collections being one common example, making resolving deadlocks difficult being another). Of course, after learning the ORM API in detail, you can usually find ways around these performance potholes.
My current take on the state of ORMs is that I want it to do as little as possible, while still giving me the efficiencies of a solid library that takes care of all of the nuts and bolts of data access. In other words, because I don't think they are "good enough" yet, and may never be with SQL as the back end, I want to retain control at the bare-metal level, and I will drop down to writing SQL by hand without hesitation in many cases, regardless of the ORM, because I know the specific way I want the data to be queried for my given needs.
This is obviously a more brittle approach to coding than if you religiously use the ORM as it was intended, so as a result, you have to be extra diligent in terms of unit testing, SQL injection, and proper separation of concerns. So, to sum up, I agree with Ash, although that does not imply he/she agrees with me :)