Multiple Arrays as input parameters to the Web API

Question

We created the Web API that queries the Oracle DB by accept thing the array of Query Parameters and returning the Result in the JSON format.

public class TestQueryController : ApiController
{
  public HttpResponseMessage Getdetails([FromUri] string[] id)
   {
    List<OracleParameter> prms = new List<OracleParameter>();
   string connStr = ConfigurationManager.ConnectionStrings["PDataConnection"].ConnectionString;
   using (OracleConnection dbconn = new OracleConnection(connStr))
 {
 var inconditions = id.Distinct().ToArray();
 var srtcon = string.Join(",", inconditions);
 DataSet userDataset = new DataSet();
 var strQuery = @"SELECT STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY,
 STCD_PRIO_CATEGORY_DESCR.SESSION_NUM AS SESSION_NUMBER,
 Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE) AS SESSION_START_DATE         
 from STCD_PRIO_CATEGORY
 where STCD_PRIO_CATEGORY_DESCR.STD_REF IN(";
 StringBuilder sb = new StringBuilder(strQuery);
 for(int x = 0; x < inconditions.Length; x++)
 {
   sb.Append(":p" + x + ",");
   OracleParameter p = new OracleParameter(":p" + x,OracleDbType.NVarchar2);
   p.Value = inconditions[x];
   prms.Add(p);
 }
 if(sb.Length > 0) sb.Length--;
 strQuery = sb.ToString() + ")";
 using (OracleCommand selectCommand = new OracleCommand(strQuery, dbconn))
 {
 selectCommand.Parameters.AddRange(prms.ToArray());
 using (OracleDataAdapter adapter = new OracleDataAdapter(selectCommand))
{
    DataTable selectResults = new DataTable();
    adapter.Fill(selectResults);
    var returnObject = new { data = selectResults };
    var response = Request.CreateResponse(HttpStatusCode.OK, returnObject, MediaTypeHeaderValue.Parse("application/json"));
    ContentDispositionHeaderValue contentDisposition = null;
    if (ContentDispositionHeaderValue.TryParse("inline; filename=ProvantisStudyData.json", out contentDisposition))
    {
        response.Content.Headers.ContentDisposition = contentDisposition;
    }
     return response;
  }}}}}}

So the API call was like https://localhost:4320/api/TestQuery?id=xxx&id=yyy and the result was {"data":[{"CATEGORY":"Internal Study","SESSION_NUMBER":7,"SESSION_START_DATE":"2015-02-13T00:00:00"}]} I would like to add one more input parameter now in to the API, which will be the Array of Column names which are in the select statement, So the the API will return only those columns. The API will be https://localhost:4320/api/PData?id=xxx&id=yyy&col=CATEGORY&col=SESSION_NUMBER returning only the column names CATEGORY and SESSION_NUMBER {"data":[{"CATEGORY":"Internal Study","SESSION_NUMBER":7}]}. Can this be done here, I am not sure how will we be selecting the Column names dynamically as we are just giving the Query in the variable. Any help is greatly appreciated.

What exactly is your question? How to do the parameters on the controller action? How to do it in the SQL code? If it's going to work? — Noémie Lord
– Noémie Lord, Commented Aug 12, 2016 at 12:46
@FrancisLord How to Select the column names that are passed in the Controller as the Input parameter — trx
– trx, Commented Aug 12, 2016 at 12:48
Yes. I understand how I will be passing them as OracleParameters — trx
– trx, Commented Aug 12, 2016 at 12:52

trx · Accepted Answer · 2016-08-12 17:01:58Z

Since you are already specifying the column names in SQL string you are sending to the server, you could simply edit that string in the C# code in a similar way you are adding the ids to the where condition. It would actually be even easier since you don't need to include the column names as string and all. The only thing I see here is that you are using aliases to changes the names of the output columns. If this is necessary, I would suggest using a dictionary to store the relationship between the (output) column names and the selection you need to add. Something like this :

var colDict = new Dictionnary<string, string>() {
    {"CATEGORY", "STCD_PRIO_CATEGORY_DESCR.DESCR"},
    //...
    {"SESSION_START_DATE", "Trunc(STCD_PRIO_CATEGORY_DESCR.START_DATE)"}
}

Then you can just use that to create the Select string :

//'columns' is the parameter from the query string

List<string> selectionStrings = new List<string>();
foreach (string col in columns)
{
    string selector = colDict[col];
    selectionStrings.Add(string.Format("{0} AS {1}", selector, col));
}

string selectString = string.Join(', ', selectionStrings);

Then you can just put that into your actual SQL string :

var strQuery = string.Format(
    @"SELECT {0}         
    from STCD_PRIO_CATEGORY
    where STCD_PRIO_CATEGORY_DESCR.STD_REF IN(",
    selectString);

Then you can just continue on to your normal code to add the ids and all. Hope that's why you were looking for! :-)

Thanks a ton. I understand that the selectString is having the all the Column names joined together. Just confused with the strQuery as where STCD_PRIO_CATEGORY_DESCR.STD_REF IN("will be holding the ID's whereexcatly we will be giving the selectString
Not too sure what your question is. Have you ever used string.Format? quick explaination : you see the {0} in string, where you'd normally put the columns you want to return? when you call string.Format, it will replace all the {#} instances by the parameters you give it afterwards (in order). In this instance I'm telling him to replace the {0} by the value of selectString.
it worked awesome. Thanks again. I am just editing few things.
Great ! Thanks for the corrections. I'm too used to VS auto-complete :)

Collectives™ on Stack Overflow

Multiple Arrays as input parameters to the Web API

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related