CODEWITHSUNDEEP
asp.netarchitecturebusiness-logicapplication-design
Ali
Ali

Reputation: 3615

Where should I write the business logic? In the front end (business layer) or in a stored procedure?

I am writing an ASP.NET application with a SQLServer database in which I have to calculate rates for members of my application. These calculations affect more than 10 database tables.

I believe I have two options :

  1. In the data access layer, fetch the data for a member from the first table in the database and return it to the business layer. Once there, perform some calculation on the fetched data to generate new data to be saved in a second table. Finally, call back into the data access layer to save the result. Afterwards, repeat this whole process, pulling information from the second table to calculate results to be saved in a third table, and keep doing this for all necessary tables.

  2. Use a stored procedure to encapsulate calculating and saving the new rates for a member in the correct tables within a database transaction.

Which option do you think is best and why? If I use the first option, how should I perform all of the inserts and updates in one ADO.NET transaction from the business logic layer? I am currently barred from using an ADO.NET transaction outside of the data access layer.

Upvotes: 0

Views: 1325

Answers (1)

Muthu
Muthu

Reputation: 2685

IMO it depends on how much priority needs to be given for performance and modular design.

If this was a trading application where I would have to calculate the prices instantaneously from different tables, I would use a stored procedure

  • Better performance for certain amount of load but when it gets too much queries, then a distributed database becomes essential
  • One disadvantage is that if you want to move to a different database in the future (in most cases people don't), then you have to port the stored proc to the new ones

The other option I would have is to keep most of the values in a distributed cache (like memcache) and do the calculations in a business layer.

  • The values will be pre-populated into the cache and the cache will be refreshed as and when there are changes.
  • This gives flexibility in terms of removing the database dependency.

But it seems to me that your operations are quite heavily DB dependent in functionality and suggest a stored procedure route. If you think the second option of cache is possible, it is worth a try.

Upvotes: 0

Related Questions