Yikes! Although the first part is a matter of taste I guess, I never
thought I'd imagine someone saying that concatenating strings is less
cumbersome than using an iterative object oriented approach. If my command
takes three parameters, logicially I think adding them to a collection and
being able to retrieve them though iteration for example is much less
cumbersome. Moreover, you could possibly run into problems with double
quote characters or other such input by concatenating strings. Don't even
get me started on the immutability of strings issue ;-).
First, about the whole other collection.... every command object has a
Parameters Collection. It may be empty or may have a ton of stuff, but it
has a Paramaters collection. To that end, I don't see what "whole other
collection " you are referring to
Also the approach string concatenation leaves a lot to be desired (see
doesn't cut it) if you are using column mappings for instance. How would
you map those parameters to a datatable so that the Update/Insert/Delete
command could automatically pick up the values on update? Using the table
mappings and colum mappings is a big issue and not using them just so you
can concatenate strings isn't really a good trade off. Also, string
concatentaion is a bit clunky and if you forgot to add a singly quote or
something, you could cause an error. Depending on how you pass in the
values, you could open yourself up to Injection attacks or issues with
people who's last name may be O'Ryan or the like. Of if you had text that
had CarriageReturns in it, and that was to be stored in the db, you might
get into trouble with string concatenation.
Another issue is clarity and precision.
If I used this:
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 10).Value = "NULL"
cmd.Parameters.Add("@param2", SqlDbType.Int, 4).Value = 100
cmd.Parameters.Add("@param3", SqlDbType.Varchar, 20).Value = 200
I have complete control over what gets sent. Even if I validate at the
control /UI level, I can make sure that nothing more than 20 characters long
gets sent at @Param1for instance. This is critical to preventing Injection
attacks and many other security threats b/c you can't do much damage if I
keep the input you can enter to 6 characters for instance, it takes a few
more to write ;DELETE * FROM SystObjects. Also, as a fellow programmer, I
could see what the code is intended to do and understand it a whole lot
better.
I guess from a style perspective, to each his own. But there are many that
would argue that string concatenation is really irksome and hard to read
maintain, and not the least bit object oriented. There's no way to deal
with it iteratively.....and you run the risk of introducing some serious
security problems.
HTH,
Bill
Mad Scientist Jr said:
Could someone please explain the advantage of using the data command
parameter collection to send paramters to SQL, instead of simply
building a SQL string like this:
sSQL = "exec mysproc @param1=[param1/], @param2=[param2/],
@param3=[param3/]"
sSQL = replace(sSQL, "[param1/], "NULL")
sSQL = replace(sSQL, "[param2/], "'" & sParam2 & "'")
sSQL = replace(sSQL, "[param3/], iParam3.toString)
Isn't it a bit cumbersome to use a whole other collection, instead of
working with simple SQL strings?
I know using a separate collection is probably the "right" way, but I
just would like a solid explanation of why.
Thanks in advance...
