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.

Everyone's answer was about as clear as mud to me. Maybe this will help someone now that I found what worked.

Need to add size to the parameter

        DynamicParameters Params = new DynamicParameters();
        Params.Add("@ProfileID", ProfileID);
        Params.Add("@CategoryName", CategoryName);
        Params.Add("@Added", dbType: DbType.String, direction: ParameterDirection.Output,size:10);

        db.Execute(sql, Params, commandType: CommandType.StoredProcedure, commandTimeout: 60);

        var Added = Params.Get<string>("@Added");

I'm not sure if this is the same problem i've had before, but using a byte for a parameter can sometimes lead to this error.

Try this. Explicitly declare the i parameter as a variable. THEN assign it's value with the Value property.

Also, you can get the actual size of the parameters by inspecting the sproc with this little command:

SqlCommandBuilder.DeriveParameters(yourCommand)

and then just foreach your way through the parameters collection.

I had this error occur, supplying a value that had a non-string, but nullable, type. In my case, an int?. I fixed it by passing in a non-nullable int instead.

you have to set Size for parameter output

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;
    outParam.Size = 50; // this is example
    command.Parameters.Add(outParam);
    command.ExecuteNonQuery();
    Console.WriteLine(command.Parameters["@out"].Value.ToString());
}

I had the same error and below is my code that works

cmd.Parameters("@ProjectKey").SqlDbType = SqlDbType.NVarChar
cmd.Parameters("@ProjectKey").Size = 150
cmd.Parameters("@ProjectKey").Direction = ParameterDirection.InputOutput
cmd.ExecuteNonQuery()