TypeHandler not working correctly with DynamicParameters

[ylibrach]

trafficstars

It appears that when executing a stored procedure and feeding parameters in through a DynamicParameters object, the command will fail if one of the parameters is set to a type that has a TypeHandler set on SqlMapper.

It appears that Dapper never makes a check to establish whether there is an associated TypeHandler for the parameter (no calls against the Dictionary happen). The error is: No mapping exists from object type [ObjectType] to a known managed provider native type. Relevant stack trace is as follows:

   at System.Data.SqlClient.MetaType.GetMetaTypeFromValue(Type dataType, Object value, Boolean inferLen, Boolean streamAllowed)  
   at System.Data.SqlClient.SqlParameter.GetMetaTypeOnly()
   at System.Data.SqlClient.SqlParameter.get_DbType()
   at Dapper.DynamicParameters.AddParameters(IDbCommand command, Identity identity) in [..]\Dapper.cs:line 4677
   at Dapper.DynamicParameters.Dapper.SqlMapper.IDynamicParameters.AddParameters(IDbCommand command, Identity identity) in [..]\Dapper.cs:line 4569
   at Dapper.SqlMapper.<>c__DisplayClass52.<GetCacheInfo>b__4d(IDbCommand cmd, Object obj) in [..]\Dapper.cs:line 2091
   at Dapper.CommandDefinition.SetupCommand(IDbConnection cnn, Action2 paramReader) in [..]\Dapper.cs:line 177
   at Dapper.SqlMapper.ExecuteCommand(IDbConnection cnn, CommandDefinition& command, Action2 paramReader) in [..]\Dapper.cs:line 3313
   at Dapper.SqlMapper.ExecuteImpl(IDbConnection cnn, CommandDefinition& command) in [..]\Dapper.cs:line 1309
   at Dapper.SqlMapper.Execute(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Nullable1 commandTimeout, Nullable1 commandType) in [..]\Dapper.cs:line 1183 

[ylibrach]

After looking into this for a bit, it seems that this line in AddParameters(...) method is causing the problem:

if (dbType == null && val != null && !isCustomQueryParameter) 
    dbType = SqlMapper.LookupDbType(val.GetType(), name, true, out handler);

Basically, Dapper is checking if the dbType of the parameter is not set, and only if it's not set does it look up the TypeHandler. In our case the dbType is set to String, but I'm not sure why the TypeHandler match should be tied to the dbType not being null? Is it possible to make the TypeHandler lookup happen regardless?

[ylibrach]

This also causes another issue - if we manually give Dapper a null dbType for this parameter so that Dapper will correctly load the TypeHandler, then it's possible to get an implicit conversion error from SQL Server. For example, on this custom type we are serializing it into a string (hence the dbType was originally set to String). Dapper will call the TypeHandler, get the new string value, but will now pass this parameter to SQL Server as DbType.Object. This causes the following error:

Implicit conversion from data type sql_variant to nvarchar(max) is not allowed. Use the CONVERT function to run this query.

If we can keep the dbType as String, this would be avoided. However, keeping it as String then prevents Dapper from calling the TypeHandler. So we have an error either way. Thoughts?

[mgravell]

For example, on this custom type we are serializing it into a string (hence the dbType was originally set to String). Dapper will call the TypeHandler, get the new string value, but will now pass this parameter to SQL Server as DbType.Object.

This sounds ... fun; however, when adding the type-parameter, you should have the opportunity to set the db-type of the parameter, no?

[ylibrach]

however, when adding the type-parameter, you should have the opportunity to set the db-type of the parameter, no?

Yes, but that leads us to the original problem :) ... If we add the parameter with the dbType set, for example like this:

dynamicParameters.Add("bodyParam", dbType: DbType.String, value: body)

Then this will cause the original issue to occur. In other words, Dapper will process the parameters, see that "bodyParam" has a DbType that is set (ie, not null), and will therefor not look for a matching TypeHandler. As you can see in this code within Dapper:

if (dbType == null && val != null && !isCustomQueryParameter) 
    dbType = SqlMapper.LookupDbType(val.GetType(), name, true, out handler);

That line is not just figuring out the DbType, it is also resolving the handler (hence the out handler parameter). So, if we set the DbType to String, then the handler will never be resolved because that line will never get called.

[ylibrach]

Bump. Any word on this?

[Jetski5822]

Ahhh - I just ran it to this; its also breaking Always Encrypted when you change the Type Handler; The Type Handler should be specified with a Data Type

[sambauwens]

I got around the issue by converting the sql_variant into the corresponding sql server string type (nvarchar(max) in my case) in the sql I'm giving to dapper :

CONVERT(NVARCHAR(MAX), @paramName)

[pynej]

I don't think there is actually a bug here. The actual issue is that the ITypeHandler has to set it's parameter.DbType so that it doesn't try to push as sql_variant

[shoaibshakeel381]

So to summarize, set Dbtype to null during AddParameter() and set it to proper DbType.Int32 or DbType.String in ITypeHandler.SetValue(IDbDataParameter parameter, TEnum value)