14

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Q''
  • 399
  • 5
  • 15
  • 3
    What's the parameter size defined as in the stored procedure? – stuartd Jan 08 '19 at 16:15
  • 1
    Show us your entire stored procedure. I don't think this is a problem with Dapper. – Robert Harvey Jan 08 '19 at 16:16
  • Did you look at https://stackoverflow.com/questions/1371383/for-nvarcharmax-i-am-only-getting-4000-characters-in-tsql?rq=1 – Flydog57 Jan 08 '19 at 16:30
  • 2
    I don't know about Dapper, but for NHibernate, we had the same problem. We had to change our map to this. Maybe it will point you in a dapper direction Map(x => x.Summary).CustomType("StringClob").CustomSqlType("varchar(max)") – Nikki9696 Jan 08 '19 at 16:38
  • @stuartd I thought I had ruled out my SPROC because when I execute it from SSMS the full string is returned. I have added my SPROC to the question. I have now found that if I use Dapper via a SQL query rather than the SPROC I get the full data returned. I still don't understand if this a Dapper issue or SPROC issue. – Q'' Jan 08 '19 at 16:43

1 Answers1

22

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

image of code from the above link

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

p.Add("@Program", "", DbType.String, direction: ParameterDirection.Output, size:int.MaxValue);
stuartd
  • 70,509
  • 14
  • 132
  • 163
  • 2
    You nailed it, @stuartd - thank you. I didn't realise the source code for Dapper was available on Github, so that's useful too. – Q'' Jan 09 '19 at 12:29