CODEWITHSUNDEEP
sqlado.netparameterssize
Roma
Roma

Reputation: 775

Default sizes for SQL parameters in ADO.NET

There are multiple overloads for sqlcommand.Parameters.Add(...) function. Some of them do not take size, and some do. What sizes are set by default? Is it safe not to set parameter sizes? Does it affect performance?

Upvotes: 4

Views: 4054

Answers (2)

DOK
DOK

Reputation: 32851

It is possible to send a parameter that is too large for the database datatype from .Net code to SQL Server. If you set the size in creating the parameter in your .Net code, that will throw an exception if a value is sent that is too large. So you'll know sooner and won't waste the database call.

Alternately, you might want to set a max number of characters for certain text fields. The database might permit last names of 400 characters, but you only want varchar 100.

The parameter Nullable property is similar. It's optional, but if the parameter cannot be null in the sproc call, making it not nullable in your .Net code will highlight the problem sooner.

Upvotes: 1

Dan F
Dan F

Reputation: 12051

From SqlParameter.Size Property doco

If not explicitly set, the size is inferred from the actual size of the specified parameter value.

There's an awesome reply by Peter Wone on 293315 that describes more detail what that actually means

Upvotes: 4

Related Questions