Dapper for creating a Web Service

I am new to .NET and creating the web services. 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, 
STRS_SESSION3.SESSION_NUM, 
Trunc(STRS_SESSION3.START_DATE), 
STRS_SESSION3.START_DATE, 
Trunc(STRS_SESSION3.END_DATE), 
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) ) - ( 
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, 
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
 STPR_STUDY.STD_REF IN (?)

I cam across using Dapper, but my question is how the Dapper will return the result when the service is called (it is possible have the data returned in JSON format) also to create the POCO class I am not sure if the type of each element is the select can be string or we will need to give the appropriate datatype. My questions might be silly, I am new to this and looking for some help. Thanks

Solution

You would normally make a model (class with props), and then get Dapper to populate it. You could then set the properties to be strings, ints, whatever matches your DB column types.

For example:

 List<Customer> customers = (List<Customer>)conn.Query<Customer>("SELECT * FROM Customer");

In this example the Customer object is a class, and the customers list is a list of customer objects. You can then recurse through the data.

Here's an example of what the Customer class might contain:

    public class Customer
    {
        public int CustomerId;
        public string Username;
        public string FirstName;
        public string LastName;
    }

You could then serialise to JSON using:

string jsonString = Newtonsoft.Json.JsonConvert.SerializeObject(customers);

Hope this helps :)