Which one is better? IMHO, it is definitely much better to put business logic in a class rather than in a SP. In fact, it is generally NOT a good idea to put business logic in a SP.
Regarding the pros and cons, there are many, for both sides. I will talk about some of them.
First, one has to deal with the issue of centralization. That is, one goal in managing business logic is to centralize it so that it is maintainable. It is a major problem to have business rules scattered throughout the system. Note also, as a corollary
of sorts, that business rules should be unique and atomic. Therefore, it quickly becomes clear that if one tries to put all business logic in the SPs it is virtually inevitable that some logic simply cannot be expressed easily in SQL. This is because some
logic is simply too complex. Therefore, one would have to put some of the logic in SPs and some of it in classes. However, this breaks the rule of centralization. Keeping the rules in the SPS and the rules in the classes synchronized and ordered is usually
very difficult and definitely error-prone. Furthermore, some rules may not access the database at all. This type of rule cannot be put in an SP easily; so, again, one would have to capture such rules in a class, which, again, scatters the business logic rather
than centralizing it.
Another con of putting business rules in the database is that it is not simple (and often not possible) to organize business logic neatly in a database. For example, avoiding the duplication of logic is certainly very tricky in a database, given that the
logic is often non-trivial and may need to be put in-line in more than one SP. To compound the problem, SQL logic tends to be linear, which makes generalizing logic difficult.
All that being said, note that it IS a good idea to have a rules engine. As such, if the rules engine uses the database to store "business rule DEFINITIONS", then that is OK and actually preferable. However, business rules, as a rules engine stores them,
are VERY different compared to simply having business logic hard-coded into a SP.
Of course, if you are talking about SQL 2005, then one CAN write SPs in .NET languages and it might be easier to manage rules in the database. While I have not tried that (yet), I already suspect that it easier and better to keep the rules in a rules-processing
layer, such as a business rules engine.
Note that all of this is just my position on this topic. It stems from several central ideas, such as: the database's job is to provide a CRUD operations on data; the business logic layer's job is to run business rules; and so on. This is the delegation
of responsibility. Note also that I generally do NOT recommend using ANY SPs-- rather, I recommed using an OR-mapper like LLBLGen or WilsonORMapper or EntityBroker or any one of the other fine products out there.
Mark stated the reasons for putting your logic in code and not in stored procedures. I expressly agree with that!
I do NOT agree with not using stored procedures, and maybe Mark just needed to explain a little bit more. OR-mappers can come in handy, definitely. But, in a large enterprise the use of stored procedures makes a lot of sense, for speed, security, deployment
reasons. It creates a true seperateion of the tiers.
I used to use stored procedures for CRUD operations, but have migrated away from that and now use parameterized sql statements that are dynamically generated in my DAL.
Before you make a decision, you may want to look at what O/R Mappers have to offer.
But, in a large enterprise the use of stored procedures makes a lot of sense, for speed, security, deployment reasons. It creates a true seperateion of the tiers.
There are some advantages to including some of your business logic in stored procedures, particularly if certain functionality will require many SQL statements to be executed consecutively.
For example, in one of my applications, every time a customer logs in all of their existing orders (which could number in the thousands) must be updated to display an up-to-the-minute status. The logic determining the status of each order is complex, and is
dictated by a number of conditions in the database. I can achieve the desired result simply by a single call to 1 stored procedure, which grabs all of the customer's orders in a cursor, then loops through the cursor and runs a number of queries on each order,
determines what the order status should be, then updates the order status one order at a time.
The stored procedure is very complex, and it does move this logic out of my BLL, but to do this all from .NET could entail literally thousands of database calls.
I've been using NHibernate for some time now and think it's probably the best open source solution. It has a steep learning curve though. If you already know it, I would stay with it.
If you want to start out a bit softer, then you could try Paul Wilson's or mapper, it has good community support and is fairly easy to learn.
In some situations I wouldn't rule out using a stored procedure, but it all depends on the type of applications you make. When that is said, I haven't used a sp for a couple of years now ;)
yonscun_lie
Participant
777 Points
171 Posts
Business Layer in stored procedure?
Jan 16, 2006 01:34 AM|LINK
Do you think which one is better :
to put our business logic in database stored procedure
or
to put our business logic in Object Class (Model)
What're the pro and cons of both method?
thanks
Amer
Member
50 Points
10 Posts
Re: Business Layer in stored procedure?
Jan 16, 2006 11:00 AM|LINK
I think using Class Objects is much better than SPs.
mkamoski
Contributor
5694 Points
1565 Posts
Re: Business Layer in stored procedure?
Jan 16, 2006 04:40 PM|LINK
Which one is better? IMHO, it is definitely much better to put business logic in a class rather than in a SP. In fact, it is generally NOT a good idea to put business logic in a SP.
Regarding the pros and cons, there are many, for both sides. I will talk about some of them.
First, one has to deal with the issue of centralization. That is, one goal in managing business logic is to centralize it so that it is maintainable. It is a major problem to have business rules scattered throughout the system. Note also, as a corollary of sorts, that business rules should be unique and atomic. Therefore, it quickly becomes clear that if one tries to put all business logic in the SPs it is virtually inevitable that some logic simply cannot be expressed easily in SQL. This is because some logic is simply too complex. Therefore, one would have to put some of the logic in SPs and some of it in classes. However, this breaks the rule of centralization. Keeping the rules in the SPS and the rules in the classes synchronized and ordered is usually very difficult and definitely error-prone. Furthermore, some rules may not access the database at all. This type of rule cannot be put in an SP easily; so, again, one would have to capture such rules in a class, which, again, scatters the business logic rather than centralizing it.
Another con of putting business rules in the database is that it is not simple (and often not possible) to organize business logic neatly in a database. For example, avoiding the duplication of logic is certainly very tricky in a database, given that the logic is often non-trivial and may need to be put in-line in more than one SP. To compound the problem, SQL logic tends to be linear, which makes generalizing logic difficult.
All that being said, note that it IS a good idea to have a rules engine. As such, if the rules engine uses the database to store "business rule DEFINITIONS", then that is OK and actually preferable. However, business rules, as a rules engine stores them, are VERY different compared to simply having business logic hard-coded into a SP.
Of course, if you are talking about SQL 2005, then one CAN write SPs in .NET languages and it might be easier to manage rules in the database. While I have not tried that (yet), I already suspect that it easier and better to keep the rules in a rules-processing layer, such as a business rules engine.
There is a lot more to this topic. I hope all of this gets you thinking. Look for articles on the web about it, I say. I have a simple business rules overview at this link http://www.WebLogicArts.com/Downloads/BusinessRulesEngineOverview.pdf if you are interested.
Note that all of this is just my position on this topic. It stems from several central ideas, such as: the database's job is to provide a CRUD operations on data; the business logic layer's job is to run business rules; and so on. This is the delegation of responsibility. Note also that I generally do NOT recommend using ANY SPs-- rather, I recommed using an OR-mapper like LLBLGen or WilsonORMapper or EntityBroker or any one of the other fine products out there.
stephenzr
Member
479 Points
105 Posts
Re: Business Layer in stored procedure?
Jan 16, 2006 07:08 PM|LINK
I do NOT agree with not using stored procedures, and maybe Mark just needed to explain a little bit more. OR-mappers can come in handy, definitely. But, in a large enterprise the use of stored procedures makes a lot of sense, for speed, security, deployment reasons. It creates a true seperateion of the tiers.
So, in summary again. Code belongs in the code.
Stephen
yonscun_lie
Participant
777 Points
171 Posts
Re: Business Layer in stored procedure?
Jan 17, 2006 01:49 AM|LINK
Hm....I just thinking what if I use SQL Procedure Function for CRUD operation only (Create-Retrieve-Update-Delete)?
Or maybe it's better to leave SQL CRUD (select, insert, update, delete) in Database Broker Class?
mbanavige
All-Star
134944 Points
15413 Posts
ASPInsiders
Moderator
MVP
Re: Business Layer in stored procedure?
Jan 17, 2006 11:57 AM|LINK
I used to use stored procedures for CRUD operations, but have migrated away from that and now use parameterized sql statements that are dynamically generated in my DAL.
Before you make a decision, you may want to look at what O/R Mappers have to offer.
macrap
Participant
870 Points
174 Posts
Re: Business Layer in stored procedure?
Jan 18, 2006 10:47 AM|LINK
Well... or mappers can be used in large enterprise applications.... And about the "usual suspects" spped, security you could take a look at : http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx
Sane Productions
- Because it's too easy being insane
www.sane.dk
yonscun_lie
Participant
777 Points
171 Posts
Re: Business Layer in stored procedure?
Jan 19, 2006 01:01 AM|LINK
Currently for O-R Mapper, I only ever test nHibernate. But Can you tell me which O-R Mapper is used most?
After reading article and comment......now I just wandering (actually confuse), is there a point when to use Stored Procedure?
rich freeman
Participant
798 Points
159 Posts
Re: Business Layer in stored procedure?
Jan 19, 2006 04:30 AM|LINK
For example, in one of my applications, every time a customer logs in all of their existing orders (which could number in the thousands) must be updated to display an up-to-the-minute status. The logic determining the status of each order is complex, and is dictated by a number of conditions in the database. I can achieve the desired result simply by a single call to 1 stored procedure, which grabs all of the customer's orders in a cursor, then loops through the cursor and runs a number of queries on each order, determines what the order status should be, then updates the order status one order at a time.
The stored procedure is very complex, and it does move this logic out of my BLL, but to do this all from .NET could entail literally thousands of database calls.
macrap
Participant
870 Points
174 Posts
Re: Business Layer in stored procedure?
Jan 19, 2006 08:17 AM|LINK
I've been using NHibernate for some time now and think it's probably the best open source solution. It has a steep learning curve though. If you already know it, I would stay with it.
If you want to start out a bit softer, then you could try Paul Wilson's or mapper, it has good community support and is fairly easy to learn.
In some situations I wouldn't rule out using a stored procedure, but it all depends on the type of applications you make. When that is said, I haven't used a sp for a couple of years now ;)
Sane Productions
- Because it's too easy being insane
www.sane.dk