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.

Damien Sawyer · Accepted Answer · 2018-07-02 23:33:49Z

14

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)

edited Jul 2, 2018 at 23:33

answered Jul 2, 2018 at 23:22

Damien Sawyer

6,0677 gold badges49 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Shay Rojansky · Accepted Answer · 2018-07-02 12:46:26Z

2

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.

answered Jul 2, 2018 at 12:46

Shay Rojansky

16.9k3 gold badges44 silver badges73 bronze badges

1 Comment

Shay Rojansky Over a year ago

You can use this overload of AddWithValue, for example. Or instantiate an NpgsqlParameter yourself, set its NpgsqlDbType property and add it to the command's parameter collection.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related