CODEWITHSUNDEEP
c#postgresqldappernpgsql
Damien Sawyer
Damien Sawyer

Reputation: 5917

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

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.

Upvotes: 10

Views: 10532

Answers (2)

Damien Sawyer
Damien Sawyer

Reputation: 5917

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)

Upvotes: 14

Shay Rojansky
Shay Rojansky

Reputation: 16722

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.

Upvotes: 2

Related Questions