CODEWITHSUNDEEP
asp.netasp.net-mvcasp.net-web-apidapper
trx
trx

Reputation: 2157

passing array of parameters in ASP .NET Web API

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

Upvotes: 1

Views: 802

Answers (2)

Shyju
Shyju

Reputation: 218732

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

Upvotes: 1

Nkosi
Nkosi

Reputation: 247108

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

Upvotes: 1

Related Questions