ADO.NET - The Size property has an invalid size of 0
I'm trying to get output value from DB via ADO.NET. There's a client code:
using (var connection = new SqlConnection(ConnectionString))
{
connection.Open();
SqlCommand command = new SqlCommand("pDoSomethingParamsRes", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@i", 1);
var outParam = new SqlParameter("@out", SqlDbType.VarChar);
outParam.Direction = ParameterDirection.Output;
command.Parameters.Add(outParam);
command.ExecuteNonQuery();
Console.WriteLine(command.Parameters["@out"].Value.ToString());
}
When I run this I get the following exception:
the Size property has an invalid size of 0
According to manual SqlParameter.Size Property I might omit size. Why do I get this exception?
How to make it work without passing size?
VarChar and NVarChar are variable width character fields (thus var+char). You have to set the length, otherwise the default is zero.
Parameter Size is required for variable size Output parameters. Generally ADO.NET decides the size of the parameter based on the Value assigned to the parameter (hence it is optional), but in output parameter since no value is Set, you need provide the size required for the parameter
Set the Parameter size to size of the output variable from the DB... Say 50
outParam.Size = 50;
Incidentally, setting the size property of an output parameter is necessary even if it isn't a string-type parameter. For example, if you are using a System.Data.SqlDbType.Int, you should set the size to be 4.
Check MSDN : SqlParameter.Size Property
For bidirectional and output parameters, and return values, you must set the value of Size. This is not required for input parameters, and if not explicitly set, the value is inferred from the actual size of the specified parameter when a parameterized statement is executed.