Collection parameters querying oracle with dapper

Question

Having issue while passing the array of parameters in the WEB API service

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

Now while calling the API as http://localhost:35432/api/Sample?id=1&id=83 it throws an error saying on var queryResult = dbConn.Query(strQuery);

{"ORA-00907: missing right parenthesis"}

Checked all the parenthesis and everything looks good

How about using `OracleCommand` to bind the array parameter? — Hackerman, Jul 22 '16 at 18:14
@trx Show the query that runs successfully when you test it in database. Did you try to use parameters in database and get it to work? The error is a syntax error so you need to look at the query string. — Nkosi, Jul 22 '16 at 18:55
This is the query that runs successfully in the DB 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, Jul 22 '16 at 19:42
Possible duplicate of [SELECT \* FROM X WHERE id IN (...) with Dapper ORM](http://stackoverflow.com/questions/8388093/select-from-x-where-id-in-with-dapper-orm) — lorond, Jul 22 '16 at 20:09

lorond · Accepted Answer · 2016-07-22T20:24:35.320

10

There is no need to wrap your collection parameters in parenthesis. Dapper is not just find-and-replace, and smart enought to handle collection parameters it by itself. Try this:

... WHERE REC_USER IN :p

Check out List Support section.

In your (:p) case following kind of query generated:

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

edited Jul 22 '16 at 20:24

answered Jul 22 '16 at 20:00

lorond

3,856
2
37
52

You are correct. I am wondering. Does that mean `:p` will still work? is it interchangeable with `@p` or are you restricted to `@p` alone. I get the feeling form the links you provided that it is suppose to be `@p` – Nkosi Jul 22 '16 at 21:34
2

@Nkosi There just an example for ms sql. You need to use syntax your database support. – lorond Jul 23 '16 at 15:30
1

Using IN is not good for larger data sets. IN is limited to exactly 1000 rows or less in most DBs I think. – Kody May 31 '18 at 20:56

score 1 · Answer 2 · edited May 23 '17 at 12:14

@lorond got it right with his comment and answer.

The links he provided confirm that your code can be refactored to

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

score 0 · Answer 3 · edited Jul 22 '16 at 21:37

0

The below code worked,

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

edited Jul 22 '16 at 21:37

Nkosi

235,767
35
427
472

answered Jul 22 '16 at 19:55

trx

2,077
9
48
97

1

The query should stay parameterized. It's much more secure than string concatenation, even though it's difficult to imagine the risk in this case (from the array of ints) – Loren Paulsen Jul 22 '16 at 21:13

score -1 · Answer 4 · answered Jul 22 '16 at 18:31

-1

Try separating your parameters with a comma. Then you can split them in your code.

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

answered Jul 22 '16 at 18:31

Dan Fitzsimmons

1
3

No, it doesnt return anything. Just returns an empty file. But with the same query it returns 4 records from Database. – trx Jul 22 '16 at 18:40

Collection parameters querying oracle with dapper

4 Answers4