使用Dapper ORM (.NET Core)将JSON插入PostgreSQL数据库

Question

标题已经解释了问题。除此之外，一切正常，我可以使用Dapper从我的应用程序中INSERT/UPDATE/DELETE/SELECT而没有任何问题。我遇到的具体问题是尝试将值INSERT到我的表中时出现错误。

我尝试了几种不同的方法，但似乎找不到一个可以正确将JSON值插入到我的PostgreSQL数据库中的方法。我总是会得到错误，即我正在尝试插入一个TEXT类型的值，而数据库需要JSON。

为了简洁起见，我将尝试仅包含相关的代码片段。

下面的原始SQL在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"     ] }');

格式有些不对，但你可以看到我基本上只是将JSON对象放在一个字符串中，然后它就可以成功插入。然而，当我尝试通过我的应用程序（使用NancyFX和Dapper）做同样的事情时，出现问题。

以下是数据访问/仓库代码以及显示发生的情况（以及错误）的截图：

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);
    }
}

可以看到，程序似乎试图做与我在pgAdmin中手动执行的完全相同的事情。 我猜Dapper中的某些内容会强制类型或告诉postgre，字符串对应于文本，这就是为什么我会出现错误的原因吗？

我的唯一问题也是在搜索后，我不太确定如何解决这个问题。

这是重点："<pre>Nancy.RequestExecutionException: Oh noes! ---&lt; Npgsql.PostgresException: 22P02: invalid input syntax for type json

而且这里还有全息截图：

所以，有人能指点我方向吗？我需要将“OtherFields”作为已序列化的JObject传递才能使其正常工作吗？在SQL语法中，我尝试了基本上我能想到的所有方法，包括转换为JSON（::json）和其他一些我此时甚至无法记住的方法。

作为进一步的健全检查：如果我完全从Dapper的查询字符串中删除“OtherFields”，则与之前完全相同的请求正常工作（并且由于我的Module编写方式，具有讽刺意味的返回“OtherFields”）。

TL;DR - 我需要使用Dapper将JSON值插入到我的PostgreSQL数据库中。 求助。

欢迎任何帮助/建议！谢谢

- Fernando Rodriguez

Answer 1

我感觉自己好傻...

我原以为我已经尝试了所有可能的类型转换语法，但显然不是这样。

在以下代码中：

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);
    }
}

您可以看到，我在@OtherFields周围使用了单引号，这是json类型列。如果您尝试使用'@OtherFields' :: json进行强制转换，则仍将从npgsql接收到完全相同的错误。然而，如果您删除单引号（例如@OtherFields :: json），则完全相同的API请求现在可以正常工作。

我会保持开放，因为我仍然有点好奇是否可以传递已序列化的对象，如果我不使用转换运算符，或者我是否必须将JObject值反序列化为string并使用此处显示的转换。

如果有人能够解释和/或回答上面的小问题，我将把您的答案标记为“答案”。

再次感谢！

- Fernando Rodriguez

---

您的回答可以再简洁一些（重新复制损坏的查询使我不得不阅读您的整个回答:-)），但这是有记录意义的好信息。 - Michael Silver

你能发布一下最终有效的查询吗？你是否也需要包括 ::json？ - jakubiszon

Answer 2

我能看出来的区别是，在pgAdmin中你发送'{}'，而你的代码发送了'"{}"'，这是无效的JSON。

- Sifiso Shezi