Create a web service with the parameter that will execute the oracle query

Question

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.

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 (?)

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

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

I have istalled the Dappler .Net in to the solution and now creating the Model Class

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;
    .......

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

Broom · Accepted Answer · 2016-07-20T02:36:05.900

0

You are missing the actual execution of the query. I don't get what you're trying to return, but getting the data from the select into a DataTable will let you manipulate or display it in .NET

using Oracle.ManagedDataAccess.Client;
using System.Data;
using Newtonsoft.Json;
    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();

        OracleCommand selectCommand = new OracleCommand(strQuery, dbConn);
        OracleDataAdapter adapter = new OracleDataAdapter(selectCommand);
        DataTable selectResults = new DataTable();
        adapter.Fill(selectResults);

        dbConn.Close();
        return JsonConvert.SerializeObject(selectResults);  
    }

edited Jul 20 '16 at 02:36

answered Jul 19 '16 at 21:22

Broom

596
2
18

Thanks Broom, I tried to run the application in Internet explorer after adding the OracleCommand, OracleDataAdapter and DataTable. like http://localhost:21374/api/ProQuery it opens a JSON file ProQuery.Json it just has the below '"SELECT \r\n STCD_PRIO_CATEGORY_DESCR.DESCR, \r\n STRS_SESSION3.SESSION_NUM, \r\n Trunc(STRS_SESSION3.START_DATE), \r\n ...."' just watever query i gave in the strQuery – trx Jul 20 '16 at 00:46
you need to return the data you are looking for. What I gave you will fill selectResults with the actual data from the database that the select would produce. Check out http://stackoverflow.com/questions/17398019/how-to-convert-datatable-to-json-in-c-sharp for creating a string you can pass that can be deserialized by whoever receives the data. Edit: I edited my answer to include serializing the datatable to Json and returning that. – Broom Jul 20 '16 at 02:33
Broom, the issue is it is not returning the actual data, intead it is jus returning the select statement, I am not seeing the data from the database. – trx Jul 20 '16 at 12:42
Yes I did try adding 'JsonConvert.SerializeObject(selectResults);' the issue is it is not selecting the data from the Database – trx Jul 20 '16 at 16:21

Create a web service with the parameter that will execute the oracle query

1 Answers1