Dapper for creating a Web Service

Question

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

Andy-Delosdos · Accepted Answer · 2016-07-18T20:38:48.940

1

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

edited Jul 18 '16 at 20:38

answered Jul 18 '16 at 20:25

Andy-Delosdos

3,560
1
12
25

Thanks Delosdos. But dont we have to use the get and the set for those columns? How do we use the Dapper in to our application just referencing them ? – trx Jul 19 '16 at 03:09
1

Dapper will automatically populate the object for you, if the DB column names matches the class property names. So you would add "public string DESCR;" and "public string SESSION_NUM;" to your class, for example – Andy-Delosdos Jul 19 '16 at 08:06
Thank you Delosdos. You are awesome, but few Columns are like this in the select satement query '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)' Do I eed to give them also the same way – trx Jul 19 '16 at 13:45
1

You do need field names for all columns in your query. You could give them an alias. e.g. SELECT xyz AS [alias]. Replace 'xyz' with your logic, and replace 'alias' with the field name you want to use in your class. – Andy-Delosdos Jul 19 '16 at 14:14

Dapper for creating a Web Service

1 Answers1