handler mapping of string to varchar in dapper

Question

I found the following code in Dapper:

sealed partial class DbString : Dapper.SqlMapper.ICustomQueryParameter
{
    ...

    public void AddParameter(IDbCommand command, string name)
    {
        ...
        var param = command.CreateParameter();
        param.ParameterName = name;
        param.Value = (object)Value ?? DBNull.Value;
        if (Length == -1 && Value != null && Value.Length <= 4000)
        {
            param.Size = 4000;
        }
        else
        {
            param.Size = Length;
        }
        ...
    }
}

Why does it compare the length to 4000?

Answer 1

Query-plan cache.

The following queries are separate and independent:

select @foo

and

select @foo

If you are confused, that is because the bit I didn't show was the parameter declaration - in the first one it is nvarchar(12) and in the second one it is nvarchar(20). What the code is trying to avoid is a single query, executed twice - for example once with hello (5 characters) and once with world! (6 characters) having two separate query-plans; that is much less efficient than allowing both to share a plan, and the number of occasions where this choice would negatively impact things is vanishingly small.

By standardising the length at some arbitrary value, it allows most common values to use the same query-plan cache. The 4000 is fairly arbitrary (well, actually it was chosen because nvarchar(4000) is the maximum size before it starts getting into max territory), and it could just as well have been 200, or whatever you want. The code is working on the basis that most of the time values are fairly short, so if there are larger values, they will be the exception rather than the rule.

Note that all of this only happens if you haven't set the Length explicitly; if you want more control over this, simply set .Length to what you want. The key properties are:

• IsAnsi - switches between unicode/not - the n in [n][var]char(len)
• IsFixedLength - switches between fixed/variable length - the var in [n][var]char(len)
• Length - the len in [n][var]char(len)
• Value - the actual contents