dbType.Binary and limit of 0-8000 bytes

[Chukkalove]

Can someone explain the following to me please, from the help files. I can't
understand what it means


DbType.Binary
Supported by the .NET Compact Framework.
A variable-length stream of binary data ranging between 1 and 8,000 bytes.
Note ADO.NET cannot correctly infer the type if the byte array is larger
than 8,000 bytes. Explicitly specify the DbType when working with byte
arrays larger than 8,000 bytes.

Its the "Explicitly specify the DbType when working with byte arrays larger
than 8,000 bytes." part that i cant understand.
Where is this specified, if im specifying it already for a byte array?

thanks

 

[Cowboy \(Gregory A. Beamer\)]

There is a byte array: byte[]
And there is the ADO.NET DbType for binary.

What it is stating is your must explicitly set a parameter to a
DBType.Binary instead of byte[] when you have a byte array larger than 8000
bytes.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

 

[Chukkalove]

Thank you for answering

I have a function with a switch statement that tests the type of an object
when it's passed to my IDbDataParameter constructing function and sets the
param.DbType accordingly.
If object type is byte[] I always set the parameter.DbType to DbType.Binary.
The help file isnt clear and may assume the reader already has knowledge.
Since writing this message, I was wondering if it's actually necessary to
set DbType at all unless you're creating a byte[] related parameter that's
over 8000 bytes in size. Is this true? And if so, then this part of the help
article should state that somewhere.

public void CreateParam(ref System.Data.IDbCommand command, string Name,
object value)
{
Hashtable data = Data;
// Add value to Data
data[Name.ToUpper()] = value;
IDbDataParameter Parm = command.CreateParameter();
Parm.ParameterName = Name;
if (value == null)
{
Parm.DbType = DbType.Object;
Parm.Value = null;
}
else
{
string str = value.GetType().ToString().ToUpper();
switch(str)
{
case "SYSTEM.UINT16" arm.DbType = DbType.UInt16; break;
case "SYSTEM.UINT32" arm.DbType = DbType.UInt32; break;
case "SYSTEM.DATETIME"arm.DbType = DbType.DateTime; break;
case "SYSTEM.STRING" arm.DbType = DbType.AnsiString;break;
case "SYSTEM.BYTE" arm.DbType = DbType.Byte; break;
case "SYSTEM.BYTE[]" arm.DbType = DbType.Binary; break;
case "SYSTEM.SBYTE" arm.DbType = DbType.SByte; break;
case "SYSTEM.INT32" arm.DbType = DbType.Int32; break;
case "SYSTEM.SINGLE" arm.DbType = DbType.Single; break;
case "SYSTEM.FLOAT" arm.DbType = DbType.Single; break;
case "SYSTEM.DOUBLE" arm.DbType = DbType.Double; break;
case "SYSTEM.BOOLEAN" arm.DbType = DbType.Boolean; break;
// cnh When the value type is dbnull, you cant work out what type it is
// Setting type to DbType.Object seems to work ok
case "SYSTEM.DBNULL" arm.DbType = DbType.Object; break;
default:throw new Exception("Type '" + str + "' missing from
BaseTable.CreateParam");
}
Parm.Value = value;
}// else
command.Parameters.Add(Parm);
}// function

 

[Cowboy \(Gregory A. Beamer\)]

Necessary? No, but explicit code has value other than necessity. If you set
the DbType on every parameter, you are showing exactly what you mean to the
person who has to take up the code when you leave.

Looking at the code you have, I assume you have a check to ensure the type
of the object is correct for the parameter somewhere else in the program.
For example, consider this stored procedure:

CREATE PROCEDURE dbo.TestMe
(
@myID int
, @myByteArray binary
)
AS

....

You could legally code this without compiler errrors:

byte[] byteArray = new byte[100];
int id = 1;

SqlCommand cmd = new SqlCommand(sqlString, conn);
CreateParam(cmd, "@myID", byteArray);
CreateParam(cmd, "@myByteArray", id);

This is the reason the MS blocks query sprocs and cache the parameter
collection. You then get a blow up before attempting a submit to the
database.

This is not a major deal overall, so don;t recode simply to add protection
on the code layer.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

*************************************************
Think outside of the box!
*************************************************

Thank you for answering

I have a function with a switch statement that tests the type of an
object when it's passed to my IDbDataParameter constructing function and
sets the param.DbType accordingly.
If object type is byte[] I always set the parameter.DbType to
DbType.Binary.
The help file isnt clear and may assume the reader already has knowledge.
Since writing this message, I was wondering if it's actually necessary to
set DbType at all unless you're creating a byte[] related parameter that's
over 8000 bytes in size. Is this true? And if so, then this part of the
help article should state that somewhere.

public void CreateParam(ref System.Data.IDbCommand command, string Name,
object value)
{
Hashtable data = Data;
// Add value to Data
data[Name.ToUpper()] = value;
IDbDataParameter Parm = command.CreateParameter();
Parm.ParameterName = Name;
if (value == null)
{
Parm.DbType = DbType.Object;
Parm.Value = null;
}
else
{
string str = value.GetType().ToString().ToUpper();
switch(str)
{
case "SYSTEM.UINT16" arm.DbType = DbType.UInt16; break;
case "SYSTEM.UINT32" arm.DbType = DbType.UInt32; break;
case "SYSTEM.DATETIME"arm.DbType = DbType.DateTime; break;
case "SYSTEM.STRING" arm.DbType = DbType.AnsiString;break;
case "SYSTEM.BYTE" arm.DbType = DbType.Byte; break;
case "SYSTEM.BYTE[]" arm.DbType = DbType.Binary; break;
case "SYSTEM.SBYTE" arm.DbType = DbType.SByte; break;
case "SYSTEM.INT32" arm.DbType = DbType.Int32; break;
case "SYSTEM.SINGLE" arm.DbType = DbType.Single; break;
case "SYSTEM.FLOAT" arm.DbType = DbType.Single; break;
case "SYSTEM.DOUBLE" arm.DbType = DbType.Double; break;
case "SYSTEM.BOOLEAN" arm.DbType = DbType.Boolean; break;
// cnh When the value type is dbnull, you cant work out what type it is
// Setting type to DbType.Object seems to work ok
case "SYSTEM.DBNULL" arm.DbType = DbType.Object; break;
default:throw new Exception("Type '" + str + "' missing from
BaseTable.CreateParam");
}
Parm.Value = value;
}// else
command.Parameters.Add(Parm);
}// function