Do I specify a length for text parameters?

  • Thread starter Thread starter Jeff Johnson [MVP: VB]
  • Start date Start date
J

Jeff Johnson [MVP: VB]

When setting up an SqlCommand object, is it required and/or recommended to
use the size parameter when the data type of the parameter is text
(SqlDbType.Text)? I'm converting an old program and I'm a ways away from
testing it, so I figured I'd ask first. In ADO specifying a size (for
adLongVarChar) was required. Just wondering if things had changed.
 
When setting up an SqlCommand object, is it required and/or
recommended to use the size parameter when the data type of the
parameter is text (SqlDbType.Text)? I'm converting an old program and
I'm a ways away from testing it, so I figured I'd ask first. In ADO
specifying a size (for adLongVarChar) was required. Just wondering if
things had changed.
Click to expand...

You are not required to set the field size. Setting the size ensures that
the parameter value cannot be set any larger than the specified size. If a
value that is too large is set into the parameter ADO.NET will not fail if
size was not set, however the database will still give you an error.

At some point you should have validation that the data passed into the
database is valid and of an appropriate length/size. Setting the Size
property is one way of doing this.

If you use a business layer in your application that already does
validation on all the column values, then the database should be configured
to accept that valid data (proper field sizes for those columns). So you
would never have a size related error, and thus it doesn't matter if you
set the parameter size.

--
Michael Lang, MCSD
See my .NET open source projects
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)
 
Back
Top