Are there any benefits in specifying full details in SQL Parameter?

  • Thread starter Thread starter James
  • Start date Start date
J

James

I had asked this question before (long ago) but not sure if the answer still stands.

When I create a SqlParameter object I can one of the many overloaded constructors.

SqlParameter param = new SqlParameter("@customerid", 1001);

SqlParameter param = new SqlParameter("@customerid", SqlDbType.Int, 4, ParameterDirection.Input, ..., 1001);

I prefer to use the second approach. But I hear arguments that first one much more cleaner, readable and maintainable...

What is your opinion? Any official stand on this from the ADO.NET group @MS?

James
 
There are some details that are not needed if you're ok with the defaults.
For example, the default parameter direction is "input", if you know that
your parameter is an input parameter then there is no need to indicate it.

On the other hand, parameter metadata might help the server by avoiding
conversions when parameter arrive to the server. For example, if you create
a parameter and set the value to a string, we'll infer the type to be
SqlDbType.NVarChar and the length to be the length of the string. If on the
server the expected type is VARCHAR instead of NVARCHAR, and some differen
length, the code will still work, but an implicit conversion will take
place. So in general it's good to indicate metadata (type, length,
precision, scale) when available. In many scenarios it won't make any
difference, but in some will.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


I had asked this question before (long ago) but not sure if the answer still
stands.

When I create a SqlParameter object I can one of the many overloaded
constructors.

SqlParameter param = new SqlParameter("@customerid", 1001);

SqlParameter param = new SqlParameter("@customerid", SqlDbType.Int, 4,
ParameterDirection.Input, ..., 1001);

I prefer to use the second approach. But I hear arguments that first one
much more cleaner, readable and maintainable...

What is your opinion? Any official stand on this from the ADO.NET group @MS?

James
 
Pablo,
On the other hand, parameter metadata might help the server by avoiding
conversions when parameter arrive to the server. For example, if you
create a parameter and set the value to a string, we'll infer the type to
be SqlDbType.NVarChar and the length to be the length of the string. If on
the server the expected type is VARCHAR instead of NVARCHAR, and some
differen length, the code will still work, but an implicit conversion will
take place. So in general it's good to indicate metadata (type, length,
precision, scale) when available. In many scenarios it won't make any
difference, but in some will.
Click to expand...

Although I have not any opinion about this.

Are you not indirectly telling that the first approach (which I find bad
look ing) makes you less database server type dependent?

Cor
 
Yep, you do make your program less database independent, but you may get
some extra performance back. The actual choice depends on what are your
actual application needs.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.
 
Pablo,

What you meant by database dependent/independent? I am using the
classes in SqlClient namespace.
What type of applications would fit in each categories in your opinion?


Thanks.
James
 
Back
Top