ASP. Net Web API service connecting to oracle database

Question

I am new to AP.Net MVC services and Trying to create a web service where we give the array of input which will connect to oracle database and return the query (the array of input needs used in the filter)result. I am trying no to use the entity data model and giving the POCO classes in Model classes.So what I tried is to create a simple controller which selectentire data from the database

 public class TestOraConnController : ApiController
 {
     public string Get(int id)
     {
        var strQuery = @"Select PRIO_CATEGORY_ID as PRIO,LANG_ID as LANG, REC_DATE as REC, REC_USER as RECUSER, RFCH_ID as RFCH,DESCR,COL_DESCR AS COL,ROW_DESCR as DROW,ABBR from STCD_PRIO_CATEGORY_DESCR where REC_USER =" +id;
        OracleConnection dbConn = new OracleConnection("DATA SOURCE=ABC;PASSWORD=ABCD;PERSIST SECURITY INFO=True;USER ID=ABC");
        dbConn.Open();
        OracleCommand selectCommand = new OracleCommand(strQuery, dbConn);
        OracleDataAdapter adapter = new OracleDataAdapter(selectCommand);
        DataTable selectResults = new DataTable();
        adapter.Fill(selectResults);
        dbConn.Close();

        return JsonConvert.SerializeObject(selectResults);
}

So the result looks

 "[{\"PRIO\":1,\"LANG\":1,\"REC\":\"2011-10-23T20:32:18\",\"RECUSER\":1,\"RFCH\":null,\"DESCR\":\"Internal Study\",\"COL\":\"Internal    Study\",\"DROW\":\"Internal Study\",\"ABBR\":\"Intern\"},{\"PRIO\":1,\"LANG\":2,\"REC\":\"2011-10-23T20:32:18\",\"RECUSER\":1,\"RFCH\":null,\"DESCR\":\"Internal Study\",\"COL\":\"Internal Study\",\"DROW\":\"Internal Study\",\"ABBR\":\"Intern\"},{\"PRIO\":2,\"LANG\":1,\"REC\":\"2011-10-23T20:32:31\",\"RECUSER\":1,\"RFCH\":null,\"DESCR\":\"Client Study\",\"COL\":\"Client Study\",\"DROW\":\"Client Study\",\"ABBR\":\"Client\"}]"

Now if I need to give the array of input parameter I am how to interate through the array of parameters to select. I understand we give the

 public string get( int []d)

But not sure how we need to change the query. Any hep is greatly appreciated.

There is no "ASP.NET MVC Web Services". There is ASP.NET Web API, though. — Uwe Keim
– Uwe Keim, Commented Jul 21, 2016 at 12:33

Joan Caron · Accepted Answer · 2016-07-22 05:32:04Z

2

If you keep your "strQuery" variable, you can use the IN keyword :

SELECT * FROM TABLE WHERE ID IN (id1, id2, ..., idn)

And string.Join method to format your Ids

var ints = new int[] {1, 2, 3, 4, 5};
var ids= string.Join(",", ints.Select(x => x.ToString()).ToArray());// "1,2,3,4,5"

var strQuery = $@"Select PRIO_CATEGORY_ID as PRIO,LANG_ID as LANG, REC_DATE    as REC, REC_USER as RECUSER, RFCH_ID as RFCH,DESCR,COL_DESCR AS COL,ROW_DESCR as DROW,ABBR from STCD_PRIO_CATEGORY_DESCR where REC_USER IN({ids})";

edited Jul 22, 2016 at 5:32

answered Jul 21, 2016 at 12:47

Joan Caron

1,97018 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

trx Over a year ago

Thanks Joan, I am understanding the query part but really not getting how would I iterate through the array of the input parameters

Collectives™ on Stack Overflow

ASP. Net Web API service connecting to oracle database

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related