How would you structure a large project with most of the business logic already inside stored procedures?
Here is a little bit of background :
We are moving from classic ASP to ASP.NET (VB) and pretty much all the business logic is inside stored procedures. Getting the logic out of there is pretty much impossible since my boss doesn't want to (too expensive, takes too long, no "real" added value).
I was thinking about making a presentation layer made of aspx pages, a business logic / data access layer that would basically get the data and interact with the existing stored procedures and a business entities layer that would be made of classes (for entities and collections) containing the information to interact between those two layers.
The reason I wanted to make those layers was to be able to reuse most of the code without duplicating it.
I would like to have your opinion on how you would structure the new application.
Be careful not to be over enthusiatic about "getting the logic out of" the stored procedures. Stored procedures have a valid role in the many applications. If used wisely, they are often the best place for encapsulating certain logic. A good answer regarding the use of stored procedures - http://stackoverflow.com/questions/4040426/use-of-storedprocs-in-an-application/4040466#4040466
On the .NET side, your design sounds reasonable. Your DAL can wrap around the stored procedure layer and abstract the persistence of your business objects. If you still require a seperate 'business logic' layer then this ought to be seperate from the DAL.
For the front end, you may want to consider ASP.NET MVC rather than ASP.NET webforms. MVC is a pattern which fits far more naturally with a web page based application and is often an easier migration target for classic ASP sites.
I would create a data access layer based on Linq2SQL or Entity Framework, where you could reference/map your existing stored procedures (also user defined functions) as well as your tables.
well your idea is prefectly right ..Business logic should not be in Store procedure.. i am not amteur i m have vast experience in development and now i m working on project which have all business logic in SP' even more then 1000 lines are there and also dynamic sql queries are there and belive me i challenge to any one genius that you cant debug the SP's A single line change in sp's is pain and it consume lot ot time to understand the effect and change.
well better DAL seprate from SP
It's a good idea to separate out the business logic from the data access code...especially if it is in the stored procedures. The problem, however, is that your boss probably does not see eye to eye with you. He sees just moving the asp code into asp.net without modifying the back end. It is going to be expensive and time consuming to rearchitect the system...and there is a lot of potential for introducing bugs, etc.
The first step is trying to convince your boss that there is value in doing something like this.