title: Create a web service with the parameter that will execute the oracle query
body: <p>I am exterly new to the .NET technology. I am having a confusion how to proceed with my requirement. I am having a complex Oracle query that needs to be executed when the services is called. I am just giving the sample of the query below. </p>
  
  <pre><code>SELECT 
  STCD_PRIO_CATEGORY_DESCR.DESCR AS CATEGORY, 
  STRS_SESSION3.SESSION_NUM AS SESSION_NUMBER , 
  Trunc(STRS_SESSION3.START_DATE) AS SESSION_START_DATE, 
  STRS_SESSION3.START_DATE AS SESSION_START_TIME, 
  Trunc(STRS_SESSION3.END_DATE) AS SESSION_END_DATE, 
  STRS_SESSION3.END_DATE AS SESSION_END_TIME, 
  Round((TO_DATE (TO_CHAR (STRS_SESSION3.END_DATE, 'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') - TO_DATE (TO_CHAR (STRS_SESSION3.START_DATE, 'dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi'))*1440),
  STCD_ACT_DESCR4.DESCR, 
  decode(( decode(sign(( DECODE(SIGN(( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )),-1,( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) ),( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )+( STPR_OPTIONS.VALUEN )) )),-1,'Y',0,'N','N') ), 'N', Decode (( STPR_OPTIONS.VALUEN ), '1', trunc(((   DECODE(SIGN(( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )),-1,( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) ),( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )+( STPR_OPTIONS.VALUEN )) )-1) /7)+1, '0', trunc(( DECODE(SIGN(( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )),-1,( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) ),( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )+( STPR_OPTIONS.VALUEN )) )/7)), 'Y', (trunc((( DECODE(SIGN(( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )),-1,( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) ),( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )+( STPR_OPTIONS.VALUEN )) ) +1)/ 7)-1) , -999),
   DECODE(SIGN(( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )),-1,( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) ),( Trunc(ILRS_DOSE.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )+( STPR_OPTIONS.VALUEN )),
   Trunc(ILRS_DOSE.RESULT_DATE), 
   ILRS_DOSE.RESULT_DATE, 
   STPR_STD_ANML.ANML_REF, 
   STPR_GRP.LONG_NAME, 
   decode(STPR_ANML.SEX,0,'Male',1,'Female',2,'Herm','--'), 
   STPR_STUDY.STD_REF, 
   STCD_TA_DESCR.COMMON_NAME_1, 
   STPR_DOSE_DET.TA_AMOUNT, 
   trim(STCD_ADMIN_ROUTE_DESCR.DESCR), 
   STCD_UNIT_DESCR.DESCR, 
   STCD_VEH_SOLV_DESCR.DESCR, 
   decode(sign(( DECODE(SIGN(( trunc(ILRS_ANML.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )),-1,( trunc(ILRS_ANML.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) ),( trunc(ILRS_ANML.RESULT_DATE) ) - ( trunc(STPR_STD_ANML.START_DATE) )+( STPR_OPTIONS.VALUEN )) )),-1,'Y',0,'N','N')
   FROM 
   STPR_STD_ANML, 
   STPR_ANML, 
   STPR_OPTIONS, 
   STCD_ACT_DESCR STCD_ACT_DESCR4, 
   STCD_ACT_DESCR, 
   STCD_UNIT_DESCR, 
   STCD_UNIT_DESCR STCD_UNIT_DESCR2, 
   STCD_UNIT_DESCR STCD_UNIT_DESCR10, 
   STCD_ACT STCD_ACT4, 
   STCD_ACT, 
   STCD_RESRCE_DESCR STCD_RESRCE_DESCR1, 
   STCD_RESRCE_DESCR, 
   STCD_UNIT, 
   STCD_UNIT STCD_UNIT2, 
   STCD_UNIT STCD_UNIT10, 
   STCD_STUDY_TYPE_DESCR, 
   STPR_GRP, 
   STCD_RESRCE STCD_RESRCE2, 
   STCD_RESRCE STCD_RESRCE1, 
   STCD_RESRCE, 
   STPR_DOSE_DET, 
   STCD_STRAIN_DESCR, 
   STCD_STUDY_TYPE, 
   STCD_ADMIN_ROUTE_DESCR, 
   STCD_VEH_SOLV_DESCR, 
   STPR_STUDY, 
   STCD_SPECIES_DESCR, 
   WHERE 
   ( STPR_STUDY.ID=STPR_STUDY_DET.STD_ID ) 
   AND ( STPR_STUDY_DET.STD_TYPE_ID=STCD_STUDY_TYPE.ID ) 
   AND ( STCD_STUDY_TYPE_DESCR.STUDY_TYPE_ID(+)=STCD_STUDY_TYPE.ID AND        STCD_STUDY_TYPE_DESCR.LANG_ID(+) = 1 )
   AND ( STPR_STUDY.ID=STPR_STD_SPECIES.STD_ID(+) ) 
   AND ( STPR_STD_SPECIES.SPECIES_ID=STCD_SPECIES.ID(+) ) 
   AND ( STPR_STD_SPECIES.STRAIN_ID=STCD_STRAIN.ID(+) ) 
   AND ( STCD_SPECIES.ID=STCD_SPECIES_DESCR.SPECIES_ID(+) AND STCD_SPECIES_DESCR.LANG_ID(+) = 1 )
   AND ( STCD_STRAIN.ID=STCD_STRAIN_DESCR.STRAIN_ID(+) AND    STCD_STRAIN_DESCR.LANG_ID(+) = 1 )
   AND ( STPR_ROOM.STD_ID(+)=STPR_STUDY.ID ) 
   AND ( STCD_RESRCE.ID(+)=STPR_ROOM.RESRCE_ID ) 
      AND ( STCD_RESRCE.ID=STCD_RESRCE_DESCR.RESRCE_ID(+) AND         STCD_RESRCE_DESCR.LANG_ID(+) = 1
       ) 
           AND ( STPR_STUDY.ID=STPR_KEY_PERS.STD_ID(+)AND STPR_KEY_PERS.STD_DIRECTOR (+) =1 )
     AND ( STPR_KEY_PERS.USER_ID=STCD_USER.ID(+) ) 
     AND ( STPR_STD_ANML.STD_ID=STPR_GRP.STD_ID AND STPR_STD_ANML.GRP_ID = STPR_GRP.ID )
     AND ( STPR_GRP.ID=STPR_DOSE_DET.GRP_ID(+) ) 
     AND ( STPR_DOSE_DET.TREATMT_ID=STPR_TREATMT.ACT_ID(+) ) 
     AND ( STPR_TREATMT.ADMIN_ROUTE_UNIT_ID=STCD_UNIT.ID(+) ) 
     AND ( STCD_UNIT.ID=STCD_UNIT_DESCR.UNIT_ID(+) AND        STCD_UNIT_DESCR.LANG_ID(+) = 1 )
     AND ( STPR_TREATMT.VEH_ID=STCD_VEH_SOLV.ID(+) ) 
  
     AND ( STPR_TREATMT.ADMIN_ROUTE_ID=STCD_ADMIN_ROUTE.ID(+) ) 
     AND ( STCD_ACT_DESCR.ACT_ID(+)=STCD_ACT.ID AND
     STPR_STUDY.STD_REF IN (?) 
  </code></pre>
  
  <p>Where STPR_STUDY.STD_REF IN (?) in the query that needs to be the parameter in the web service. What I have been trying is to create a MVC ASP.NET application with the WEB API. Trying to create a controller with </p>
  
  <pre><code>using System.Net;
  using System.Net.Http;
  using System.Web.Http;
  namespace ProConn.Controllers
  {
  public class ProQueryController : ApiController
  {
      public string Get()
      {
          var strQuery = @"The above query";
          OracleConnection dbConn = new OracleConnection("DATA SOURCE=ABC;PASSWORD=ABCD;PERSIST SECURITY INFO=True;USER ID=ABCDE");
  
          dbConn.Open();
  
          dbConn.Close();
  
          return strQuery;
      }
  }
  }
  </code></pre>
  
  <p>I have istalled the Dappler .Net in to the solution and now creating the Model Class </p>
  
  <pre><code>using System;
  using System.Collections.Generic;
  using System.Linq;
  using System.Web;
  
  namespace ProConn.Models
  {
   public class ProStudyData
   {
      public string CATEGORY;
      public int SESSION_NUMBER;
      public DateTime SESSION_START_DATE;
      public DateTime SESSION_START_TIME;
      public DateTime SESSION_END_DATE;
      public DateTime SESSION_END_TIME;
      .......
  </code></pre>
  
  <p>I am not sure if I am doing things correctly, I have looking to forums and articles and trying to put together. Also I need to return the data in the JSON format, in the other question it was suggested to use DTO. Can anyone please guide me how can I proceed</p>