Using Stored Procedures as the Business Logic Layer

Question

The company I'm working for is currently using Stored Procedures (in the MsSQL server backend) as their Business Logic Layer. The actual Business Logic DLL just calls the sProcs and basically manages the UI (events, data binding, etc.)

I think there's something wrong in the setup, although I'm not sure how to explain it to my colleagues. Btw, the system works.

Is the "Best Practices" in my workplace wrong? Or am I just overthinking this?

Answer 1

GaiusSensei - it's perfectly fine to work in that way as long as the business domain is able to handle seismic shifts in business practices. i think the debate is still rife between SP's and BLL dll's and no doubt, there will be plenty on both sides in this thread. However, from my own experience over a range of projects in the past 10 years, here are my observations supporting the BLL dll approach:

• logic contained in the BLL can be 'agnostic' of the storage medium and therefore more flexible to change (tho how often this happens is debatable)
• Finer grained control over business permissions ACROSS a range of applications that rely on the datastore. By this I mean the core tables whose integrity must be maintained at a level specific to it's use within the business application in question.
• BLL logic can be encapsulated in self contained classes that can be re-used in other areas of the business and or project. The class can even be written as a sealed class or extensible depending on your target 'audience'
• Unit testing - this (in my experience) is a black art if used inside SP's. under java/c# etc, this is an standard and some would say mandatory practice now.
• Maintainability. By keeping well organised interfaces within a BLL dll scenario, you make it easy for supporting developers to extend your classes without breaking existing logic
• Portability. your BLL (depending on the language implementation) can be hosted on a variety of platforms. Likewise, the injection of the implemetation of the datastore can literally be anything from an xml file to mysql, mssql postgres etc, etc.
• Standardisation. The data architect can define exactly how each data element should be taken from the database and how each item should be saved (tho this would be better located in a DAL dll). Thus, the cost of entry for new developers as well as seasoned, on the project is much reduced.

The list goes on but, these are my top of the head PROS for adopting a BLL approach.

Looking fwd to the many spins on this one :)

jim

[edit] - i'd also add that this BLL should NOT emit any UI information either, other than (as you mention) to convey events etc. each UI layer (relevant to the target device -browser/mobile device/factory) should reference the BLL and do it's own 'thang' with the data. I'd further add that below the BLL would be your DAL layer. this layer could be considered a 1-1 reference with the underlying datastore.

Answer 2

We do that.

That's because we support scenarios where users connect to the db using programs other than the intended software (like, SQL Management studio, osql and Excel).

When you directly connect to a database which is no more than data storage, you can mess everything up as there's no rules that would stop you. These rules only exist inside the program that uses this database, and if you don't use that program, you can use you I-can-write-to-this-table permissions to do stupid (or fun) things.

When you only have permissions to execute stored procedures, you can't.
I personally think it's a better approach.