Solved

stored procedure vs sql statements in asp.net application

Posted on 2010-09-08
8
440 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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 72 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 72 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 72 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 2

Assisted Solution

by:murphomatic
murphomatic earned 71 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 71 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 71 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 71 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

816 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now