Using custom Dapper ITypeHandler for parsing data but cause conflict to handle SQL parameters

[cycbluesky]

I use custom Dapper ITypeHandler to automap between C# string[] and mysql Json field. But it causes confict to handle SQL parameters

1. table

CREATE TABLE if NOT EXISTS post (
id bigint NOT NULL AUTO_INCREMENT,
approver json NULL COMMENT 'approvers, null or array'
PRIMARY KEY (id)
}

2. mapping class
   public class Post
   {
   public long Id { get; set; }
   public string[] Approver { get; set; }
   }

3. custom Dapper ITypeHandler
   ` public class JsonTypeHandler : SqlMapper.TypeHandler
   {
   public override T? Parse(object value)
   {
   if (value is string json)
   {
   return JsonSerializer.Deserialize(json, Consts.Genernal.JsonSerializerOptions);
   }

        return default;
    }
   
    public override void SetValue(IDbDataParameter parameter, T? value)
    {
        parameter.DbType = DbType.String;
        parameter.Value = JsonSerializer.Serialize(value, Consts.Genernal.JsonSerializerOptions);
    }
   

   }`

4. register custom typehandler
   SqlMapper.AddTypeHandler(new JsonTypeHandler<int[]>());

5. confilct when using IN clause (from hangfire.mysql)

var connectionString = "your mysql connectionstring"; var queues = new string[] { "default"}; using var connection = new MySqlConnection(connectionString); int nUpdated = connection.Execute( $"updateJobQueue set FetchedAt = UTC_TIMESTAMP(), FetchToken = @fetchToken " + "where (FetchedAt is null or FetchedAt < DATE_ADD(UTC_TIMESTAMP(), INTERVAL @timeout SECOND)) " + " and Queue in @queues " + "LIMIT 1;", new { queues = queues, timeout = 60, fetchToken = Guid.NewGuid().ToString() });

the above code throws exception: MySqlConnector.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''["default"]' LIMIT 1' at line 1