使用Dapper查询Oracle的集合参数

Question

在WEB API服务中传递参数数组时遇到问题。

public class SampleController : ApiController
{
   public string Getdetails([FromUri] int[] id) 
   {
    var inconditions = id.Distinct().ToArray();
    using (var dbConn = new OracleConnection("DATA SOURCE=h;PASSWORD=C;PERSIST SECURITY INFO=True;USER ID=T"))
    {
        dbConn.Open();
        var strQuery = "SELECT PRIO_CATEGORY_ID AS PRIO, LANG_ID AS LANG, REC_DATE AS REC, REC_USER AS RECUSER, DESCR, COL_DESCR AS COL, ROW_DESCR AS DROW, ABBR FROM STCD_PRIO_CATEGORY_DESCR WHERE REC_USER  IN (:p)";
        var queryResult = dbConn.Query<SamModel>(strQuery, new { p = inconditions });
        return JsonConvert.SerializeObject(queryResult);
    }
}

现在，当调用API时，使用http://localhost:35432/api/Sample?id=1&id=83，它会在var queryResult = dbConn.Query(strQuery);处抛出错误，错误信息为

{"ORA-00907: missing right parenthesis"}

。

检查了所有的括号，一切看起来都很好。

- trx

---

使用 OracleCommand 绑定数组参数怎么样？ - Hackerman

@trx 在测试数据库时展示成功运行的查询语句。你尝试使用数据库参数并使其工作了吗？错误是语法错误，因此你需要查看查询字符串。 - Nkosi

这是在数据库中成功运行的查询： SELECT PRIO_CATEGORY_ID AS PRIO, LANG_ID AS LANG, REC_DATE AS REC, REC_USER AS RECUSER, DESCR, COL_DESCR AS COL, ROW_DESCR AS DROW, ABBR FROM STCD_PRIO_CATEGORY_DESCR WHERE REC_USER IN (1,83) - trx

我们如何使用参数化查询？ - trx

可能是Dapper ORM中使用SELECT * FROM X WHERE id IN (...)的重复问题。 - lorond

Answer 1

不需要在括号中包装您的集合参数。Dapper不仅是查找和替换，而且足够智能以自行处理集合参数。请尝试这样做：

... WHERE REC_USER IN :p

查看List Support章节。

在您的(:p)情况下生成了以下类型的查询：

... WHERE REC_USER IN ((1,2,3))

- lorond

---

你说得对。我在想，这是否意味着 :p 仍然有效？它是否可以与 @p 互换，还是只能使用 @p？从你提供的链接中，我感觉应该使用 @p。 - Nkosi

@Nkosi 这只是一个 MS SQL 的示例。您需要使用您的数据库支持的语法。 - lorond

在处理大型数据集时，使用IN操作不是一个好的选择。我认为，在大多数数据库中，IN操作通常限制为1000行或更少。 - Kody

Answer 2

@lorond在他的评论和答案中是正确的。

他提供的链接证实了您的代码可以重构为

public class SampleController : ApiController {
   public string Getdetails([FromUri] int[] id) {
    var inconditions = id.Distinct().ToArray();
    using (var dbConn = new OracleConnection("DATA SOURCE=h;PASSWORD=C;PERSIST SECURITY INFO=True;USER ID=T")) {
        dbConn.Open();
        var strQuery = "SELECT PRIO_CATEGORY_ID AS PRIO, LANG_ID AS LANG, REC_DATE AS REC, REC_USER AS RECUSER, DESCR, COL_DESCR AS COL, ROW_DESCR AS DROW, ABBR FROM STCD_PRIO_CATEGORY_DESCR WHERE REC_USER IN @p";
        var queryResult = dbConn.Query<SamModel>(strQuery, new { p = inconditions });
        return JsonConvert.SerializeObject(queryResult);
    }
}

- Nkosi

Answer 3

以下代码有效：

var inconditions = id.Distinct().ToArray();
var srtcon = string.Join(",",inconditions);
dbConn.Open();
var strQuery = @"SELECT PRIO_CATEGORY_ID AS PRIO, LANG_ID AS LANG, REC_DATE AS REC, REC_USER AS RECUSER, DESCR, COL_DESCR AS COL, ROW_DESCR AS DROW, ABBR FROM STCD_PRIO_CATEGORY_DESCR WHERE REC_USER  IN ("+srtcon+")";
var queryResult = dbConn.Query<SamModel>(strQuery);
return JsonConvert.SerializeObject(queryResult); 

- trx

---

查询应该保持参数化。即使在这种情况下（从整数数组中），很难想象风险，但它比字符串串联更安全。 - Loren Paulsen

Answer 4

尝试使用逗号分隔您的参数，然后在代码中拆分它们。

http://localhost:35432/api/Sample?id=1,83

- Dan Fitzsimmons

---

不，它不会返回任何东西。只是返回一个空文件。但是使用相同的查询它会从数据库返回4条记录。 - trx