Parameter Object really necessary?

  • Thread starter Thread starter Shawn Ferguson
  • Start date Start date
S

Shawn Ferguson

I had a question about the Parameter object, is it really necessary or could you develop your query to include the parameters like this?

sql = "EXEC dbo.UpdateDepartmentalPages " &_
"@URL= '" & URL & "', " &_
"@Department= '" & Department & "', " &_
"@Remove= '" & Remove & "', " &_
"@DepartmentalPagesID= '" & DepartmentalPagesID & "'"

Then run it through the command object? Please let me know if it is possible. I'm still learning about .NET and it's one of those things that I've been curious about.

Thanks,
 
Yes you can but you'll have to double singles quotes, take care about the
date format, be cautions about those country where "," is the decimal point,
possible SQL injection attacks etc...

My personnal preference is to use the SqlCommand object and its parameters
collection rather than executing a plain text SQL statement....

Patrice


--

"Shawn Ferguson" <[email protected]> a écrit dans le message de
I had a question about the Parameter object, is it really necessary or could
you develop your query to include the parameters like this?

sql = "EXEC dbo.UpdateDepartmentalPages " &_
"@URL= '" & URL & "', " &_
"@Department= '" & Department & "', " &_
"@Remove= '" & Remove & "', " &_
"@DepartmentalPagesID= '" & DepartmentalPagesID & "'"

Then run it through the command object? Please let me know if it is
possible. I'm still learning about .NET and it's one of those things that
I've been curious about.

Thanks,



----------------------------------------------------------------------------
----


I had a question about the Parameter object, is it really necessary or could
you develop your query to include the parameters like this?

sql = "EXEC dbo.UpdateDepartmentalPages " &_
"@URL= '" & URL & "', " &_
"@Department= '" & Department & "', " &_
"@Remove= '" & Remove & "', " &_
"@DepartmentalPagesID= '" & DepartmentalPagesID & "'"

Then run it through the command object? Please let me know if it is
possible. I'm still learning about .NET and it's one of those things that
I've been curious about.

Thanks,
 
Like Patrice said, you don't even need to use paramaters at all in many instances. However whether or not you want to is a different issue altogether. I've been around a few people who INSIST on concatenating sql instaed of using params and when they do use params, they do it as the example above did. They'll say stuff like "I'm not lazy, I'll just escape them myself...." Every single time I've heard this, they've either forgot to do it a few months later, or someone else maintaining their code forgot it. Most of the times, the customer discovered the problem and a whole new dll needed to be deployed. And at a minimum, if you do escape everything manually, all you're doing is reinventing the wheel b/c Micorosft's param object already handles this, has been tested and while it may not be 100% impervious to every form of bad data, it's almost a guarantee that ti's more secure than anything the average developer can write. Plus it's quick and easy to do.

The long and short of it is that you can use other methods, but there is a LOT of cost and no benefit so I'd recommend against it.

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
I had a question about the Parameter object, is it really necessary or could you develop your query to include the parameters like this?

sql = "EXEC dbo.UpdateDepartmentalPages " &_
"@URL= '" & URL & "', " &_
"@Department= '" & Department & "', " &_
"@Remove= '" & Remove & "', " &_
"@DepartmentalPagesID= '" & DepartmentalPagesID & "'"

Then run it through the command object? Please let me know if it is possible. I'm still learning about .NET and it's one of those things that I've been curious about.

Thanks,
 
Shawn,

Do you know what is so nice, when you are used to it, than it is so easy to
use.

Is this not much nicer than all that strugling with date formats or whatever
\\\
Dim cmd As New OleDbCommand("SELECT User, ID, Date FROM Work WHERE Date
BETWEEN @begindate And @enddate", objConn)
cmd.Parameters.Add("@begindate", cdate(txtBeginDate.text))
cmd.Parameters.Add("@enddate", cdate(txtEndDate.text))
///

I hope this helps,

Cor
 
Dear mister Ryan,

I don't mind of something is topposted, lowposted or whatever, however a
message with rows which expand the distance almost from Amsterdam to New
York, is almost a little bit to much for my reading capabilities.

Will you please be so kind to post next time not in this format.

(Yes I know I am not obliged to read your message however mostly I do that
with much pleasure)

:-))

Cor
 
Cor - I'm not sure what you mean. I posted this one at the top like I
always do.. If there's a formatting issue, I can't see it on my end and
don't think I did anything different, but if you could send me a screen
shot, I could then see it and look into the problem.

Thanks for bringing it to my attention ;-)

Bill
 
Huh? Are they complaining that the response (our answer) is at the top of
the message and not at the bottom? I find it a royal PITB to scroll down to
the end to answer or to find an answer. I'm too old and ornery to change.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
I agree, I hate scrolling down. In this case, I *think* that there may have
been some formatting issue b/c when I looked at the message, you could see
the whole thing, Original question and my answer, in one window.

I know that some people (in Comp.Os.Linux.Advocacy, it gets UGLY if you top
post and I mean UGLY) get really really mad about top posting though, when
to me, bottom posting is a lot more annoying. Bottom POsting advocates say
that in long conversations, top posting makes it impossible to read, but
IMHO, it's the exact opposite, WIth top posts, you always know where to look
and don't have to scroll down or exclude signatures or anything else.

Agreeing with you also has the side benefit of putting me in good company a
la "Great minds think alike" ;-)
 
Bill,

I got the message as one long string in my OE.
It was the poster which has done that before you wich was using Rich Text
box and you had just answered that.

Cor
 
Sahil.,
WEE WEE WEEE WEEE .. Newsgroup cops strike again !!!
Click to expand...
I see that you want to act as the newsgroup MP.

Please do that in another newsgroup.

Bill and I are to long active in this newsgroup.

At least I don't to be corrected by a newbie as you.

Cor
 
Bill,

What you do after that in past I attended you on the fact that there are no
rules.

Where I like topposting (and to do what Armin likes, delete irrelevant
pieces because of dialup users).

Only the ones who don't like topposting (in email context not newsgroup
context) are attending you every time on that.

:-)

Cor
 
I see that you want to act as the newsgroup MP.
What is MP?
Please do that in another newsgroup.
Click to expand...
Others don't have an annoying self appointed policeman.
Bill and I are to long active in this newsgroup.
At least I don't to be corrected by a newbie as you.
Click to expand...
But what is Cor to do if the newbie keeps correcting him?

- SM
 
W.G. Ryan eMVP said:
Cor - I'm not sure what you mean. I posted this one at the top like I
always do.. If there's a formatting issue, I can't see it on my end and
don't think I did anything different, but if you could send me a screen
shot, I could then see it and look into the problem.

Thanks for bringing it to my attention ;-)

Bill
Click to expand...
No formatting problem here. Possibly Cor's news client?
I'm using OE (no guffaws, please).
 
Bill,

It is not a big issue, not worth so many messages. If I make my box smaller
than it is readable.

It resizes with the box, I did not think about that.

It is nice to see that accordion.

I only wanted to make you attend on it.

:-)

Cor
 
I don't personally care as long as the question is answers. But to dispise
a perfectly good answer and complain about only a top/bottom post is plain
rude. It gets the entire off topic almost instantly and can almost forget
about much meaningful talk afterwards. Nonetheless, I saw your post that he
mentioned and I didn't have to scroll to read it. Everything was fine. It
could just be the particular reader he was using.


Thanks,
Shawn
 
I've never "struggled" with date formats or whatever. Simply knowing the
rules was good enough that for the past 3 years I've never had a problem
using parameters, I've never had a problem knowing when to cast them, or how
to use them. However... another person on the team using a string concat
almost exclusively and then there were injection problems. Won't have to
worry about it if you just use parameters with the command object. Sure,
the code isn't as clean (but clean is relative to the one who thinks it is
clean).

But, we also have a data wrapper (similar, but much cleaner than the data
access block) that everything uses and so we really just say:

dataObject.AddParameter("@param1", value1);
dataObject.AddParameter("@param2", value2);

DataTable result = dataObject.ExecuteTable(storedProc);

Nothing wrong with that, and of course, you can use overloads with
AddParameter to specify the type, size, direction, or lengh of value if you
want.

In the end end, it is a matter of taste and preference. Of couse, what is
more important than abiding by your own styles on a team, is to have a team
standard and it is always done the same, predictable, way, otherwise you end
up with 10 different ways of doing the same thing (as is the case in our
company) and it is a royal pain to switch from one project to another,
copy/paste, re-use, etc. There are some places where we use the string
concat, mainly, for any of our 23 different search screens, where we
dynamically generate a sql statement based on the search criteria. But we
have to do some serious pre-parsing to help mitigate injection attacks and
other such stuff first. It can be convenient. But in the end, regarding
stored procedure parameters, we always just use the Command object and
Parameters, that's what they are there for, and they already do much of what
we would have to write code for to do anyway, so why not just use them?

Besides, the concern isn't whether *we* who wrote them/works with them has
problems, but whether everyone else will, new people will, or when you
leave, whether it'll leave others in a rut until they learn all the *quirks*
that you failed to realize were there because you know your style so well,
others, they don't, eventaully work it out. That's a problem we're
struggling with now is that the original people aren't here anymore, and
they constantly argued how easy it was to do this or that with data access,
but once they left, others had to decipher all the *quirks* and rules and
conditions in which something actually works. Doesn't matter how
straight-forward something *appears*. It'll always be straight-forward to
the one who wrote it. The point is, don't do what is good for you, do what
is good for the company in the long run, and what is more productive, and
what more people, including beginners, are more likely to be familiar with,
or be ready to document it so thoroughly that it because counter productive
to do it a certain way because of constantly having to document document
document.

I've been in some interesting discussions/debates with other team members on
this issue. The one wants it this way, the other wants it that way and
there's not agreeing with anyone else. In the end, it is important to
establish company standards and that way, whether anyone likes it or not,
things will be done a certain, predictable way.


Thanks,
Shawn
 
Back
Top