Where to move the business logic when moving it out of the database

Question

I have a CRUD-heavy ASP.NET application with all the business logic in Stored Procedures.

As an example, there is an UPDATE stored procedure that's ~500 lines long and contains large amounts of conditional logic, referencing multiple tables & UDFs. The proc takes in the field name being updated and the new value, sets a bunch of declared variables, does a bunch of validation and creates a dynamic SQL statement to do the update. Once size fits all. It's big and confusing.

I would like to move the business logic over to the .NET side to make it easier to manage/update, test and put under source control.

My question is this: where should this business logic go?

Say I have a PurchaseOrder object with a property called 'Factory'. If the Factory gets changed, I need to make sure the new factory assigned makes the product that's on the PurchaseOrder, that it has pricing, and that there is a minimum quantity requested based upon that factory, etc. All these validations require inquiries in the database.

Should I have the PurchaseOrder object's Factory setter be responsible for doing the data validation via an 'isFactoryValid' method/property that makes the multiple calls to a generic data access object then do the update if it is?

Or do I create a PurchaseOrder/Database 'proxy' object that's responsible for handling just PurchaseOrder-related data access. In this case, would I have an 'isFactoryValid' method in the proxy that's called by the PurchaseOrder's setter and then a call to the proxy's update method?

How do I determine if I need to worry about increasing traffic to the database with all these extra calls?

Answer 1

One way to do it: You have a data layer in .net (one or multiple data classes) with an interface for the layer... then you have a business layer that performs the business logic using the interface. http://en.wikipedia.org/wiki/Multitier_architecture

Answer 2

You can also transform your business logic into chunks of reusable web services. WCF provides great tooling support.

Answer 3

There are two main patterns that are widely used to implement persistence logic out of the DB:

• ActiveRecord Pattern - Persistence logic is in your domain object.
• Repository Pattern - A separate object or layer handles the data access -- your "proxy" concept addresses this.

The trick with both objects is knowing when to make a trip to the database and knowing when not to. For example, there will be redundant validations that will be done between the DB and the domain layer, e.g., even before you make a DB call you should evaluate for not null values, truncate strings to length, etc. Only after these checks have been made should a call to Save in the db be made.

There are also a wide range of strategies available to increase performance or minimize database trips, like lazy loading, transactions, and the like.

Answer 4

This will depend upon the object model that you've created and how you've let your caller decide which Factory will be the new Factory to process the PurchaseOrder.

For example, if you give your caller a list of Factories they can pick from, you could filter the list to only those that support the product associated with the existing PurchaseOrder (I'm assuming you're edited an existing order). If you want to have the PurchaseOrder validate that the Factory can process the order, I would have the setter on the PurchaseOrder call a method on the Factory (something like CanProcessOrderFor(product, quantity)).

I'm assuming that you will have had to do a database query already to get the list of Factories and the PurchaseOrder. I would have the query for the Factory objects return their list of supported products and current quantities (or minimum order -- whatever your logic needs to be).

A good ORM like NHibernate will let you cache some of these results to minimize roundtrips if this is a common scenario.