Stored procedures vs. command strings in c#...

[trint]

My boss wants me to use stored procedures, but I thought somehow that
using c# that
ADO.Net was better. Any help is appreciated.
Thanks,
Trint

[Morten Wennevik]

Hi Trint,

Using stored procedures there is less data that needs to be transfered
over the net as the data is processed on the sql server and only the
result is returned. Lower bandwith needed and faster result :)

On 17 Jan 2005 04:48:47 -0800, trint <tr***********@gmail.com> wrote:

My boss wants me to use stored procedures, but I thought somehow that
using c# that
ADO.Net was better. Any help is appreciated.
Thanks,
Trint

--
Happy Coding!
Morten Wennevik [C# MVP]

[Peter Chadwick (MCP)]

Hi,

I guess its all a matter of personal preference really. I think the
general consensus that the best practices is to use stored procs.

The advantage of stored procs is that they're compiled (I believe), and
hence run much faster than a query passed in a string. Plus as they are
doing mainly direct database actions it makes sense that they sit
within the database server.

However, if you are doing many calls to the db server, it may make
sense to wrap the query up into one string and pass that, as the db
server round-trips can be a bottle-neck.

Also, stored procs don't have source control, unlike c# code can have.

Personally I sit in the use stored procs camp. I believe database
functions should sit as close to the db as possible. Plus, you can
still use ADO.NET and all it's functionality with stored procedures -
the only difference being you pass a stored proc name rather than a
string of sql.

I know some developers who prefer using sql queries within their code,
but as far as I can tell it's purely because that's the environment
they are used to and like - unlike sql enterprise manager.

My tuppence worth... hope it's of some help...
Regards,

Peter Chadwick (MCP)
pe**@code-explorers.com

[trint]

Thanks,
I am sure that a guy with the compay who installed our report services
told me that if the procedure had a 'where' clause in it, report
services had to recompile it anyway...but...stored procs it is.
trint

[Cor Ligthert]

Trint,

I think almost the same as Peter, where I think for
production: command stored procedure
developing: command Strings

Have a look as well for this probably makes it all clear.
http://msdn.microsoft.com/library/de...dtexttopic.asp

Just my thought,

Cor

[Frans Bouma [C# MVP]]

Peter Chadwick (MCP) wrote:

I guess its all a matter of personal preference really. I think the
general consensus that the best practices is to use stored procs.
no, it's a myth that stored procs are 'better'.
The advantage of stored procs is that they're compiled (I believe), and
no they're not compiled, at least not on SqlServer. On DB2 they're
generated as C code which is compiled but on SqlServer and also on
Oracle, they're not compiled, but stored as is and when executed they're
compiled and the EXECUTION plan is cached. The same is done with a
parameterizied query. This means that the second time a parameterized
query is executed, it's cached execution plan is used, it's not recompiled.
hence run much faster than a query passed in a string. Plus as they are
doing mainly direct database actions it makes sense that they sit
within the database server.
Only if they are not used for CRUD operations. Procs for CRUD are more
of a burden than a help. Long running data munching procs are better
suitable in procedure form in the database, however not a lot of systems
needs these.
However, if you are doing many calls to the db server, it may make
sense to wrap the query up into one string and pass that, as the db
server round-trips can be a bottle-neck.

Also, stored procs don't have source control, unlike c# code can have.

Most DBA's write their scripts first in textfiles and store these in
sourcecontrol :)

Frans

--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

[Arjang]

If you create a well defined DB Layer for your application then yes, use
C# and keep all the DB logic out of the db.

The stored procs scatter your appliaction logic.

The Stored Procedures are good for DB Developers, where they can massage
the data all they want without any proper understanding of why the data
is being massaged.

The good thing about Stored Procedures is that they are for DataBase
side not the application side of the system.

Cheers
trint wrote:

My boss wants me to use stored procedures, but I thought somehow that
using c# that
ADO.Net was better. Any help is appreciated.
Thanks,
Trint

[Cor Ligthert]

Frans,

no, it's a myth that stored procs are 'better'.

Better is a subjective word.

Being in the sun on the "Pier" is found by most people better than in the
rain, however there are as well fishermain who wants to do that in the rain
and find that better.

:-)

Cor