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

  • Thread starter Thread starter trint
  • Start date Start date
T

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
 
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 :)
 
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)
(e-mail address removed)
 
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
 
Peter said:
I guess its all a matter of personal preference really. I think the
general consensus that the best practices is to use stored procs.
Click to expand...

no, it's a myth that stored procs are 'better'.
The advantage of stored procs is that they're compiled (I believe), and
Click to expand...

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

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

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

Frans

--
 
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
 
Frans,
no, it's a myth that stored procs are 'better'.
Click to expand...

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
 
Back
Top