converting/casting before submitting

[djc]

I have typically always used one of the VB CType functions (not CType itself
but CInt, CString, etc...) to cast/convert an input value to its proper type
before passing it as a parameter to a SQL stored procedure. I recently came
accross one I did not know how to handle though... TinyInt. I have an SQL
field of type TinyInt which is an 8bit integer. I came accross a ToInt16()
vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead
and tried to submit the value as is to the stored procedure to see if there
would be a problem. The value is a string (from a
dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
took the value fine. The SQL stored procedure was expecting a TinyInt as
declared in the stored procedure. ??

1) Is there some type of auto-conversion in place? have I been wasting time
and/or slowing performance converting/casting myself all this time?

2) does the SQL stored procedure convert it itself since that is what was
declared within it?

any info would be greatly appreciated. Thanks.

 

[Karl Seguin]

SqlParameter.Value is of type Object, which suggest that everything is taken
care of internally and casting is indeed not required. Of course, to
confirm this assumption, we get Reflector and look at the disassembled code
for how SqlParameter.set_Value works.

We see that it calls something called SetTypeInfoFromComType which in turn
calls a static function GetMetaType of an internal class named MetaType.
This is the function that seems to be doing all the work..stuff like:

case TypeCode.DBNull:
throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean:
return MetaType.metaTypeMap[2];
case TypeCode.Char:
throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte:
throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte
return MetaType.metaTypeMap[20];
case TypeCode.Int16
return MetaType.metaTypeMap[0x10];


Also, FIY, TinyInt is a value from 0-255, which in .Net maps to a Byte and
not an Int16.

Karl

 

[bruce barker]

the sql sp declares the datatype. when you call the client libary, you pass
an object and tell the client lib the actual datatype of your data. the
client lib will then convert the datatype to the matching sqldatatype, and
pass sql datatype to the sqlserver. the sqlserver than converts the passed
sql datatype to the sp dataype if it can. see the sql Convert function doc's
to see the supported conversions.

-- bruce (sqlwork.com)


| I have typically always used one of the VB CType functions (not CType
itself
| but CInt, CString, etc...) to cast/convert an input value to its proper
type
| before passing it as a parameter to a SQL stored procedure. I recently
came
| accross one I did not know how to handle though... TinyInt. I have an SQL
| field of type TinyInt which is an 8bit integer. I came accross a ToInt16()
| vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead
| and tried to submit the value as is to the stored procedure to see if
there
| would be a problem. The value is a string (from a
| dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
| took the value fine. The SQL stored procedure was expecting a TinyInt as
| declared in the stored procedure. ??
|
| 1) Is there some type of auto-conversion in place? have I been wasting
time
| and/or slowing performance converting/casting myself all this time?
|
| 2) does the SQL stored procedure convert it itself since that is what was
| declared within it?
|
| any info would be greatly appreciated. Thanks.
|
|

 

[Karl Seguin]

My newsreader shows my reply to this as deleted...so I'll try again...sorry
for the possible double post:

SqlParameter.Value is of type Object, which suggest that everything is taken
care of internally and casting is indeed not required. Of course, to
confirm this assumption, we get Reflector and look at the disassembled code
for how SqlParameter.set_Value works.

We see that it calls something called SetTypeInfoFromComType which in turn
calls a static function GetMetaType of an internal class named MetaType.
This is the function that seems to be doing all the work..stuff like:

case TypeCode.DBNull:
throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean:
return MetaType.metaTypeMap[2];
case TypeCode.Char:
throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte:
throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte
return MetaType.metaTypeMap[20];
case TypeCode.Int16
return MetaType.metaTypeMap[0x10];


Also, FIY, TinyInt is a value from 0-255, which in .Net maps to a Byte and
not an Int16.

 

[djc]

Thank you for the excellently explained answer. I appreciate it greatly.

SqlParameter.Value is of type Object, which suggest that everything is taken
care of internally and casting is indeed not required. Of course, to
confirm this assumption, we get Reflector and look at the disassembled code
for how SqlParameter.set_Value works.

We see that it calls something called SetTypeInfoFromComType which in turn
calls a static function GetMetaType of an internal class named MetaType.
This is the function that seems to be doing all the work..stuff like:

case TypeCode.DBNull:
throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean:
return MetaType.metaTypeMap[2];
case TypeCode.Char:
throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte:
throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte
return MetaType.metaTypeMap[20];
case TypeCode.Int16
return MetaType.metaTypeMap[0x10];


Also, FIY, TinyInt is a value from 0-255, which in .Net maps to a Byte and
not an Int16.

Karl



--
MY ASP.Net tutorials
http://www.openmymind.net/

I have typically always used one of the VB CType functions (not CType itself
but CInt, CString, etc...) to cast/convert an input value to its proper type
before passing it as a parameter to a SQL stored procedure. I recently came
accross one I did not know how to handle though... TinyInt. I have an SQL
field of type TinyInt which is an 8bit integer. I came accross a ToInt16()
vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead
and tried to submit the value as is to the stored procedure to see if there
would be a problem. The value is a string (from a
dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
took the value fine. The SQL stored procedure was expecting a TinyInt as
declared in the stored procedure. ??

1) Is there some type of auto-conversion in place? have I been wasting time
and/or slowing performance converting/casting myself all this time?

2) does the SQL stored procedure convert it itself since that is what was
declared within it?

any info would be greatly appreciated. Thanks.

 

[djc]

My newsreader shows both posts... but since your original is showing deleted
to you I am thanking you in both places to make sure you get the proper
thanks. Thanks!

Thank you for the excellently explained answer. I appreciate it greatly.

My newsreader shows my reply to this as deleted...so I'll try again...sorry
for the possible double post:

SqlParameter.Value is of type Object, which suggest that everything is taken
care of internally and casting is indeed not required. Of course, to
confirm this assumption, we get Reflector and look at the disassembled code
for how SqlParameter.set_Value works.

We see that it calls something called SetTypeInfoFromComType which in turn
calls a static function GetMetaType of an internal class named MetaType.
This is the function that seems to be doing all the work..stuff like:

case TypeCode.DBNull:
throw ADP.InvalidDataType(TypeCode.DBNull);
case TypeCode.Boolean:
return MetaType.metaTypeMap[2];
case TypeCode.Char:
throw ADP.InvalidDataType(TypeCode.Char);
case TypeCode.SByte:
throw ADP.InvalidDataType(TypeCode.SByte);
case TypeCode.Byte
return MetaType.metaTypeMap[20];
case TypeCode.Int16
return MetaType.metaTypeMap[0x10];


Also, FIY, TinyInt is a value from 0-255, which in .Net maps to a Byte and
not an Int16.


--
MY ASP.Net tutorials
http://www.openmymind.net/

I have typically always used one of the VB CType functions (not CType itself
but CInt, CString, etc...) to cast/convert an input value to its proper type
before passing it as a parameter to a SQL stored procedure. I recently came
accross one I did not know how to handle though... TinyInt. I have an SQL
field of type TinyInt which is an 8bit integer. I came accross a ToInt16()
vb function but not one for an 8 bit 'TinyInt'. So I next just went ahead
and tried to submit the value as is to the stored procedure to see if there
would be a problem. The value is a string (from a
dropdownListBox.SelectedItem.Text). To my suprise there was no error. It
took the value fine. The SQL stored procedure was expecting a TinyInt as
declared in the stored procedure. ??

1) Is there some type of auto-conversion in place? have I been wasting time
and/or slowing performance converting/casting myself all this time?

2) does the SQL stored procedure convert it itself since that is what was
declared within it?

any info would be greatly appreciated. Thanks.

 

[djc]

great! thank you!