Solved

stored procedure vs sql statements in asp.net application

Posted on 2010-09-08
8
437 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Assisted Solution

by:murphomatic
murphomatic earned 71 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Assisted Solution

by:vusov
vusov earned 71 total points
Comment Utility
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
Comment Utility
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
Comment Utility
We used Linq-To-Sql with Stored procedures in all Web applications.
0
 

Author Closing Comment

by:TrialUser
Comment Utility
thanks fo al the suggestions
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

728 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