Using Dapper to get nvarchar(max) returns a string trimmed to 4000 characters. Can this behaviour be changed?

Question

I have a SQL Server data table which stores a JSON string in one of its columns. The JSON string is a serialised .net object and the data typically exceeds 4000 characters.

I have a simple stored procedure which I use to retrieve the data:

    @StageID int,
    @Description varchar(250) = null OUTPUT,
    @Program nvarchar(max) = null OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @Program = StageProgram, @Description = Description 
    FROM StageProgram 
    WHERE StageID = @StageID;

    RETURN 0;
END 

I am using the data type nvarchar(max) for the column. When I serialise the .net object to JSON and write it to the database using Dapper, I find that the full string is correctly stored in the database.

However, when I attempt to retrieve the string I find that it is trimmed to 4000 characters, discarding the rest of the data.

Here is the relevant code:

DynamicParameters p = new DynamicParameters();

p.Add("@StageID", Properties.Settings.Default.StageID, DbType.Int32, ParameterDirection.Input);
p.Add("@Description", "", DbType.String, direction: ParameterDirection.Output);
p.Add("@Program", "", DbType.String, direction: ParameterDirection.Output);
p.Add("@ReturnValue", DbType.Int32, direction: ParameterDirection.ReturnValue);               

try
{
     int stageID = Properties.Settings.Default.StageID;
     connection.Execute(sql, p, commandType: CommandType.StoredProcedure);                 
     json = p.Get<string>("@Program");
     int r = p.Get<int>("@ReturnValue");                    
}

When I run this, the string json is trimmed to 4000 characters.

If I use the built in .net SQL Server connection to retrieve it instead (using a query rather than a stored procedure for simplicity), the full data is correctly returned:

SqlCommand getProgram = new SqlCommand("SELECT StageProgram FROM StageProgram WHERE StageID = 1;");
getProgram.Connection = connection;
string json = Convert.ToString(getProgram.ExecuteScalar());

Is an experienced Dapper user able to provide an explanation for this behaviour?

Can it be changed?

Answer 1

4000 characters is (currently) the default length for a DBString in Dapper:

To get the full text, you just need to set the size parameter:

p.Add("@Program", "", DbType.String, 
       direction: ParameterDirection.Output, size:int.MaxValue);