Introduction
This article tells you how to build a 3-tier application by using COM and XML. Here, I'm going to show you how to build an ATL COM component that returns data in XML format, and use XSLT to transform it into a HTML page, and at last, how to sort and split the data into pages locally.
You need a local data source to build this demo. In my case, I use the employee table of pub database in a local SQL server.
Application Architecture
As the picture shown above, the whole system will split into four parts. User browses the ASP web page, then ASP will create the COM component on remote server, and issue the command to retrieve data from SQL database. The COM component then contacts SQL DB using ADO, after the record set returns, it wraps them into XML format, and passes back to ASP. ASP dynamically outputs the XML data from the return value of COM component, while adding some head information to notify Internet Explorer using a specific XSL file to render it. At this time, the user can see a web page containing a table of data, he can sort the data by clicking on the table head, or navigate pages by clicking on the button above the table.
Although there are four parts in this application, I built it on my own PC, that means I installed Windows 2000 Advance Server with IIS, and a local SQL Server.
COM
OK, let's talk about the code step by step. The first thing is to build a COM component, which uses ADO to retrieve data in SQL Server.
Launch VC, use ATL COM AppWizard to create a new project, name it as xmlrs. Press finish to accept all the default setting values. Insert a simple object by using ATL Object Wizard (Insert -> New ATL Object), name it as xmlrsobj, and accept all the default settings. Add the methods of this object by right clicking on the Ixmlrsobj icon on the class view. Select Add Method, and then enter ConnectDB in the method name field, and [in] BSTR bstrConn in the parameter field. Add the following two methods in the same way:
GetXmlrs ([in] int intRow, [out, retval] BSTR *xmlrs)
CloseDB (void)
Fill in the code into these functions. The following code should be added to the CPP file, since the ADO is employed to retrieve data from SQL server.
#import "C:\Program Files\Common Files\System\ADO\msado15.dll" \
named_guids no_namespace rename("EOF", "EndOfFile")
COM connects to SQL server using the connection string passed from ASP client, then the COM component gets the specific record set by a SELECT command. COM component wraps up the record set into XML format in turns.
Now it's time to build your COM object. VC would register the DLL file after a successful build, then you can use it locally. If you want to deploy it on another computer, you have to do extra administrator works according to the DCOM or COM+ you use here.
ASP
The ASP file is simple. Just create the COM object we built above, calling the GetXmlrs method of this object, and use response.write to output all the returned data. To make Internet Explorer understand what information it gets from the ASP pages, we should set the content type to text/xml, XML version, and specify the XML style sheet.
Response.ContentType = "text/xml"
response.write "<?xml version=""1.0""?>"
response.write "<?xml-stylesheet type=""text/xsl"" href=""xml.xsl""?>"
Don't forget to modify the connection string in the ASP file, so that it can work in your environment.
XSL
XSL is a kind of render mechanical for XML, it is something like CSS to HTML. By using XSL, we can separate the data rending from the data itself.
-
HTML Code to Build the Page Skeleton
<HTML>
<BODY id="listing">
<TABLE width="60%" cellspacing="0">
<TR>
<TD> fName </TD>
<TD> lName </TD>
<TD> hire_date </TD>
</TR>
<TR>
<TD> fName </TD>
<TD> lName </TD>
<TD> hire_date </TD>
</TR>
</TABLE>
</BODY>
</HTML>
With these lines of HTML code, we build a web page with an empty table, where we will fill the XML data.
-
XSL Transfer Code
Now update the code, by using the XSL code, we filled the table with the data stored in XML.
...
<xsl:for-each select="Recordset/Record" order-by="fName">
<xsl:if test="context()[index() $ge$ 0 and index() $lt$ 10]">
<TR>
<TD><xsl:value-of select="fName"/></TD>
<TD><xsl:value-of select="lName"/></TD>
<TD><xsl:value-of select="hire_date"/></TD>
</TR>
</xsl:if>
</xsl:for-each>
...
Take a look on these codes. The code pair <xsl:for-each select="Recordset/Record" order-by="fName"> and </xsl:for-each> means repeat all the code between the pair with every Record node in the XML file, and all the data would be sort by fName field. Recordset/Record is following the XPath syntax, which means the Record of root Recordset.
Next code pair: <xsl:if test="context()[index() $ge$ 0 and index() $lt$ 10]"></xsl:if>. This code pair is used to implement page split function, which will filter out the data not in current page. The default value will only show the first 10 records. When processing xsl:if command, computer will first calculate the value of test item, if it contains at least one node of data, the test item will be true. In this case, context() and index() are XSL built-in functions, context() will return the current node, and index() returns the sequence number of current node according to the order. [...] means a filter, and $ge$ and stands for >= in a well formed HTML page, meanwhile $lt$ stands for <. So the code context()[index() $ge$ 0 and index() $lt$ 10] means for the current node, if its index value is >= 0 and < 10, then test item will return true, in turns the HTML code between xsl:if pair will be rendered.
Last but not least, the code <xsl:value-of select="fName"/> returns the current value of fName. With the above code, it will render the content of the table during the looping.
-
CSS Style Sheet to Make the Output More Beautiful
XSL style sheet can be combined with CSS style sheet to make the page more beautiful. The following code defines the CSS styles used in this example. Update the XSL files according to the following lines:
...
<STYLE>
BODY {margin:0}
.row {font:8pt Verdana; border-bottom:1px solid #CC88CC}
.header {font:bold 9pt Verdana; cursor:hand;
padding:2px; border:2px outset gray}
.up {background-color:#DDFFDD; cursor:hand;}
.down {background-color:#FFDDDD;}
</STYLE>
...
<TD class="header"> fName </TD>
<TD class="header"> lName </TD>
<TD class="header"> hire_date </TD>
...
<TD class='row'> fName </TD>
<TD class='row'> lName </TD>
<TD class='row'> hire_date </TD>
...
<TD class='row'><xsl:value-of select="fName"/></TD>
<TD class='row'><xsl:value-of select="lName"/></TD>
<TD class='row'><xsl:value-of select="hire_date"/></TD>
...
-
JavaScript to Sort and Split All the Data Into Pages
By now, you've got a static page with a table of XML data. We make it interactive with user by adding the following JavaScripts. These scripts helps user navigate the data in pages, while there is too much data in the table.
There are four script functions:
init() -> Used to init the first page;prevpg() -> Move to the previous page;nextpg() -> Move to the next page;sort (key) -> Sort the records according to new keywords
Then we will hook the functions with HTML items.
...
<BODY id="listing" onLoad="init()">
...
<p class="down">Click on the head to sort!</p>
<div class="up" onClick="prevpg()">prev page</div>
<div class="up" onClick="nextpg()">next page</div>
...
<TD class="header" onClick="sort('fName')">fName</TD>
<TD class="header" onClick="sort('lName')">lName</TD>
<TD class="header" onClick="sort('hire_date')">hire_date</TD>
...
Pay attention on the code pair ![CDATA[...]]. This is because the well formed HTML will encode <, thus we have to put our script into ![CDATA[...]] pair to avoid encoding.
Now you should have a 3 tier application powered by COM and XML. Enjoy it. You will find when you navigate through the pages, the order will not change until you set a new order!
License
This article has no explicit license attached to it, but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below.
A list of licenses authors might use can be found here.
