Why use stored procedure in C#?

  • Thread starter Thread starter Jason Huang
  • Start date Start date
J

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
 
Jason Huang said:
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.
Click to expand...

1. To isolate the app from the database schema.
2. To allow simulation of inheritance and other OO features in the database
(i.e. a derived class will typically map to two tables and SPs can hide
this)
3. To allow the app to have access restricted to only those operations and
data that it needs.
 
Seems very comprehensive!


Nick Hounsome said:
1. To isolate the app from the database schema.
2. To allow simulation of inheritance and other OO features in the
database (i.e. a derived class will typically map to two tables and SPs
can hide this)
3. To allow the app to have access restricted to only those operations and
data that it needs.
Click to expand...
 
Nick said:
1. To isolate the app from the database schema.
Click to expand...

since when do you need a proc for that?
2. To allow simulation of inheritance and other OO features in the
database (i.e. a derived class will typically map to two tables and
SPs can hide this)
Click to expand...

I can do that perfectly fine without a proc. If you truly want it
inside the db, you can also do it with a view. A view is even more
practical as well, as you don't have to hardcode filter statements in
the proc.
3. To allow the app to have access restricted to only those
operations and data that it needs.
Click to expand...

you can also do that with different methods. (role based security).

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#)
------------------------------------------------------------------------
 
Nick,

a couple of very good reason for using stored procedures rather than SQL
Statements coded in code are.

1) The processing of the query is done by the server where the stored
procedure is located, thus, in theory speeding up processing times. Now if
your running one or 2 statements you might not notice this, but if your
running hundreds of thousands of transaction an hour, then you will notice
the time saving.

2) If there is a bug in your SQL statement it is quite easy to fix these
within a stored procedure without having to recompile your app and then
redistribute it to the client.

Regards
Scott Blood
C# Developer
 
Frans Bouma said:
since when do you need a proc for that?
Click to expand...

How else?
I can do that perfectly fine without a proc. If you truly want it
inside the db, you can also do it with a view. A view is even more
practical as well, as you don't have to hardcode filter statements in
the proc.
Click to expand...

Additions and updates can be a problem through a view.
you can also do that with different methods. (role based security).
Click to expand...

Much harder to set up, document and maintain.
 
I can do that perfectly fine without a proc. If you truly want it
Additions and updates can be a problem through a view.
Click to expand...

Well, I think we're talking about apples and oranges here. Using a DataSet
and a set of views *can be* quite useful. But not for everything. So, I
don't think we need to debate on the usefulness of DataSets and Views, but
it is certain that they are not useful for everything. I believe the real
issue here is using Stored Procedures versus hard-coded SQL statements in
the business logic. And it is inarguable that Stored Procedures are far
superior to inline SQL statements for all the reasons originally stated.

--
HTH,

Kevin Spencer
Microsoft MVP
Professional Numbskull

Show me your certification without works,
and I'll show my certification
*by* my works.
 
Wow... finally a sensible discussion about SP vs inline dynamic. I get soooo
fed up with hearing developers saying how much quicker SP's are compared to
inline sql when in fact in 90%+ of cases the difference is nano-seconds. But
yes there are other advantages which have been very elequently pointed out.
However, there are advantages to dynamic inline sql.

I recently built a framework for an NHS trust where they wanted to be able
to have classes that they specify the tablename and field names that they are
interested in and the framework would generate a class based on those
properties, in this instance you can ONLY achieve this with dynamic inline
sql as the connection string was specified in the constructor of the class
and could be changed at runtime, therefore there is no way that you could use
sp's as you would have to deploy the sp's to the databae before you could use
it.

Also for lookup data, dynamic sql is far, far simpler. In a previous job a
guy we will call JimBob wrote an sp which depending on what type of lookup
you were looking for built up a sql string inside the stored proc to select
the various fields from the various tables, however, it was a nightmare as he
had so many double quotes to worry about that it ended up working brilliantly
until one day someone entered something with 3 single quotes in one of the
fields, which totally fubar'd it all and by the time he had written all the
checking etc into the SP it was totally unmaintainable by anyone but him.

One of the other advantages of SP's over inline is the fact you can create
global temporary tables and use cursors etc, which I'm sure is probably
possible with inline, but it would be very, very messy.

So as you have probably gathered yourself by now, it is a case, as with so
many development theologies of horses for courses, there are some situations
where inline is better, some situations where SP's are better, and some
situations where it makes no difference at all and is entirely down to what
you / the organisation prefer.

Cheerio

Ian
 
Mark Rae said:
http://www.google.com/search?source...8,GGLG:en&q="stored+procedures"+"dynamic+SQL"
Click to expand...

An interesting discussion.

I think that the disagreements come about because of differences in the type
of application that people have worked on.

If the application drives the database design then it is true that SPs are
less useful but if you are in the situation where there is a database and
all you can do is request access to it then SPs look much more appealing.
When you work on an app where you have a database and several independent
apps all be upgraded more or less independently SPs are the only way to go.
 
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)
 
Anil said:
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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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.
Click to expand...

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#)
------------------------------------------------------------------------
 
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.
 
Back
Top