时间戳

首页 > 解决方案 > How to query ODBC with Dapper with multiple parameters including a WHERE IN?

sql - How to query ODBC with Dapper with multiple parameters including a WHERE IN?

问题描述

I've hit an issue using Dapper to query an ODBC provider. The query in question is supplied several parameters. One of the parameters is used to populate a WHERE IN operator.

So far I've tried supplying the query with: DynamicParameters, ? regular parameters, ?name? pseudo-positional parameters and some combinations of these.

SELECT companyId
    ,projectId
    ,contractId
    ,status
FROM certificate
WHERE companyId = ?companyId?
    AND projectId = ?projectId?
    AND contractId = ?contractId?
    AND invoiceId IN ?invoiceIds?

var results = await connection
    .QueryAsync<Certificate>(query, new { companyId, projectId, contractId, invoiceIds = new string[] { '1a', '1b', '2a' }});

Expected results are several rows being returned. If I hardcode the query with it's parameters it works fine, so the data I'm supplying should return rows.

Instead I'm getting an exception with the following message:

ERROR [07001] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Value has not been specified for parameter 4.
ERROR [07001] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]Value has not been specified for parameter 5.

标签： sqlodbcdapper

解决方案

如果那是您的代码，那么您的代码将无法编译。您没有为前三个参数提供任何值，并且在第四个参数中提供了非法字符而不是字符串。我看不出为什么这不起作用：

var results = await connection
    .QueryAsync<Certificate>(query, 
         new { companyId=1, projectId=1, contractId=1, invoiceIds = new [] { "1a", "1b", "2a" }});

推荐阅读