How to use Prepare?

  • Thread starter Thread starter Christian Romberg
  • Start date Start date
C

Christian Romberg

Hi,

when using Prepare() on a SqlCommand, the SqlClient (unlike the
OracleClient) requires for certain types, that Size, Precision and/or
Scale have been set *explicitly* by the user.

The problem I have is, that I don't know in advance what values should be
stored.

So my approach is, to set the maximal value (e.g. 4000 for size for
DbType.String).

My questions are:
Does that matter at all when executing the Command?
If I specify 4000 but the string is just 3 characters long, would it have
been faster (or consume less memory) if I had specified some smaller value
instead of 4000?
What happens if my string exceeds 4000 characters?

Thank you in advance!

Christian
 
Christian said:
Hi,

when using Prepare() on a SqlCommand, the SqlClient (unlike the
OracleClient) requires for certain types, that Size, Precision and/or
Scale have been set explicitly by the user.

The problem I have is, that I don't know in advance what values
should be stored.

So my approach is, to set the maximal value (e.g. 4000 for size for
DbType.String).

My questions are:
Does that matter at all when executing the Command?
If I specify 4000 but the string is just 3 characters long, would it
have been faster (or consume less memory) if I had specified some
smaller value instead of 4000?
What happens if my string exceeds 4000 characters?
Click to expand...

You only have to set the length/size if you fill in the value later.
So if you create a parameter by specifying a value as well, you don't
have to set the length/size as it's derived from the length of the
value. If you don't have the value yet, it's important you set the
length/size to such a value which is larger than the value you're
setting the parameter to. There is no memory allocated for the size
specified, so if you specify 2GB for a Text column, you won't get 2GB
allocated ;)

FB

--
 
Frans Bouma wrote in :
You only have to set the length/size if you fill in the value
later.
So if you create a parameter by specifying a value as well, you don't
have to set the length/size as it's derived from the length of the
value. If you don't have the value yet, it's important you set the
length/size to such a value which is larger than the value you're
setting the parameter to. There is no memory allocated for the size
specified, so if you specify 2GB for a Text column, you won't get 2GB
allocated ;)

FB
Click to expand...

Thanks for your reply!

For Aug CTP of .NET 2.0 I can definitely confirm, that although size,
type, etc. are derived from the passed value (visible in debugger), you
nevertheless have to explicitly specify them, otherwise Prepare throws
an exception, stating that.

A follow-up question:
DbType.Decimal requires to have specified Significance and Scale during
Prepare. I've tried 38 and 28 respectively, getting Exceptions for a
Decimal value of -75, the stack trace showed, that during scale
adjustment something went wrong, reducing scale to 20 worked.

But what would be the recommended values for Significance and Scale when
I don't know the value in advance, and it could be every value supported
by Sql Server?

Christian
 
Back
Top