Hi there, this one used to be a hot topic and I was wondering what peoples opinions where now. The big question is; Where do I put my business logic, in the BLL assembly or in StoredProcs? Consider this scenario. I have a business service for maintaining Users
in my application. There is a method on this to Delete a user, but users can only be deleted if say, they are marked as active. So, the business rule is -> Delete only if the active flag is set to true. The Delete method on the BLL component takes the id of
the user to be deleted. In the BLL I could do one of two thing; I could read the user's active flag based on the id given, test is for being true and then delete the user. Or I could call a DeleteUser stored procedure that only performs the delete if the active
flag is true. The difference between these two methods is that the first required two trips to the database where as the second only requires one. I hope you can see what I'm getting at. Why not perform as many checks like the one above as close to the database
as possible? Although, having said that it is splitting the business logic, some may be in the BLL assembly, some may be in SPs. Any thoughts? Graham
::Where do I put my business logic, in the BLL assembly or in StoredProcs? This depends. Performance: Database SP Maintenance: BLL. THe big tradeof. I prefer to have little in the SP, actually not to have an SP at all, becaust this means the app can easily
be moved from SQL Server to Access to Oracle without a rewrite. ::The difference between these two methods is that the first required two trips to the ::database where as the second only requires one. The question is whether this makes a difference for the
overall application that is grave enough to actually be nagging - or not. Normally it does not really - there are some very special cases where it does, though. ::Why not perform as many checks like the one above as close to the database as possible? ::Although,
having said that it is splitting the business logic, some may be in the BLL ::assembly, some may be in SPs. You touch the problem here. This means having a DLL and having a lot of SP's, this means maintaining the SP's in a badly integrated environment (source
control integration). It means debugging them, though this is doable. The question is: is it worth the trouble? Depends on the application. My opinion: no, it is not - unless you run into extreme performance problems, and THEN you look at it. I try to keep
my business logic in my BLL, actually in my business objects.
Thanks for the reply. I understand what you're saying and it makes sense. Let me extend the question slightly if I may. If we were to introduce transactions. Now my scenario is that I have a Delete method on the User service. However, there are other tables
that relate to the user so I need to delete these foreign key rows first (else referencial integrity will get me!!). Now, I have an option. I could have one DAL method that was named something like DeleteUser and let it perform multiple deletes, alternatively
if we continue the idea earlier what I should do is perform multiple deletes from the business method, and it's the business method that aquires a transaction and passes it to each of the delete operation in the DAL. It is therefore the business method that
will know if a commit or rollback is required. Do you agree? Graham
I will give you a short answer: 1. Keep them in SP if you don't have a good O/R mapper. do it in code will be a disaster. 2. Keep them in code if you have a good O/R mapper, that what they are for. I am in route one, and almost ready to shift to route two.
Calvin
::Do you agree? Nope, both methods are broken :-) The best way is if the BLL turns over to the DAL a list of objects / entries to be deleted :-) ONE call to the DAL for the complete transaction. Naturally, if you need to integrate with other transactions this
has to be possible, but I think that this can be shoveled to the DAL in one call. At least this is what I do in our O/R mapping framework - one call do the DAL to apply all changes (insert, update, delete).
I'm responding to Thona's original response to this post. I think he summarized the trade-offs very well. But I've found that when integrating two seperate applications that the sp route was much faster to develop and appears to have created much less code
to maintain. We've just completed the integration of the source code for these forums in to another application. Both applications came with their own registration, authentication and permission schemes. We did not want to rewrite all the code in these forums
to use the reg, auth, permission scheme of the other (much larger) application. So we relaxed the identity definition for userid in the forums and developed a few stored procedures and triggers to keep the users and usersinroles tables for the forums in synch
with the members and memberroles tables in the other application. Of course we also had to pull out the ui pages for account maintenance in the forums since this was now tied to the other app's objects and processes. It was quick painless and effective. You
could argue with my assertion that there is less code since all of the code and sps for the forums app are now in place as part of our baseline, but we had very little new code to develop. How have others handled this sort of thing?
I was recently at a developer's conference and was able to ask some questions to a panel of some pretty well known developers. (Jeff Richter, Fritz Onion, Charles Petzold, and many others.) I asked what they thought of Yukon, and whether or not they were worried
about business logic in the stored procedures getting far more common when people can embedded any CLR-happy language into a sproc. The most common reply was actually very shocking to me. They basically had no problem whatsoever with embedding business logic
into sprocs. Ever since my college days, one resounding design principal was the logical seperation of your tiers, especially when it came to the database. "Little or no logic in the sprocs!" was what I was always taught. Well, these guys made some pretty
good points to the contrary. First, it's typically easier to maintain logic in a sproc than it is in compiled code. It's also not really a violation of the tiered model, as you can think of sprocs as just an extension of your business layer that lives very
close to the datasource for speed reasons. Lastly, one primary reason to not have business logic in the database is for portability. It makes it more difficult to switch backends if you have lots of logic in your sprocs. The thing is, very seldom is this a
realistic requirement. Sure, lots of clients say they want it because they want to have the option of changing stuff, just in case. But few if any will ever actually switch backends, and the costs (both in development time and speed) of creating a cross-database
application are usually enough to convince most to drop that request. I think they're right.
I am going in parts here :-) @mbinnix: ::How have others handled this sort of thing? We go for integratable objet model here, 100% - using an O/R mapper. There is NO (!) code for the database - nothing. Clases are designed in an editor, shells are generated
and then basically all SQL is generated on thy fly by a complete automatic DAL, supporting SQL Server and Access (Oracle in the works). It takes a vision, though - this is much over the head of most developers, to let thedb interaction just go and just HAVE
the objects when they need them. We even went so far to implementan object based query system. ::We did not want to rewrite all the code in these forums to use the reg, auth, permission ::scheme of the other (much larger) Thank god you had this hack. The main
issue here is that these forums here are very badly designed from a DB and application point of view, especially when you take this little item called "integration" into account. Normally an application should provide hooks for this. SOMETIMES going into the
database and bypass the application is nice, but - well - you could always do this. No excuse for a badly written app. @RMD: ::First, it's typically easier to maintain logic in a sproc than it is in compiled code. You are joking, right? My code is completly
Source Control integrated, I can just get a defined version out in a second, compile it and it is there. This level of integration is just not there with SP's. Unless you talk about SQL Server, single stepping a stored procedure just does not work, not in
the same solution that issues the call to the SP. And then you have to redo the SP for every database. This type of integration is something the SQL people do not even dare to dream of. Yes, you are joking. I have customers that deliver a product for SQL Server
and Oracle, and I do a system now for SQL Server and MS Access. Without (!) changing one line of code in my program, without rewriting anything. THIS is maintainable. We work on having the db structure autogenerated (with changes) from a schema that is compiled
into the BLL's dll and then to even be able to load initial data (from a ressource embedded or attached to the dll). Means: you ship the logic dll's, put a tool into the app directory, point it to a database and everything is set up - regardless whether you
talk about Oracle or SQL Server. THIS is maintainability. ::It's also not really a violation of the tiered model, as you can think of sprocs as just an ::extension of your business layer that lives very close to the datasource for speed reasons. It is, itf
you work in modern terms. Why? Because your business layer consists of - objects. And the SP's are not object oriented and are in the wrong layer, actually. No, SPEED is one reason to fgo with SP's, security another one. BUT - you PAY a hefty price in maintainability.
You forego everythin that most IDE's give you. THis MAY change with Whidby and YUKON, but then it is - well - MS specific. Dont get me wrong. I LOVE the VM in YUKON. I can write triggers that I do write (occasionally for some things) finally in C#. I can use
the CLR to do some things in a SP that are tendious to do in SQL (string manipulation, encryption etc.). But I would not call this an architecture that I would like to mintain for 99% of my applications. There I want business objects (as opposed to a set based
system and access methods, a.k.a. stored procedures) and I want to deal with my databasse as little as possible.
Thona, regarding the transactions in the BLL or the DAL. Well, after some investigation this afternoon I came to the exact same conclusion, thanks :-). Exposing a transaction to the BLL from the DAL is quite tricky (and clunky). In the end I decided that as
all the transaction updates / inserts or deletes should be one atomic operation where the transaction is started and commit asap then calling a method in the DAL that performs all the database changes is much simpler and neater. Thanks for your input.
You are joking, right? No. If I was, I would put a smiley face. My code is completly Source Control integrated, I can just get a defined version out in a second, compile it and it is there. You can do exactly the same thing with stored procedures.
In fact, I do this all the time. This level of integration is just not there with SP's. Unless you talk about SQL Server, single stepping a stored procedure just does not work, not in the same solution that issues the call to the SP. Well, I am talking
about SQL Server. I will grant you that debugging sprocs in other databases is not nearly as easy as it is in SQL Server, so in those situations you have a point. Otherwise, debugging a sproc is just as easy as any other code.
And then you have to redo the SP for every database. This type of integration is something the SQL people do not even dare to dream of.
Yes, but the VAST majorty of projects do NOT require your data source to be interchangable. I'm sure there are some types of projects that have this requirement, but they are easily the special case scenario. At any rate, I already address cross-database
support in my previous post, which you either missed or ignored. It is, itf you work in modern terms. Why? Because your business layer consists of - objects. And the SP's are not object oriented and are in the wrong layer, actually. Why do you use objects
in your business layer? It's because you want to completely abstract away the datasource so that a developer doesn't need explicit knowledge of how/where the data is stored to work with that data. Guess what, the same thing can be achieved with a light wrapper-style
business object and a set of stored procedures. In fact, this is exactly how all the reference applications Microsoft has ever created look. Petshop, Dwamish, etc. They all have business objects that basically lightly wrap stored procedures that contain most
of their business logic. This approach almost always results in a faster application, and certainly results in less development time. Obviously, it doesn't always work. My current project absolutely requires a O/R mapping framework for multiple reasons. I
simply can't take the approach above, but if I could have, I would have. But my application is pretty specialized, and I bet 90% of the applications being created these days would work just fine with the approach that Microsoft recommends.
No, SPEED is one reason to fgo with SP's, security another one. BUT - you PAY a hefty price in maintainability. You forego everythin that most IDE's give you. THis MAY change with Whidby and YUKON, but then it is - well - MS specific. I say you won't
pay a hefty price at all, at least not with MS centric stuff. I completely agree that some things are hard to do in sprocs, but then you do those things in your light weight business object wrapper classes. Best of both worlds.
GAllwood
Member
183 Points
57 Posts
Thought on business logic in StoredProcs
Sep 17, 2003 08:45 AM|LINK
thona
Member
20 Points
2923 Posts
Re: Thought on business logic in StoredProcs
Sep 17, 2003 10:32 AM|LINK
GAllwood
Member
183 Points
57 Posts
Re: Thought on business logic in StoredProcs
Sep 17, 2003 02:14 PM|LINK
cklein
Participant
1561 Points
363 Posts
Re: Thought on business logic in StoredProcs
Sep 17, 2003 03:58 PM|LINK
http://www.raincoder.com
Equal parts art and science
Email: cguo@raincoder.com
thona
Member
20 Points
2923 Posts
Re: Thought on business logic in StoredProcs
Sep 17, 2003 04:19 PM|LINK
mbinnix
Member
230 Points
46 Posts
Re: Thought on business logic in StoredProcs
Sep 17, 2003 06:13 PM|LINK
RMD
Member
734 Points
172 Posts
Re: Thought on business logic in StoredProcs
Sep 17, 2003 06:32 PM|LINK
thona
Member
20 Points
2923 Posts
Re: Thought on business logic in StoredProcs
Sep 17, 2003 07:14 PM|LINK
GAllwood
Member
183 Points
57 Posts
Re: Thought on business logic in StoredProcs
Sep 17, 2003 09:06 PM|LINK
RMD
Member
734 Points
172 Posts
Re: Thought on business logic in StoredProcs
Sep 18, 2003 04:49 AM|LINK