Collection options requesting oracle with dapper

Question

Problem passing parameter array in 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, calling the API as http://localhost:35432/api/Sample?id=1&id=83 , it gives an error message var queryResult = dbConn.Query(strQuery);

{"ORA-00907: missing right bracket"}

All brackets checked and everything looks good

Answer 1

No need to wrap your collection options in parentheses. Dapper is not just finding and replacing, but the skill is enough to independently process the parameters of the collection. Try the following:

 ... WHERE REC_USER IN :p 

List Support opens.

In your case (:p) that caused the following query:

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

Answer 2

@lorond got this right with his comment and answer .

The links he provided confirm that your code can be reorganized into

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

Answer 3

Try separating your options with a comma. Then you can split them into your code.

http: // localhost: 35432 / api / Sample? id = 1.83

Answer 4

Code below

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