Why use Input Parameters?

  • Thread starter Thread starter Michael
  • Start date Start date
M

Michael

Coming from an ADO Background, I got by ok without using Parameters when I
executed SQL Statements. I would just dynamically create the sql statement,
inserting text between single quotes here and there, and send her off to the
server, and voila, It worked.

Now I'm learning ado.net, and many examples I see show using parameters to
pass input values to the command object. I also see examples just like the
old way I did it.

What would be the advantage of using Parameters for Input? It just seems to
me to be too much of a hassle to set them up when I can just concatenate a
sql string together much easier.

So is there any big advantage I'm missing when it comes to using parameters?

Thanks,
--Michael
 
Couple of reasons: If you the query is very large, string concatenation
becomes more of an expensive operation.

Let's say that you are inserting 100 records, and one of the columns is of
type Text. All the records need to have the same value for this column (for
some reason). Setting the parameter once, isn't that expensive.
Concatenating 100 strings, with this potentially huge string in one of them,
will become more of an issue.
 
Ok,

So should I go to the trouble of specifying the sqldbtype and size, or will
a name/value suffice? Here is the only values going into my parameters right
now.

New SqlClient.SqlParameter("@employer_name", employer_name.Text)
 
Security, Speed, and coding elegance/clarity . Avoid dynamic sql like the
plague..it sucks in just about every regard.
 
The most serious reason is that string concatenation which includes user
input of any kind opens your application up to SQL injection attacks. We are
seeing this more and more as hackers pound on every conceivable application
they can find.
Using typed parameters virtually eliminates this problem.
Parameters do improve performance and remove overhead caused by string
concatenation.
They also deal with more complex issues such as the O'Malley problem and
date formatting. Parameter queries are also optimized and cached more
efficiently. So yes, there are a lot of good reasons to use Parameters.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
MVP, hRD
www.betav.com
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 can think of very, very, very few reasons to ever use dynamic SQL
Query strings, but surprisingly there are many who are still using
them and teaching others to do the same.




Security, Speed, and coding elegance/clarity . Avoid dynamic sql like the
plague..it sucks in just about every regard.
Click to expand...

Otis Mukinfus
http://www.otismukinfus.com
 
Back
Top