How to use Dapper to insert data into a PostgreSQL jsonb column?

c# dapper npgsql postgresql

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.

1
6
7/2/2018 10:22:24 AM

Accepted Answer

NpgsqlDbType and DbType are two different enums, you can't simply cast one into the other...

You'll have to make for set the NpgsqlDbType property on the NpgsqlParameters it creates and send. IIRC there's a way to specify custom parameters.

1
7/2/2018 12:46:26 PM

Popular Answer

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)


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow