Andrew Stopford's Weblog

I simply dont use sprocs anymore.  With a good OrMapper, you dont need them. That is a layer I dont have to *deal* with anymore in my apps.

Now there are arguments that have been re-hashed a billion times, but in the end the OrMapper's benefits out weigh sprocs by far. I wont argue sprocs do have some benefits.

Also there are some OrMappers that can use sprocs, http://www.ormapper.net comes to mind for the best of both worlds.

This post by Paul Wilson is interesting - http://weblogs.asp.net/pwilson/archive/2004/09/18/231188.aspx

but in a real world query what about several tables with several joins or variables from one table to serve another query etc?

Depends what you're doing.

If it's pure CRUD, where your OO and the relational model have clear correspondence there's one answer.

If the sprocs do some relational work that will take a lot of OO code to emulate it's another.

Neither is there a good reason for an OR-mapper not to encapsulate sprocs!

I feel the real problem is languages that have big blind spots.  (Think C# vs. T-SQL for example.)  COmega looked on the surface to have a much smarter language design, though we won't (unfortunately) see all of that any time soon.

To repeat.  It depends what you're doing (and how things are architected).

nhibernate... kickass...

I agree with Travis. We use LLBL Gen Pro (http://www.llblgen.com) on our site (http://www.myhomepoint.com) and it is great to leave the majority of our DAL up to the O/R mapper. Occasionally we've had to go outside of it, but he includes strongly typed methods for calling sprocs.

One of the major source of power for most ORM is that they have a great amount of flexibility in generating SQL.

SP limit you to a predetermained set of operations, often very hard to handle.

If you need do present a different view to the world than your real tables, use views.

The place where I still use SPs for even when using an O/R mapper is for triggers - especially in cases of updatable views.

After doing O/R mapping for quite some time now, I have found that updatable views are a required abstraction. On the one hand, you want to allow the DBA (sometimes myself) to reorganize or partition tables (for example horizontally) for improved performance, while on the other hand you don't want to have to redo all the mapping work. Views give you just what you need.

In my last post I pondered for how a ORM and Sprocs fitted in, for mea database that is in at least 4nf

O/R mapping tools are becoming more popular each day and people are realizing the productivity gain they provide to developers. Yet, many people don’t know enough about O/R mapping to consider using these tools and many others are weary of using any code generators (including O/R mapping tools).

In this article, Iqbal Khan try to educate people about the various important features that a good O/R mapping tool would provide us and how it can be beneficial to us.

O/R mapping tools are becoming more popular each day and people are realizing the productivity gain they provide to developers. Yet, many people don’t know enough about O/R mapping to consider using these tools and many others are weary of using any code generators (including O/R mapping tools).

I've written a CodeSmith template which along with data access entities generates the sprocs for me, and have integrated this step into the VS.NEt 2003 build using Nant.

Sprocs != bad

O/R Mappers can work with sprocs to generate a strongly typed result (O/R Mapping) of the sproc as well as make the sproc look more like a function in that the syntax you call the sproc is also strongly typed.

The best OR/Ms let you use sprocs where you want to and entities and collections everywhere else.

I've used LLBLGen Pro generate a DAL included storngly-typed stored procedure calls that enabled my team to start out by calling all of our legacy procs, and then deciding where to refactor to entities and collections.  That was years ago, and today that project still have some procs, but they just turned out to be not productive use of anyone's time to refactor (and in one case, would not have been performant to do so).

I'm now looking at SubSonic which does everything through ASP.Net 2.0 build providers to produce the OR/M objects at build and run.  This cuts down generated DAL code to ZERO (although there is a utility to generate the classes for you since you can't use build providers in medium trust by default).