How in ASP.NET Web API and Dapper, do you send an array of arguments?

asp.net asp.net-mvc asp.net-web-api dapper

Question

constructing a web API that accepts an array of ids and delivers an OracleDB result.

public class SampleController : ApiController
{
    public string Getdetails([FromUri] int []id)
    {
       string inconditons = "";
        for (int i = 0; i < id.Length; i++)
        {
            if (i == id.Length - 1)
            {
                inconditons = inconditons + id[i];
            }
            else
            {
                inconditons = inconditons + id[i] + ", ";
            }
        }
        using (var dbConn = new OracleConnection("DATA SOURCE=X;PASSWORD=03JD;PERSIST SECURITY INFO=True;USER ID=IN"))
        {
            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 (" + inconditons + ");";
            var queryResult = dbConn.Query<SamModel>(strQuery);
            return JsonConvert.SerializeObject(queryResult);
        }
    }
}

It gives an error indicating on var queryResult = dbConn.Query(strQuery); enter image description here after using the API with the number http://localhost:35432/api/Sample?id=1&id=83. But if I just enter the following argument, it will function.

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  =" +id ; 

Please tell me what the problem is because a single parameter works. Thanks

1
1
7/22/2016 4:21:58 PM

Accepted Answer

Verify that your query doesn't include any extra characters.

According to the remarks

Use parameterized queries, otherwise you're vulnerable to errors like this and SQL Injection attacks.

then pass theid when the parameterized query is executed, array into it.

Here is your example that has been refactored.

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);
        }
    }
}
1
7/22/2016 5:12:15 PM

Popular Answer

I see no issues with your code. Possibly failing if yourid argument for array is empty (but it will be a different error than what you see now). Create a breakpoint in your code and look at its value.

Additionally, you may use the to convert your array to a stringString.Join method.

var ids = String.Join(",",id);

This will get a result similar to"1,3,5" , assuming your integer array has three items: 1, 3, and 5

This string variable is now available for use in your query. You may also think about including this info as a parameter.

 var q= " ...  where REC_USER IN (@ids);" //Please fill the  missing part of query
 var result = con.Query<SomeModel>(q,new { ids });


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow