Inline SQL or stored procs for my C# windows app?

[Burt]

I'm a stored proc guy, but a lot of people at my company use inline
sql in their apps, sometimes putting the sql in a text file, sometimes
hardcoding it. They don't see much benefit from procs, and say anyway
they're are db specific- what if we change from SQL Server to Oracle
one day?

What say you experts? Is this one of those "no right answer"
questions?

Thanks,

Burt

 

[Guest]

The RPC (stored proc) path through SQL Server is a lot faster than the
language ("text SQL") path in most cases.

In addition, there are a number of very sophisticated things you can do
inside the database - transactions, UDFs, triggers, multistatement procs,
Table variables, cursors and so on that are difficult or impossible to do
with text sql even if it is parameterized.
My 2 cents.
Peter

 

[Nicholas Paldino [.NET/C# MVP]]

Burt,

Well, it kind of is one of those "no right answer" questions. It always
depends on context.

If you have a routine that is going to be called over and over again,
then there really is no reason to not have it in a stored procedure.

Yes, you will have to migrate these if you move to another database, but
honestly, how hard is it to move a stored procedure from one database to
another? If you have dynamic sql, then you are going to run into the same
problem, you will have to change the dynamic sql generator (or strings for
the sql) to make sure you are not using any database-specific features.

Just because you are using dynamic sql doesn't mean that you are not
using database-specific features in the dynamic sql.

Honestly, I would use something like the Data Access Application Block
in the Enterprise Framework that is offered by Microsoft. It will help
mitigate using stored procedures and changing from one database to another
(you won't have to worry about changing how you call stored procedures from
managed code, for example), and you can use the object model exposed by it
to generate dynamic sql that will work across any database that has a
provider for the DAAB, assuming you don't use any database-specific features
in your SQL.

Personally, I think that dynamic sql has a place, but for established
logic which will be called repeatedly, stored procedures are the way to go.
For me, I would use dynamic sql for things like persisting an object model
to the database, but not performing multi-statement logic against the
database.

Hope this helps.

 

[j1mb0jay]

I'm a stored proc guy, but a lot of people at my company use inline
sql in their apps, sometimes putting the sql in a text file, sometimes
hardcoding it. They don't see much benefit from procs, and say anyway
they're are db specific- what if we change from SQL Server to Oracle
one day?

What say you experts? Is this one of those "no right answer"
questions?

Thanks,

Burt

Due to SQL injection becoming a large problem to some databases, i would
suggest sticking with
stored procs. As all you have to do is run the stored procs you can disable
other query methods.

 

[Nicholas Paldino [.NET/C# MVP]]

JJ,

Stored procedures don't necessarily prevent SQL injection attacks. It
is using the object model for passing parameters to a stored procedure which
prevents SQL injection attacks.

You can use that same object model to create parameterized dynamic sql
which is immune to injection attacks.

Conversely, you can actually execute the stored procedure by stringing
together a statement with the parameters (without using the object model)
and be vulnerable to an injection attack.

 

[Andy]

Due to SQL injection becoming a large problem to some databases, i would
suggest sticking with
stored procs. As all you have to do is run the stored procs you can disable
other query methods.

Stored procedures offer no protection against sql injection attacks;
they are just as prone as dynamically built sql. The problem arises
from how you build the statement; if you are concatinating strings to
insert user supplied values, you are vunerable to sql injection
attacks. If you are instead leaving placeholders in the sql string,
and using something which implements IDataParameter to add the user
supplied values, you are immune from attacks.

 

[Susie DBA [MSFT]]

develop it with inline and then move it to sprocs

for example.. using dreamweaver this strategy is actually quite nice

of course.. dreamweaver includes wizards for building webpages; MS
doesn't give a crap about web dev.. I mean.. Visual Interdev?
FrontPage? SharePoint?

Screw M$

 

[Kevin Spencer]

I agree with Peter. Another consideration is good app design/layering. Since
the format of the query is dependent upon the structure of the database, a
Stored Procedure can behave like a "function" that is called from the
database. If designed well, you can make changes to the underlying structure
of the database, leave the SP parameters the same, and the change will be
transparent to the application, thereby preventing you from having to
rebuild the application.

--
HTH,

Kevin Spencer
Microsoft MVP

Printing Components, Email Components,
FTP Client Classes, Enhanced Data Controls, much more.
DSI PrintManager, Miradyne Component Libraries:
http://www.miradyne.net

 

[Burt]

Honestly, I would use something like the Data Access Application Block
in the Enterprise Framework that is offered by Microsoft. It will help
mitigate using stored procedures and changing from one database to another
(you won't have to worry about changing how you call stored procedures from
managed code, for example), and you can use the object model exposed by it
to generate dynamic sql that will work across any database that has a
provider for the DAAB, assuming you don't use any database-specific features
in your SQL.

My next question was actually about the DAAB. At my current company
we're using it. At my last job, we just had a shared class called
"Data Access" which all programmers used. It was one level, and just
had some basic "GetDataReader", "GetDataSet", etc methods. The DAAB
sure does seem like a lot of overhead. Occasionally it will throw an
error which I'll have to Step through through a few Microsoft code
files to figure out. But I see there are benefits, like the one
above.

Burt

 

[ocdecio]

I'm a stored proc guy, but a lot of people at my company use inline
sql in their apps, sometimes putting the sql in a text file, sometimes
hardcoding it. They don't see much benefit from procs, and say anyway
they're are db specific- what if we change from SQL Server to Oracle
one day?

What say you experts? Is this one of those "no right answer"
questions?

Thanks,

Burt

I'll tell you what's been working for me for years: dynamically
generating SQL from my object model. Which implies first of all that I
do have an object model. I use two functions (static to boot because I
don't want to create objects more than necessary) :

- Read (which generates SELECT)
- Write (which generates either INSERT, UPDATE or DELETE).

I get 90% of my app working this way; the other 10% is custom sql or
stored procedures that I write after a lot of performance profiling.
If performance is ok, no sprocs - and complete database independence.
Also, I found it pretty hard to inject SQL because all of it is
generated. And I get most of the benefits of stored procedures since
of course I use parameterized prepared statements. I only update
columns that I actually changed in the objects, which increases
concurrency (two users are able to change different columns of the
same row at the same time). I don't need to pass any parameters to
stored procedures. My systems have never been so clean and
uncluttered. Life is good.

I would stay away from the DAAB but the microsoft buffs may disagree
(reasonable people really disagree over this very topic). Also, if you
sold your soul to a database vendor, well, maybe better stick with
it.

Otavio

 

[Ravichandran J.V.]

1. The inline SQL statement, hard coded, makes maintenance in the log
run a little tedious.

2. Stored procs have a distinct advantage because they are executed in
the DB and hence, is out of process from the currently executing code.
Plus, it is easily maintainable as you would not need to touch the code
but change it in the DB.

3. No matter what the DB if you use stored procs all you need to do is
to retype the stored proc in the other database. The call to the stored
proc remains the same with only the connection string to be changed.
Even this can be separated from code as you could put it in the config
file (suitable encrypted in 2.0)!

with regards,


J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com

 

[Andy]

1. The inline SQL statement, hard coded, makes maintenance in the log
run a little tedious.

It depends on how you build things. Calling a proc can also make
maintenance more difficult as well.

2. Stored procs have a distinct advantage because they are executed in
the DB and hence, is out of process from the currently executing code.

All Sql is executed by the database engine, dynamic or sp call. This
is just plain wrong.

Plus, it is easily maintainable as you would not need to touch the code
but change it in the DB.

Again, not necessarly true either. Procedures can change what they
return, or what arguments they take, and you'll be back changing the
code.

3. No matter what the DB if you use stored procs all you need to do is
to retype the stored proc in the other database. The call to the stored
proc remains the same with only the connection string to be changed.
Even this can be separated from code as you could put it in the config
file (suitable encrypted in 2.0)!

What if the next new db doesn't support stored procedures at all? And
again, this isn't always true. Sql Server uses @varName for parameter
names. Other Dbs simply use ? for place holders, and you must add
parameters in the proper order.

 

[Jon Skeet [C# MVP]]

1. The inline SQL statement, hard coded, makes maintenance in the log
run a little tedious.

Otavio said that 90% of his SQL is auto-generated, not hard-coded. In
my experiences with Hibernate in Java, there was even less hard-coded
SQL than that.

2. Stored procs have a distinct advantage because they are executed in
the DB and hence, is out of process from the currently executing code.

As Andy pointed out, the SQL itself is always executed in the DB.
Where's the advantage?

Plus, it is easily maintainable as you would not need to touch the code
but change it in the DB.

Why do you believe it's easier to change DB code than server-side code?
(I'm not talking about clients talking directly to the DB, but
something like a web service.)

3. No matter what the DB if you use stored procs all you need to do is
to retype the stored proc in the other database. The call to the stored
proc remains the same with only the connection string to be changed.
Even this can be separated from code as you could put it in the config
file (suitable encrypted in 2.0)!

Um, you're kidding, right? Stored procs vary *massively* between
different databases. Ever tried putting complex SQL stored procs on an
Oracle database?

If you're talking about using the same kind of database, just pointing
at a different box, I don't see that there's any benefit in using
stored procs over dynamically generated SQL that's relevant to what you
put in the above paragraph. Whether you generate the SQL or call a
stored proc, you've still just got to change the connection string.

 

[Guest]

The RPC (stored proc) path through SQL Server is a lot faster than the
language ("text SQL") path in most cases.
Yep

In addition, there are a number of very sophisticated things you can do
inside the database - transactions, UDFs, triggers, multistatement procs,
Table variables, cursors and so on that are difficult or impossible to do
with text sql even if it is parameterized.

Neither transactions, UDFs or triggers is tied to SP's.

Arne

 

[=?ISO-8859-1?Q?Arne_Vajh=F8j?=]

If you have a routine that is going to be called over and over again,
then there really is no reason to not have it in a stored procedure.

That depends a lot on portability requirements, application design etc..

Yes, you will have to migrate these if you move to another database, but
honestly, how hard is it to move a stored procedure from one database to
another?

It can be very hard. Some databases does not have SP's. Other databases
use SP's a lot different from the Microsoft/Sybase way.

If you have dynamic sql, then you are going to run into the same
problem, you will have to change the dynamic sql generator (or strings for
the sql) to make sure you are not using any database-specific features.

Not necesarrily.

Just because you are using dynamic sql doesn't mean that you are not
using database-specific features in the dynamic sql.

It is by no way give, but it is possible to avoid those.

Honestly, I would use something like the Data Access Application Block
in the Enterprise Framework that is offered by Microsoft. It will help
mitigate using stored procedures and changing from one database to another
(you won't have to worry about changing how you call stored procedures from
managed code, for example), and you can use the object model exposed by it
to generate dynamic sql that will work across any database that has a
provider for the DAAB, assuming you don't use any database-specific features
in your SQL.

There are other ways of implementing data access. But a lot of them are
worse than DAAB, so if in doubt DAAB is a good starting point.

Arne

 

[Paul Shapiro]

Security is simpler to implement if your db doesn't allow any dynamic SQL.
Then all db access is via SP, you can assign permissions to your desired SQL
roles, and any access not specifically allowed can be prevented.
Paul Shapiro