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

Creating a Web API through which array of id is passed and returns the result from the OracleDB.

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

And called the API as http://localhost:35432/api/Sample?id=1&id=83 it throws an error saying on var queryResult = dbConn.Query(strQuery); enter image description here But if I just give one parameter as below it works

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 ; 

Can anyone please suggest me what is the issue here as a single parameter works. Thanks

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

Accepted Answer

Check to make sure your don't have any stray characters in your query.

As stated in the comments

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

So pass the id array into the parameterized query when executing.

Here is a refactored version of your example.

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

Your code looks fine to me. It might fail if your id array parameter is empty (but it will be a different error than what you see now). Put a breakpoint in your code and inspect the value of that.

Also for converting your array to string, You may use the String.Join method.

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

This will give the result like "1,3,5", assuming your int array has 3 items ,1,3 and 5

Now you can use this string variable in your query. Also you may consider passing this data 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