Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

stored procedure vs sql statements in asp.net application

Posted on 2010-09-08
8
Medium Priority
?
465 Views
Last Modified: 2013-11-11
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
Comment
Question by:TrialUser
8 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 288 total points
ID: 33627855
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
 
LVL 2

Assisted Solution

by:bureshd
bureshd earned 288 total points
ID: 33628012
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
 
LVL 12

Assisted Solution

by:w00te
w00te earned 288 total points
ID: 33628359
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Assisted Solution

by:murphomatic
murphomatic earned 284 total points
ID: 33628825
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
 
LVL 3

Assisted Solution

by:vusov
vusov earned 284 total points
ID: 33628837
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
 
LVL 2

Assisted Solution

by:junimation
junimation earned 284 total points
ID: 33631050
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
 
LVL 13

Assisted Solution

by:Ashok
Ashok earned 284 total points
ID: 33708486
We used Linq-To-Sql with Stored procedures in all Web applications.
0
 

Author Closing Comment

by:TrialUser
ID: 33781750
thanks fo al the suggestions
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question