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! ---&lt; 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

Answer 1

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!

Answer 2

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