时间戳

首页 > 解决方案 > 使用 PetaPoco 从 PostgreSQL 返回记录 ID

postgresql - 使用 PetaPoco 从 PostgreSQL 返回记录 ID

问题描述

PetaPoco PostgreSQL 11.1

我正在尝试获取已删除记录 ID 的列表。这不起作用：

var sql = new Sql()
      .Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
                  VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
                       )
                   DELETE FROM dx d
                    USING _in n
                   JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
                  JOIN disease z ON (z.description = n.description)
                  WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
               RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);

      return db.Fetch<int?>(sql);

RETURNING 没有被兑现。（我收到“1”显示删除成功而不是 reci 值）。它在 pgAdmin 4 中正确运行。

有没有办法用 PetaPoco（和 C#）做到这一点？我不仅在寻找一个单一的 recid，而且在寻找一个 int 的 IEnumerable？从许多删除。

TIA

标签： postgresqlpetapoco

解决方案

我花了一点时间才明白这里发生了什么。

运行查询并将结果Fetch<T>集作为类型列表返回。直接来自文档。

您可能需要尊重其中的键入部分。

希望以下内容能证明正在发生的事情。

public class TypedReturn { 
   public string recid { get; set; }
}

var sql = new Sql()
    .Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
                VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
                )
                  DELETE FROM dx d
                    USING _in n
                   JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
                  JOIN disease z ON (z.description = n.description)
                  WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
               RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);

List<TypedReturn> returnValues = db.Fetch<TypedReturn>(sql);

foreach(var returnValue in returnValues) 
{
    Console.WriteLine(returnValue.recid);
}

或者你可以使用dynamic，你会这样做

var sql = new Sql()
    .Append(@"; WITH _in (lastname, firstname, birthdate, description, dencounter) AS (
                VALUES ( UPPER(@0), UPPER(@1), @2::date, LOWER(@3), @4::date  )
                )
                  DELETE FROM dx d
                    USING _in n
                   JOIN patients pt ON (pt.lastname, pt.firstname, pt.birthdate) = (n.lastname, n.firstname, n.birthdate)
                  JOIN disease z ON (z.description = n.description)
                  WHERE (d.patient_recid, d.disease_recid, d.dposted)=(pt.recid, z.recid, n.dencounter)
               RETURNING d.recid;", lastName, firstName, birthDate, description, tencounter);

List<dynamic> returnValues = db.Fetch<dynamic>(sql);

foreach(var returnValue in returnValues) 
{
    Console.WriteLine(returnValue.recid);
}

推荐阅读