Why use stored procedure in C#?

[Jason Huang]

Hi,

In my C# Windows Form application project, I have done all queries on my
codes.
Now I wanna try using the Stored Procedure.
But I am not clear about why using the stored procedure.
Would some one give me some advice?
Thanks for help.

Jason

[Anil Kumar]

Hello,
Surprised by comments, the reasons of using stored procedure is
because they are stored as compiled form in sql server. looks simple, but
imagine how much time in your life you spent for the compilation of a
program will make some sense. As well as SP execute in parellel by SQL
server, means if a multithreading app or many app ask for different SP then
they executed in parelled rather than sequential. More importantly also the
sql server keep those proc, (frequently used) in its cache, so that even
loading of SP from HardDisk is escaped.

plus plus many more reason, as alredy mentioned etc.

Anil Kumar
(Project Lead)
Meritech Software Pvt. Ltd.
Chandigarh
(INDIA)

[Frans Bouma [C# MVP]]

Anil Kumar wrote:

Hello,
Surprised by comments, the reasons of using stored procedure is
because they are stored as compiled form in sql server. looks simple,
but imagine how much time in your life you spent for the compilation
of a program will make some sense.
Untrue, stored procedures aren't stored in compiled form, they're
re-compiled every time IF there's no execution plan in the cache,
EXACTLY the same as when I send SELECT * FROM Customer WHERE CustomerID
= @ID to the server. If I send that query AGAIN, it's also not compiled
again, the execution plan is then reused. See BOL about execution plans
and caching.
As well as SP execute in parellel
by SQL server, means if a multithreading app or many app ask for
different SP then they executed in parelled rather than sequential.
A SP isn't something special. the query inside a proc can also be send
to it through an external program and it's executed exactly the same.
More importantly also the sql server keep those proc, (frequently
used) in its cache, so that even loading of SP from HardDisk is
escaped.
till it's kicked out of the cache (execution plan that is), or a
recompile is necessary because you needed an IF statement because you
can't have flexible where clauses in the proc based on a broad range of
variables in your main app.
plus plus many more reason, as alredy mentioned etc.

yeah yeah. You can jump up and down all you want to keep the myth
alive, that doesn't make it true, Anil :)

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Michael.Suarez]

Some very good reasons to use stored procedures instead of hard-coding
your SQL in the client apps:

1) If you need to update/enhance/fix bugs in your SQL statements, you
simply drop and re-create the proc once its been tested and you are
done. Having to go back into the client to update the SQL means having
to go back into the code, re-compiling, re-deploying the app.

2) Having all of your queries in stored procedures promotes
re-usability. If you create another app that will ultimately call the
same query, no need to re-write it, it's already there.
2)b) the same can be said about views... BUT... you have much more
control in your SP's being as you can optimize them how you want. Views
are going to use things like left outer joins, or join several tables
in the same statement, which is less than ideal.

3) The ability to unit test SP's is huge. If you get an unexpected
error in your program and you know it came from an SP, you can just go
straight to that sp, and continue to tweak it and execute it until
you've solved your problem.

That being said, I highly recommend utilizing SP's in your Forms
application.