Is it a good approach to query the database only through stored procedures?

Question

When I am developing an ASP.NET website I do really like to use Entity Framework with both database-first or code-first models (+ asp.net mvc controllers scaffolding).

For an application requiring to access an existing database, I naturally thought to create a database model and to use asp.net mvc scaffolding to get all the basic CRUD operations done in a few minutes with nearly no development costs.

But I discussed with a friend who told me that accessing data stored in the database only through stored procedures is the best approach to take.

My question is thus, what do you think of this sentence? Is it better to create stored procedures for any required operations on a table in the database (e.g. create and read on this table, update and delete only on another one, ...)? And what are the advantages/disadvantages of doing so instead of using a database-first model created from the tables in the database?

What I thought at first is that it double costs of development to do everything through stored procedures as you have to write these stored procedures where Entity Framework could have provided DbContext in a few clicks, allowing me to use LINQ over Entities, ... But then I've read a few stuff about Ownership Chains that might improve security by setting only permissions to execute stored procedures and no permissions for any operations (select, insert, update, delete) on the tables.

Thank you for your answers.

Answer 1

following are some Stored Procedure advantages

• Encapsulate multiple statements as single transactions using stored procedured
• Implement business logic using temp tables
• Better error handling by having tables for capturing/logging errors
• Parameter validations / domain validations can be done at database level
• Control query plan by forcing to choose index
• Use sp_getapplock to enforce single execution of procedure at any time

in addition entity framework will adds an overhead for each request you make, as entity framework will use reflection for each query. So, by implementing stored procedure you will gain in time as it's compiled and not interpreted each time like a normal entity framework query.

The link bellow give some reasons why you should use entity framework

http://kamelbrahim.blogspot.com/2013/10/why-you-should-use-entity-framework.html

Hope this can enlighten you a bit

Answer 2

So I'm gonna give you a suggestion, and it will be something I've done, but not many would say "I do that".

So, yes, I used stored procedures when using ADO.NET.

I also (at times) use ORM's, like NHibernate and EntityFramework.

When I use ADO.NET, I use stored procedures.

When you get data from the database, you have to turn it into something on the DotNet side.

The quickest thing is to put data into a DataTable or DataSet.

I no longer favor this method. While it may make for RAPID development ("just stuff the data into a datatable")......it does not work well for MAINTENANCE, even if that maintenance is only 2-3 months down the road.

So what do I put the data into?

I create DTO/POCO objects and hydrate the data from the database into these objects.

For example.

The NorthWind database has

Customer(s)
Order(s)
and OrderDetail(s)

So I create a csharp class called Order.cs, Order.cs and OrderDetail.cs. These ONLY contain properties of the entity. Most of the time, the properties simple reflect the columns in the database for that entity. (Order.cs has properties, that simulate a Select * from dbo.Order where OrderID = 123 for example).

Then I create a child-collection object

public class OrderCollection : List<Order>{}

and then the parent object gets a property.

public class Customer ()
{
/* a bunch of scalar properties */
public OrderCollection Orders {get;set;}
}

So now you have a stored procedure. And it gets data.

When that data comes back, one way to get it is with an IDataReader. (.ExecuteReader).

When this IDataReader comes back, I loop over it, and populate the Customer(.cs), the Orders, and the OrderDetails.

This is basic, poor man's ORM (object relation mapping).

Back to how I code my stored procedures, I would write a procedure that returns 3 resultsets, (one db hit) and return the info about the Customer, the Order(s) (if any) and the OrderDetails(s) (if any exist).

Note that I do NOT do alot of JOINING.
When you do a "Select * from dbo.Customer c join dbo.Orders o on c.CustomerID = o.CustomerId, you'll note you get redundant data in the first columns. This is what I do not like.

I prefer multiple resultsets OVER joining and bringing back a single resultset with redundant data.

Now for the little special trick.

Whenever I select from a table, I always select all columns on that table.

So whenever I write a stored procedure that needs customer data, I do a Select A,B,C,D,E,F,G from dbo.Customer where (......)

Now, alot of people will argue that. "Why do you bring back more info than you need?"

Well, real ORM's do this anyway. So I am poor-man reflecting this. And, my code for taking the resultset(s) from the stored procedure to turn that into instances of objects........stays consistent.

Because if you write 3 stored procedures, and each one selects data from Customer table, BUT you select different columns and/or in a different order, youre "object mapper" code needs to have a method for each stored procedure.

This method of ADO.NET has served me well. And, once my team swapped out ADO.NET for a real ORM, and that transition was very pain free because of the way we did the ADO.NET from the get go.

Quick rules of thumb:

1.  If using ADO.NET, use stored procedures.
2.  Get multiple result-sets, instead of redundant data via joins.
3.  Make your columns consistent from any table you select from.
4.  Take the results of your stored procedure call, and write a "hydrater" to take that info and put into your domain-model as soon as you can.  (the .cs classes)

That has served me well for many years.

Good luck.

Answer 3

Yes, it's a good approach.

Whether it's the best approach or not, that depends on a lot of factors, some of them which you don't even know yet.

One important factor is how much furter development there will be, and how much maintainence. If the initial development is a big part of the total job, then you should rather use a method that gets you there as fast and easy as possible.

If you will be working with and maintaining the system for a long time, you should focus less on the initial development time, and more on how easy it is to make changes to the system once it's up and running. Using stored procedures is one way to make the code less depending on the exact data layout, and allows you to make changes without a lot of down time.

Note that it's not neccesarily a choise between stored procedures and Entity Framework. You can also use stored procedures with Entity Framework.

Answer 4

In my opinion :

1. Stored Procedures are written in big iron database "languages" like PL/SQL or T-SQL
2. Stored Procedures typically cannot be debugged in the same IDE your write your UI.
3. Stored Procedures don't provide much feedback when things go wrong.
4. Stored Procedures can't pass objects.
5. Stored Procedures hide business logic.

Source : http://www.codinghorror.com/blog/2004/10/who-needs-stored-procedures-anyways.html

Answer 5

Its a cost benefit analysis. Being a DB focused guy, I would agree with that statement. It is best. It also makes you code easier to read (no crazy sql statements uglifying it). Increased performance with cached execution plans. Ease of modifying the querying without recompiling the code, eetc.

Many of the ppl I work with are not all that familiar with writing SPROCs so it tends to be a constant fight with them use them. Personally I dont see any reason to ever bury SQLStatments in your code. They tend to shy away from them b/c it is more work for them up front.

Answer 6

This is primarily an opinion based question and the answer may depend on the situation. Using stored procedure is definetely one of the best ways to query the database but since the emergence of Entity Framework it is widely used. The advantage of Entity Framework is that it provides a higher level of abstraction.

Entity Framework applications provide the following benefits:

• Applications can work in terms of a more application-centric conceptual model, including types with inheritance, complex members,
  and relationships.
• Applications are freed from hard-coded dependencies on a particular data engine or storage schema.
• Mappings between the conceptual model and the storage-specific schema can change without changing the application code.
• Developers can work with a consistent application object model that can be mapped to various storage schemas, possibly implemented in
  different database management systems.
• Multiple conceptual models can be mapped to a single storage schema.
• Language-integrated query (LINQ) support provides compile-time syntax validation for queries against a conceptual model.

You may also check this related question Best practice to query data from MS SQL Server in C Sharp?