Inserting data into PostgreSQL jsonb column with Daper.Net and NPGSQL

Question

I'm trying to insert JSON data into a JSONB PostgreSQL column using Dapper.Net.

The NPGSQL Documentation for JSONB gives specific instructions to use the NpgsqlDbType.Jsonb datatype.

With Dapper, I'm trying to add this as a custom parameter without success.

using (var conn = myconnection)
{
    var sql = "INSERT INTO mytable (jsonbody) VALUES (@jb);";
    dp =  new DynamicParameters();
    dp.Add("jb", stringOfJsonData, (DbType)NpgsqlDbType.Jsonb);
    await conn.ExecuteAsync(sql,dp);                    
}                

The error I'm receiving is System.NotSupportedException : The parameter type DbType.36 isn't supported by PostgreSQL or Npgsql

Any suggestions on how to use these two libraries together for JSONB?

Thanks.

Answer 1

It appears that changing the SQL statement to the following fixed it.

var sql = "INSERT INTO mytable (jsonbody) VALUES (CAST(@jb AS json));";

Also, there's no need to specify the type in the parameter mapping.

dp.Add("jb", stringOfJsonData)