Mixing stored procedures business logic and ORM_问答_开发者

The company I work for develops a large application which is almost entirely based on stored procedures.

We use classic ASP and SQL Server and the major part of the business logic is contained inside those stored procedures.

For example, (I know, this is bad...) a single stored procedure can be used for different purposes (insert, update, delete, make some calculations, ...). Most of the time, a stored procedure is used for operations on related tables, but this is not always the case.

We are planning to move to ASP.NET (WebForms) in a near future.

I have read a lot of posts on StackOverflow recommending that I move the business logic outside the database. The thing is, I have tried to convince the people who takes the decisions at our company and there is nothing I can do to change their mind.

Since I want to be able to use the advantages of object-oriented programming, I want to map the tables to actual classes. So far, my solution is to use an ORM (Entity Framework 4 or nHibernate) to avoid mapping the objects manually (mostly to retrieve the data) and use some kind of Data Access Layer to call the e开发者_运维百科xisting stored procedures (for saving).

I want your advice on this. Do you think it is a good solution? Any ideas?

Edit: Should I just go with a standard DataTable / DataRow approach?

In My Opinion

Stored procedures are your friends for many, many reasons. I enforce performing all db operations in stored procs in my projects, and lock down the application's account on the SQL server to only allow it to execute stored procedures (no direct table access of any kind). Still, mixing updates/deletes in the same proc strikes me as poor practice (though I tend to combine insert/update within the same procedure).

Note also: whatever logic is in the stored procedures won't have to be reimplemented as you move from ASP to ASP.NET, since it exists outside of the web code. +100 points for keeping queries where they belong.

Common "wisdom" these days indicates that you should only use your database server as an object store, which I vehemently disagree with. I've had several projects that, after years in service, suddenly needed to share data and logic with other apps written in different languages. Having most of the db code actually in the database, and outside of the application code, made this very straightforward and minimized code duplication between projects.

Mixing an ORM with stored procs sounds like a good way to lose weight and your hair at a young age.

Switching an existing app from your current data model to an ORM, on top of the complexity of moving from ASP to ASP.NET MVC, is adding a lot of variables and points of failure to an already-challenging task. If you can't present a convincing argument to the Powers That Be that an ORM is a good idea, you might ask yourself why that is.

I think it is a good solution. Just be careful with side effects you might get if you don't carefully inspect what triggers might be doing to your table data.

On the other hand, for some kind of applications, I still think having business logic on the Database side is a very good solution. Taking it out of the Database is not always the best choice. There are a lot of things to consider.

What are the advantages of object-oriented programming you expect to receive?

When people talk about the advantages of OOP, they usually mean centralizing business logic or building meaningful abstractions around related operations and data. Putting business logic in stored procedures undermines those abstractions and disperses business logic.

If your team intends to keep business logic procedures, you should at least consider keeping things that way: make your code procedural so there's only one place to look for your logic.

Having said that, it's quite common to use an ORM to map your data to data transfer objects (that don't have any significant behavior or logic) for use in your web site. Dino Esposito's Pros and Cons of Data Transfer Objects covers your question quite well - just think of your stored procedures as the service layer.

It entirely depends on the business needs. How much of the business logic in stored procedures, and the dependencies between multiple stored procedure to establish business work flows matters.

Scalability suffers big time. Dependent logic can block CPU use while processes wait to finish. Parallization is important for service driven architectures, where the database needs are basically read cache to improve performance. Why beat one CPU box and block and clog the work flows, where it can be distributed?

I think the arguments have to take these into considerations without losing hair at any age.