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

Question

The name pretty much explains this. Everything else works fine, and I can INSERT / UPDATE / DELETE / SELECT from my application use Dapper without any problems. The specific problem I am facing is trying to use the INSERT values ​​in my table.

I tried several different approaches and cannot find one that will correctly insert JSON values ​​into my PostgreSQL database. I always get the error that I am trying to insert a value of type TEXT where the database wants JSON .

For brevity, I will try to include only the relevant code snippets.

The following RAW SQL file works 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 doesn't work a bit - but you can see that I essentially just have a JSON object in the string, and it inserts just fine. However, when I try to do what I think is the same with my application (using NancyFX and Dapper ).

Here is the data / repository access code, as well as a screenshot showing what is happening (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 seems to be trying to do the exact same thing as me manually in pgAdmin. My guess is that Dapper forces the type or tells postgre that string matches TEXT and why am I getting an error message?

My only problem was also after some searches, I'm not quite sure how to get around this.

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

as well as a screenshot with the full message:

So can anyone point me in the right direction? Do I need to pass my "OtherFields" as already serialized JObject for this to work correctly? I tried basically everything that I can imagine in SQL syntax, including casting in json ( ::json ) and some other things that I really can’t remember even at that moment.

As an additional health check: if I completely remove “OtherFields” from the query string in Dapper, then the same query from before works just fine (and, ironically, returns “OtherFields” due to the way I wrote my Module ) ,.

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

Any help / suggestions are welcome! Thanks

Answer 1

I'm so stupid ...

So, I thought I tried to use every syntax for translation, 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 that I have single quotes around my @OtherFields , which is a json type column. If you try to do this using '@OtherFields'::json , you will still get the same error from npgsql . HOWEVER, if you remove single quotes (e.g. @OtherFields::json ), then the same API request now works just fine.

I will leave it open, because I’m still a little curious that you can transfer objects already serialized if I don’t use the translation operator, or if I have to DESERIALIZE JObject values ​​in string and use the spell shown here.

If someone can give an explanation and / or answers to the small questions above, I will mark your answer as the answer.

Thanks again!

Answer 2

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