【Aras二次开发源码】使用ApplySQL进行快速查询

admin

( J5 b3 x6 W6 c8 G  |/ n0 G' o: R
# g( e, G, W# N, U6 \3 u! V& p
2 P6 A$ H& a' Q1 D% p1 g9 Z) eTechnique  
Use the  Innovator.applySQL( … )  method to submit SQL direct to the database.  This
recipe returns the XML from the  applySQL()  method and forms HTML for a table to
display the data.
5 D, @9 H: @8 a2 c
# P4 k- N4 @. q6 a  }
$ M9 E2 n4 l5 m1 R' F" gC#  
Innovator myInnovator = this.newInnovator();
Item results = myInnovator.applySQL(
6 X6 f. x  Z, V' c- J  "select login_name,first_name,last_name,email " +
4 \7 e/ \' V7 j0 C  "from [user] " +
5 f8 X! r& I1 O! V  "order by last_name,first_name");
' T$ G% L/ \: g! ~2 G# W! [
9 F3 b% M0 t8 j0 Q, Sstring content = "" +
  "<style type='text/css'>" +
    "table {background:#000000;}" +
' G/ b0 G- O+ F; c    "th {font:bold 10pt Verdana; background:#0000FF; color:#FFFFFF;}" +
    "td {font:normal 10pt Verdana; background:#FFFFFF;}" +
    "caption {font:bold 14pt Verdana; text-align:left;}" +
  "</style>" +
. c7 i$ _/ K2 D, D) R8 V    "<table id='tbl' border='0' cellspacing='1' cellpadding='2' datasrc='#itemData'>" +
    "<caption>User Directory</caption>" +
    "<thead>" +
* S5 L9 a9 W( o/ a: @8 L      "<tr>" +
        "<th>Login Name</th>" +
        "<th>First Name</th>" +
8 s# x$ c6 O, f4 b# j        "<th>Last Name</th>" +
        "<th>EMail</th>" +
9 K( T' S0 h, k- x! X% d      "</tr>" +
    "</thead>" +
    "<tbody>";
" ^3 F2 L9 u- m0 b! R. P7 s     
XmlNodeList users = results.dom.SelectNodes("//recordset/row");
for (int i=0; i<users.Count; i++) {
content += "<tr><td>" + ((users.SelectSingleNode("login_name") != null) ?  
                       users.SelectSingleNode("login_name").InnerText : "") + "</td>";   
   
content += "<td>" + ((users.SelectSingleNode("first_name") != null) ?
                       users.SelectSingleNode("first_name").InnerText : "") + "</td>";   
; H  A/ R) s1 N! N, p6 |   
content += "<td>" + ((users.SelectSingleNode("last_name") != null) ?
                       users.SelectSingleNode("last_name").InnerText : "") + "</td>";   
         
* r& S) d8 q+ R( i content += "<td>" + ((users.SelectSingleNode("email") != null) ?
  ^1 z9 |& j8 Q( G2 \4 t" t+ Q                       users.SelectSingleNode("email").InnerText : "") + "</td></tr>";   
; _# S) t: Y) @4 Y8 D; ]* s   
1 Y8 l8 m; V2 R3 f/ N- G& M}
2 ~! k, |: o+ G3 ?4 @
* v" M$ u& g" q$ _4 W+ p: [content += "" +
2 y, x% l, i6 ^0 l    "</tbody>" +
  "</table>";
+ ]% ~4 u9 y1 ^+ H* B6 d
return myInnovator.newResult(content);