trx trx - 3 months ago 9
SQL Question

Multiple Arrays as input parameters to the Web API

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.

Answer

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! :-)