SQL templates and template instantiation

Requirements: Need to draw a data source DataTable, I know how SQL and HttpRequest, through the way the SQL template to instantiate the parameters in the matching httprequest to query SQL, and finally return to the DataTable

1. SQL templates

1 SELECT2SUBSTR (placename, InStr (Placename,'-') +1) scene name,3COUNT (*) Number of cells,4 SUM (Alarmcount) traffic TB,5 ROUND (SUM (UserCount)) online users,6 SUM (totalflow) high-load cell number,7To_char (ROUND (AVG. downprbrate),2),'fm9999990.00') ||'%'cell serious alarm,8 SUM (burthencount) downlink prb utilization9 From V_childscene_real_infoTenWHERE Placeid =':%placeid%' One GROUP by Stime, Placeid, placename AORDER by high load cell number DESC

2. SQL instantiation

1 Private stringCreatesql (stringsql, HttpRequest req) {2ilist<string> list =Newlist<string>();3             if(SQL. IndexOf (":%") >-1)4             {5                 varMat = regex.matches (sql,"(? <=:%) [^%]+ (? =%)");6                 foreach(Match Iteminchmat)7                 {8 list. ADD (item. Value);9                 }Ten                 foreach(varIteminchlist) One                 { Asql = SQL. Replace (":%"+ Item +"%", Req[item]); -                 } -             } the             returnSQL; -}

3. Query data and return

1  using (varnew  oracledb ()) 2 {3     return db. executedatatable (SQL); 4 }

SQL templates and template instantiation