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

admin

! L& w/ v. x+ I

Technique  
* A) {! J* G; F7 \  bUse 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.


C#  
# `- q5 b$ r8 ^) i+ _Innovator myInnovator = this.newInnovator();
Item results = myInnovator.applySQL(
  "select login_name,first_name,last_name,email " +
  "from [user] " +
  "order by last_name,first_name");
+ f4 [/ G5 |; K& |' X
string content = "" +
  "<style type='text/css'>" +
    "table {background:#000000;}" +
    "th {font:bold 10pt Verdana; background:#0000FF; color:#FFFFFF;}" +
, G" ?4 s* r$ W0 G: l+ ]' N$ `    "td {font:normal 10pt Verdana; background:#FFFFFF;}" +
    "caption {font:bold 14pt Verdana; text-align:left;}" +
( A. R  f# Q$ t4 k$ H. F' o  "</style>" +
    "<table id='tbl' border='0' cellspacing='1' cellpadding='2' datasrc='#itemData'>" +
. R& i% z# N1 k& M    "<caption>User Directory</caption>" +
    "<thead>" +
      "<tr>" +
6 }2 ?7 C8 X; H& d8 x2 S. |        "<th>Login Name</th>" +
! m. X0 C, s( A, o$ i! R. I        "<th>First Name</th>" +
        "<th>Last Name</th>" +
  c. K7 i/ e2 j        "<th>EMail</th>" +
      "</tr>" +
* u3 r" w7 c8 [8 L8 U8 V, e    "</thead>" +
2 C, B# {: |6 n    "<tbody>";
     
6 O+ H9 v! J8 N; JXmlNodeList users = results.dom.SelectNodes("//recordset/row");
& r. g1 H7 ~& `$ _. Bfor (int i=0; i<users.Count; i++) {
content += "<tr><td>" + ((users.SelectSingleNode("login_name") != null) ?  
                       users.SelectSingleNode("login_name").InnerText : "") + "</td>";   
0 Z! K9 ], d/ R0 p1 k   
* U. `* }; D1 I( a( ~* H7 k5 S2 {; r4 r! ? content += "<td>" + ((users.SelectSingleNode("first_name") != null) ?
                       users.SelectSingleNode("first_name").InnerText : "") + "</td>";   
6 ^" B7 H8 C, W9 r" L* J7 b: l   
* o) U: G' j" v& k content += "<td>" + ((users.SelectSingleNode("last_name") != null) ?
                       users.SelectSingleNode("last_name").InnerText : "") + "</td>";   
! \5 r( ^7 ^8 e. `1 O$ l         
' m1 I% Q/ E* d, T content += "<td>" + ((users.SelectSingleNode("email") != null) ?
                       users.SelectSingleNode("email").InnerText : "") + "</td></tr>";   
1 M+ a9 C; O7 |3 T' y) h$ G4 D% E   
}
# S# }3 h+ B0 S( J
content += "" +
) o# y/ K' l# Q9 c! Y6 X    "</tbody>" +
1 p9 H. R1 Y4 {6 V" c3 h8 R# D  "</table>";
# B1 n$ E- C" r% h
) E& r# p- B( @, }& s! w, T$ ireturn myInnovator.newResult(content);