ADO.NET - The Size property has an invalid size of 0

Question

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?

Answer 1

VarChar and NVarChar are variable width character fields (thus var+char). You have to set the length, otherwise the default is zero.

Answer 2

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;

Answer 3

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.