Continuing my reply about Celemens' O/R post, I thought that I would give a more real world example of the secnarios. Here is my schema:

I think that this is a pretty standard schema (if a bit simplified) for shipping orders to customers. Now, let's say that I want to show the customer all his orders, and let him see the details of each order. Here is my objects for this application:

So far so good,  I would assume? There shouldn't be any major objections unless I'm missing something really big. Now, let's say that I want a sophisticated DAL that I can use with the Unit Of Work pattern. I spend a couple of hours refreshing myself with Fowler's and Evans' books, and then I set to the task. It's not a trivial matter, but it's a very boring one, I think. Let's start with the overall design, I think that I can find three root objects, Customers, Products and Orders. This means that I will want the following DAL:

I'm not so sure about AddOrder() and CancelOrder() in the CustomersRepository, but I'll leave it there for the meantime. Now that I've the design, I'll probably use code generation to create stored procedures and most of the DAL. It shouldn't be too hard to find pre-written code that does this, and it shouldn't be hard to write if it isn't avialable. I now can write the code to display a customer on a page:

Simple, isn't it? And not that much different from what I would get if I where using an ORM tool. Now, let's try to display all the orders of the customer, shall we? At this point, we can use either of two ways. Let the repositories handle it, and access it naturally:

Or explicitly access it via the OrdersRepository, like this:

The first way means that Customer should know whatever its orders have been loaded or not, and that it should talk to the OrdersRepository when it needs its orders. This way means that you can lazy load or not. The second way means that a Customer knows nothing about its orders, and that you always need to explicity access them from the OrdersRepository. This way does not allow for non-lazy load aproach.

Now, let's consider a common scenario, that is currently not supported by this DAL. Displaying a list of all the orders and their order lines for a customer. Something like:

In order to get that from the database, we need to:

• Add a stored procedure that gets the data.
• Add a method to the OrdersRepository that calls this stored procedures and then takes result set from the stored procedure and put it into objects.

I would assume that the query would be similar to:

It took me a couple of minutes to write  this query, and it's not a complex one. This is probably the best way to get all the data from the database in a single query. Does this sound familiar to you? I just do not believe that realizing that you need to another query should cause quite that much trouble. Using NHibernate (my ORM of choice) I can express the above query with:

But you know what, that is the cheesy example, it doesn't really mean anything. It's not the true benefit of ORM.

The true benefit of ORM is that it make it possible to use OOP when dealing with data. For instance, if I were building this system, I might chooce to create a FedExOrder and a UpsOrder, and discriminate on the DispatchMethod. This way, I don't really care what dispatch methods an order has. It is handled inside the object, using the familiar encapsulation and seperation of concerns that I use elsewhere.

The data in this case is not a disconnected entity that my code is manipulating. There is no such thing as data only, there are objects that encapsulate the data and has responsabilities. That is why I like ORM so much. In the example above, all I need to do in order to write add air mail dispatching is to create an AirMailOrder. The integration between the data and the objects is the key here. It opens up a whole range of possibilities.

That said, it's important to understand what we are working against. We are not working against the physical schema of the database. We are working against the logical structure of our objects. There is a mapping document that allows the ORM to translate our object structure to the database schema, but there doesn't have to be any connection between the two. You are free to modify each independently, and writing an application that uses ORM doesn't mean that you will not be able to use good database design ideas, or that the data will be inaccessible to other applications, which may not use the same ORM (or anything at all, for that matter).

If your schema changes, you update the mapping file, and you're done. If you are worried about changing the schema for deployed clients, you can even put the file in the database, and have the client initialize itself by loading the latest schema from the database. I would say that worried about being unabled to change the database structure.

I just don't see stored procedures and custom DALs as a good abstraction for the database, since more often than not, they are used blindly to map the schema to result sets or DTOs, without giving much benefit over the simple approach. If I want to truely seperate my applications from the database, I would use a Service (web or not is another matter), not a custom DAL.

The good thing about ORM is that you can use if for more than just databases, in one of my project, nearly everything was accessed via IRepository<T>, which was specialized to objects were persisted to the DB, objects that I got from external services, etc. I didn't care how that happened. In this case, ORM was an implementation technique that greatly aided in the implementation of the database bound objects.

That is a good abstarction layer, in my opinion. Bulid a custom DAL when you can save quite a bit of time and effort on ORM is a waste, in my eyes. Premature optimization is bad, but so is premature abstraction.