ADO.Net SqlParameter and missing text in the output value

If you’re reading nvarchar(max) or ntext data from a SQL database using an output parameter with ADO.Net, you may find that the strings returned are one character in length, even though the field has more than one character of text.

This is happening because there is no Size property declared with your SqlParameter, which apparently tells ADO.Net to assume a size of 1. The solution, fortunately, is easy: specify a size of -1.

See the below examples which illustrate how to work around this ‘feature’.

// the following line doesn't specify a length, so the returned string will be one character long
SqlParameter badParam = new SqlParameter("@mynvarcharmax", SqlDbType.NVarChar);
badParam.Direction = ParameterDirection.Output;

// the following line resolves this by specifying a length of -1
SqlParameter goodParam = new SqlParameter("@mynvarcharmax", SqlDbType.NVarChar, -1);
goodParam.Direction = ParameterDirection.Output;

// if you are creating your SqlParameter by providing a parameter name and value,
// just specify the size after construction
SqlParameter goodParamWithValue = new SqlParameter("@mynvarcharmax", "my object value");
goodParamWithValue.Size = -1;
goodParamWithValue.Direction = ParameterDirection.Output;

// if using .Net 3.5, you can clean it up a bit, too
SqlParameter good35ParamWithValue = new SqlParameter("@mynvarcharmax", "my object value")
{
    Size = -1, Direction = ParameterDirection.Output;
}

Hope this helps someone else who finds themself with one-character strings inexplicably coming back from the database.