Insert JSON into PostgreSQL database with Dapper ORM (.NET Core)

Question

Title pretty much explains it. Everything otherwise works fine and I can INSERT/UPDATE/DELETE/SELECT from my application using Dapper with no problem. The specific issue I am having is when attempting to INSERT values into my table.

I have tried several different approaches and can't seem to find one that will properly insert JSON values into my PostgreSQL database. I always get the error that I am attempting to insert a value of type TEXT where the database wants JSON.

For the sake of brevity, I will try to include just the pertinent code snippets.

The following RAW SQL works perfectly fine in pgAdmin:

INSERT INTO public.sales(
     firstname, lastname, middlename, address1, address2, city, state, 
    zipcode, dateofbirth, phonenumber, phonenumberalt, insurancename, insuranceid, binnumber, pcnnumber, groupid, 
    offerid, offercodes, timestamp, otherfields)
    VALUES ('Fname', 'lname1', NULL, '123 ABC St', NULL, 'Washington DC', 'DC', 
            '10062', '1988-01-01', '9545555555', NULL, 'BCBS', 'XYZ123', '600100', 'ABC123', 'DC123', 
            '12', '10,50,12', '2017-03-24', '{     "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",     "name": "Angela Barton",     "is_active": true,     "company": "Magnafone",     "address": "178 Howard Place, Gulf, Washington, 702",     "registered": "2009-11-07T08:53:22 +08:00",     "latitude": 19.793713,     "longitude": 86.513373,     "tags": [         "enim",         "aliquip",         "qui"     ] }');

Formatting is a little off--but you can see I essentially just have the JSON object in a string and it inserts just fine. However, when I attempt to do what I think is the exact same thing via my application (using NancyFX & Dapper).

Here is the Data access/repository code as well as a screenshot showing what's going on (and the error):

public void Add(Sale item)
{
    string sQuery = "INSERT INTO Sales (firstname, lastname, middlename, address1, address2, city, state, zipcode, dateofbirth, phonenumber, phonenumberalt, insurancename, insuranceid, binnumber, pcnnumber, groupid, offerid, offercodes, timestamp, otherfields)"
                    + " VALUES(@FirstName, @LastName, @MiddleName, @Address1, @Address2, @City, @State, @ZipCode, @DateOfBirth, @PhoneNumber, @PhoneNumberAlt, @InsuranceName, @InsuranceId, @BinNumber, @PcnNumber, @GroupId, @OfferId, @OfferCodes, @Timestamp, '@OtherFields')";
    using(IDbConnection active = dbConn) 
    {
        active.Open();
        active.Execute(sQuery, item);
    }
}

As you can see, the program would appear to be attempting to do the exact same thing I did manually in pgAdmin. My guess is something with Dapper forces the type or tells postgre that the string corresponds to text and that's why I'm getting an error?

My only issue was also after some searching, I'm not really sure how to get around this.

This is the important bit: "<pre>Nancy.RequestExecutionException: Oh noes! ---< Npgsql.PostgresException: 22P02: invalid input syntax for type json

and also here goes a screenshot with full message:

So, can anyone point me in the right direction? Do I need to pass my "OtherFields" as an already serialized JObject in order for this to work properly? I've tried basically everything I can think of within the SQL syntax, including casting to json (::json) and a few other things I can't really remember even at this point.

As further sanity check: if I completely remove "OtherFields" from query string in Dapper, the exact same request from before works just fine (and ironically returns the "OtherFields" due to the way I have my Module written).

TL;DR - I need to insert JSON values into my PostgreSQL database using Dapper. Halp.

Any help/suggestions are welcome! Thanks

Brunner · Accepted Answer · 2017-07-13 12:49:43Z

7

I feel so stupid...

So I thought I had attempted every syntax possible for a cast, but apparently not.

In the following code:

public void Add(Sale item)
{
    string sQuery = "INSERT INTO Sales (firstname, lastname, middlename, address1, address2, city, state, zipcode, dateofbirth, phonenumber, phonenumberalt, insurancename, insuranceid, binnumber, pcnnumber, groupid, offerid, offercodes, timestamp, otherfields)"
                    + " VALUES(@FirstName, @LastName, @MiddleName, @Address1, @Address2, @City, @State, @ZipCode, @DateOfBirth, @PhoneNumber, @PhoneNumberAlt, @InsuranceName, @InsuranceId, @BinNumber, @PcnNumber, @GroupId, @OfferId, @OfferCodes, @Timestamp, '@OtherFields')";
    using(IDbConnection active = dbConn) 
    {
        active.Open();
        active.Execute(sQuery, item);
    }
}

you can see I have single quotes around my @OtherFields which is the json type column. If you attempt to cast that using '@OtherFields'::json you still receive the exact same error from npgsql. HOWEVER, if you remove the single quotes (e.g. @OtherFields::json) the exact same API request now works perfectly fine.

I will leave this open as I am still a little curious about the ability to pass objects already serialized if I do not use the cast operator or if I will have to DESERIALIZE JObject values into string and use the casting shown here.

If someone can provide explanation and/or answers to little questions above, I will mark your answer as "the" answer.

Thanks again!

edited Jul 13, 2017 at 12:49

Brunner

1,96523 silver badges26 bronze badges

answered Mar 24, 2017 at 18:17

Fernando Rodriguez

4931 gold badge4 silver badges21 bronze badges

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

2 Comments

Michael Silver Over a year ago

Your answer could have been a bit more concise (repasting the broken query made me have to actually read your entire response :-)), but this is good information to have on the record.

jakubiszon Over a year ago

Can you post the final query that worked? Did you have to include the ::json too ?

Sifiso Shezi · Accepted Answer · 2017-03-26 08:22:22Z

1

The difference I can see it that in pgAdmin you send '{}' and your code sends '"{}"' which is invalid json.

answered Mar 26, 2017 at 8:22

Sifiso Shezi

4862 silver badges6 bronze badges

Collectives™ on Stack Overflow

Insert JSON into PostgreSQL database with Dapper ORM (.NET Core)

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related