Im working on the architecture of a large application (100+ tables) and would like to use LLBLGen on it (adapter model). The dba on the project is having a lot of issues with using LLBLGen; he has all the normal issues that come up when talking about O/R tools (security, performance, ...). However, the one point he has that I havn't seen talked about much in the O/R - SP debate is that stored procedures have the ability to abstract the data model from the object model. I also believe that this is an important abstraction within an architecture, and am trying to find the best way to solve the issue (hopefully without having to revert to always using stored procedures).

Here's a couple of thoughts I have...

1) The definition of "object model" is important here. You could have two sets of objects in the system; entities and business objects. Entities would map directly to the database model and would be operated on using LLBLGen. Business Objects would encapsulate entities to expose what the clients wants in terms of an API. This seems like it would work but would lead to a ton of extra code that in some places wouldnt serve much of a purpose. I'm not a big fan of this approach is it is defined here.

2) forgot this point, ill update my post later if i remember it

What do people think about this issues If you have run into this issue, how did you solve it? What was the final outcome of your solution (did it work)?

Check this link for more info on the O/R - SP debate... http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

Thanks CT

ctadlock wrote:

Im working on the architecture of a large application (100+ tables) and would like to use LLBLGen on it (adapter model). The dba on the project is having a lot of issues with using LLBLGen; he has all the normal issues that come up when talking about O/R tools (security, performance, ...). However, the one point he has that I havn't seen talked about much in the O/R - SP debate is that stored procedures have the ability to abstract the data model from the object model. I also believe that this is an important abstraction within an architecture, and am trying to find the best way to solve the issue (hopefully without having to revert to always using stored procedures).

I don't see why it is of any importance to have that abstraction in the form of a functional API. After all, a set of stored procedures are just a set of functions in a library. But does that help you working with data in an object oriented manner? Not at all, on the contrary. (and I also don't see why a DBA would be concerned about that abstraction, as he/she doesn't do the actual software development)

Here's a couple of thoughts I have... 1) The definition of "object model" is important here. You could have two sets of objects in the system; entities and business objects. Entities would map directly to the database model and would be operated on using LLBLGen. Business Objects would encapsulate entities to expose what the clients wants in terms of an API. This seems like it would work but would lead to a ton of extra code that in some places wouldnt serve much of a purpose. I'm not a big fan of this approach is it is defined here.

If it leads to a lot of extra code for no other reason than DBA politics, why would you go through all that hassle? Don't use 2 class libraries because someone says so, use 2 class libraries because it really helps you. If it doesn't help you, don't use it. Often it is more practical to model the classes representing abstract business processes the same as these business processes, consuming entity classes. You then have a theoretical base for the definition of the business process classes, and you don't have to write a lot of code.

What I've learned is that the pro-stored procedure people have some arguments but these are most of the time not that well informed about other options for the same problem plus a lot of these discussions are really about politics: the more dynamic SQL there is in the world, the less the importancy of the DBA will be. DBA's still are important, but they shouldn't influence the structure of how software is written. They administrate a database, they don't control the development process.

ctadlock wrote:

stored procedures have the ability to abstract the data model from the object model

I typically havent found this to be true. What I have found with SP laden systems is that there are 2 types of stored procedures. Conditional stored procs, and stored procs tied to a CRUD operation.

A conditional procedure is one that uses arguments of the procedure and internal control of flow langurage to determine if the procedure is to do a SELECT, Insert, Update, Delete. The other type of procedure is upGetOrders, upAddOrder, upDeleteOrder, etc etc, i.e. the procedure is directly tied to Select, Insert, Update, or Delete.

This isnt abstraction, this is strongly defining your API to your DAL. Abstraction is being able to pass an object that Implements an Interface to some method that works with any object that implements the interface. If Customer, Order, and Employee all implement IAddress, the SaveAddress(IAddress newAddress) can work with Customer, Order, and Employee objects in the same fashion. That is abstraction.

Using stored procedures you have a very fine grained interface. You would prefer a coarse grained interface to an api, such as Adapter.FetchEntity(SomeObjectThatImplementsIEntity2), etc.

As to the importance of an object model: back in the days when I wrote VB6 classes that were strongly tied to stored procedures, everytime the analys changed a field in the table, I had to change my method to handle the new field, change the stored procedure, change the COM+ object, redeploy the COM+ Proxy. With an object model, the service that does CRUD operations on an entity doesnt care about the fields, just the entity. So with an object model, when the analyst changes his mind, all thats required is a simple schema refresh, code gen, and additon of new rules where the changes need to take place. The point is that no interfaces are changed, and impact to the system as a result of a field name or slight change in logic dont have such a major impact.

Three cheers for Frans's statement about the DBAs. It usually is politics, and they will throw all sorts of reasons at you. Just wait till the DBA starts talking a SQL Injection Attacks from the ORM tool. That will be fun.