Hi, has anyone in here got some guidance or direction for Patterns and Practices on this one?

When should business logic be put into a SQL Stored procedure and when should it be put into Class object? we are debating different architectural approaches and I am looking for some guidance.
TIA?

Referenced post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=55375&SiteID=1

Another rule of thumb: If you decide to design the persistant elements as classes and then define the database, you are probably better with classes having the logic and vice versa.

Why? you want to leave data access with the team which designed the model ;-)

Anyway, there is no one answer. Using stored procedures optimizes data access ( if used wisely) and gives some other benefits like having compiled resultsets.

Using objects reduces your dependance on the database, and lets you have only C# or whatever skills in the team while the DB architect could just spend a few weeks designing and optimizing the database. If you are convinced on using OR mapping, you will have the logic in objects anyway.

Never put business logic into SQL stored procedures.

It seems to be a consensus about taking advantage of the strenghts and weaknesses of, both, OO paradigm and RDBMS

Very intensive computing activity would be better carried out by classes' methods than stored procedures, while intensive record-oriented activities fit better in stored procedures

Why? In the case of computing activity, because OO paradigm helps (without warrantying it) to achieve better component reuse and flexibility through interface-based programming, dependency injection and object composition, among other facilities still not present -or partially present- in databases

Why intensive data access better in stored procedures? Because we pay a performance penalty every time we make a round trip to the database, so by putting such logic all together in a stored procedures, we are applying coarse-grained granularity (a component design pattern). Maarten Mullender talks about fine-grained problems in his "CRUD, Only When You Can Afford It"

I particularly model behaviour in classes more associated with use cases than data; I also model entities as an OO representation of the database (so, I mean I model the database schema first) and usually get and store data by interchanging XML messages with the database (my RDBMS handles XML pretty well). Thus, I can store a graph of objects (for example, a purchase order with its items) in just one round trip

A good place to find info about Data Access is the MSDN Solution Architecture Centre

Well Thanks for the support RMD.

In my experience, I tend to avoid SP, till performance becomes an issue as there is a slight shortage of developers who know SP well. And also, logging/tracing gets distributed to multiple places ( We cant use entlib from Stored procedures AFAIK)

Typical instance where performance will force SP is if a page requires execution of 100s of SQL queries, OR if SQL where clause cannot be used to filter results.

Cases where performanc will force app layer logic is - if a cartesian join is required -lets say for a report. Then app layer can process records one by one instead of making the large cartesian join.

There are some other cases where we have used Stored procedure as well. An application required dynamic change in some part of business logic - 2-3 times a day. I felt it better to make that a SP and use alter statements, Rather than distributing DLLs and doing IISREST to load them. Now - if designing the same again - I  probably would go for a rule engine instead.

Another case was two applications (one of them legacy) interfacing by sharing the same DB tables. In that case, using SP to provide "interfaces" (if you will) to the new application made the development shielded from maintenance changes in Legacy applications.

Pranshu

Thanks all of you for your Posts to date. I will leave this one open for a while.

Rgds,

Martin.