By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

Why use stored procedure in C#?

P: n/a
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
Mar 23 '06 #1
Share this Question
Share on Google+
12 Replies


P: n/a

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
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.


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.
Mar 23 '06 #2

P: n/a
Seems very comprehensive!
"Nick Hounsome" <nh***@nickhounsome.me.uk> glsD:SX*********************@fe1.news.bl ueyonder.co.uk...

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
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.


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.

Mar 23 '06 #3

P: n/a
Nick Hounsome wrote:

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
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.
1. To isolate the app from the database schema.


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)
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.


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#)
------------------------------------------------------------------------
Mar 23 '06 #4

P: n/a
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
"Nick Hounsome" <nh***@nickhounsome.me.uk> wrote in message
news:SX*********************@fe1.news.blueyonder.c o.uk...

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
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.


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.

Mar 23 '06 #5

P: n/a

"Frans Bouma [C# MVP]" <pe******************@xs4all.nl> wrote in message
news:xn***************@news.microsoft.com...
Nick Hounsome wrote:

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
> 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.
1. To isolate the app from the database schema.


since when do you need a proc for that?


How else?
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)


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.


Additions and updates can be a problem through a view.
3. To allow the app to have access restricted to only those
operations and data that it needs.


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


Much harder to set up, document and maintain.
Mar 23 '06 #6

P: n/a
"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
But I am not clear about why using the stored procedure.


http://www.google.com/search?sourcei...dynamic+SQL%22
Mar 23 '06 #7

P: n/a
>> 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.

Additions and updates can be a problem through a view.


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.

"Nick Hounsome" <nh***@nickhounsome.me.uk> wrote in message
news:9f********************@fe2.news.blueyonder.co .uk...
"Frans Bouma [C# MVP]" <pe******************@xs4all.nl> wrote in message
news:xn***************@news.microsoft.com...
Nick Hounsome wrote:

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
> 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.

1. To isolate the app from the database schema.


since when do you need a proc for that?


How else?
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)


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.


Additions and updates can be a problem through a view.
3. To allow the app to have access restricted to only those
operations and data that it needs.


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


Much harder to set up, document and maintain.

Mar 23 '06 #8

P: n/a
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
"Nick Hounsome" wrote:

"Frans Bouma [C# MVP]" <pe******************@xs4all.nl> wrote in message
news:xn***************@news.microsoft.com...
Nick Hounsome wrote:

"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
> 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.

1. To isolate the app from the database schema.


since when do you need a proc for that?


How else?
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)


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.


Additions and updates can be a problem through a view.
3. To allow the app to have access restricted to only those
operations and data that it needs.


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


Much harder to set up, document and maintain.

Mar 23 '06 #9

P: n/a

"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:Oo**************@tk2msftngp13.phx.gbl...
"Jason Huang" <Ja************@hotmail.com> wrote in message
news:eC*************@TK2MSFTNGP10.phx.gbl...
But I am not clear about why using the stored procedure.


http://www.google.com/search?sourcei...dynamic+SQL%22


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.
Mar 24 '06 #10

P: n/a
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)
Mar 24 '06 #11

P: n/a
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#)
------------------------------------------------------------------------
Mar 24 '06 #12

P: n/a
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.

Mar 24 '06 #13

This discussion thread is closed

Replies have been disabled for this discussion.