• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

stored procedure vs sql statements in asp.net application

1) What are the adv/disdv of calling stored procedure vs sql statetments inside the asp.net web application to execute sql statements.
2) does Linq to sql address the disadv of using sql statements inside the .net application (like security)
0
TrialUser
Asked:
TrialUser
7 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I just LOVE having stored procedures, as it makes it possible to change the underlying sql without having to redeploy the whole application, if the change is just to fix minor things, or improve performance on database level ...
0
 
bureshdCommented:
Stored Procedures are more secure in my opinion vs inline sql statements in code. You also have the compiling issues. When a stored proc is used, the code just knows how to handle a certain return value. This makes it possible that you can make any changes to the stored proc on the DB side without having to make changes on the code side which would cause the need for a recompile.
0
 
w00teCommented:
The real main advantages of using stored procedures are:

(1) Ease of update & redistribution
(2) Security

If you are the only person using the stored procedure (i.e. you will only use it in one place in one application, and noone else will have any reason to be using that stored procedure) then there is no difference really.

The ease of update and redistribution comes from the fact that as long as the stored procedure provides the same interface (inputs, outputs, and name), you can change it however you want.  You can optimize it, make it do logging, change locking, or anything in the stored procedure and everyone using it would always by default get the newest version, because its built into the database rather than your code.   Obviously if its only used in one place, this is no advantage for you.

From a security standpoint, you're always safer when things are running on your server (much less chance of anyone influencing the code).  If you're executing things in your code-behind server code then you're pretty much as safe as if you did it in your database (maybe a little less so, but there could be all kinds of dispute over why, its close enough though either way!).

-w00te
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
murphomaticCommented:
There are 3 advantages to using stored procedures vs. embedding SQL in your code:

1.  Performance: SQL Server compiles stored procedures and determines an execution plan appropriate for that procedure.  So your SQL statement within the stored procedure executes faster.  SQL states are compiled on the fly an always have a new execution plan built before they are run.

2.  Security: Stored procedures allow you to use typed parameters, where using embedded SQL in your code, you're likely concatenating strings to come up with a full SQL statement.  Creating SQL statements by way of concatenation often leaves you open to SQL injection attacks.

3.  Portability:  Stored procedures make your overall solution more modular, and therefore - more maintainable.  As a previous commenter said:   It's easy to make simple adjustments to the stored procedure vs. editing embedded SQL and redistributing your entire application.
0
 
vusovCommented:
Linq to Sql it's some wrapper over sql statements, so security issues will be the same.
Stored procedures is more complex development way, so if your DB and Web Server on the same machine, using Linq to Sql approach will more efficient.
0
 
junimationCommented:
Let me explain a bit more on Linq since I see the lack of knowledge in the given answers so far, no offence.

Number 1. It is Type safe
number 2. Abstraction - allows for development of your code and be able to upgrade it easily if the framework changes. Can't do that with sproc because you will have to change the sproc and everything tied with it.
number 3. You can debug linq with in .net debugger. sproc you are going to rely on the db vendor like ms sql
number 4. Linq is not only for ms sql it supports many different databases. sproc is not as good to port to another db.

number 5. you don't have to learn t-sql you can just learn the language linq provides which is the same as c# or vb.net


Number 6.  LINQ to SQL avoids injection by using SqlParameter in queries. User input is turned into parameter values. This approach prevents malicious commands from being used from customer input. SO NO, LINQ is not just a wrapper.

I used to be a big sproc guy, but I'm starting to lean towards LINQ as a better alternative in general. If there are some areas where sprocs are clearly better, then I'll probably still write a sproc but access it using LINQ.
0
 
AshokCommented:
We used Linq-To-Sql with Stored procedures in all Web applications.
0
 
TrialUserAuthor Commented:
thanks fo al the suggestions
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now