|
Worlds Fastest Listbox with XML,
Application Variables
by Jamiel Humayun Jhumayun@liveperson.com
and Ian Payne ivpayne@wagga.fsnet.co.uk
Sometimes data (like a HTML list box
or table) is displayed on many pages of a website. In fact, the database generated
list box is displayed thousands of times a day, and the database is queried
every time, but it is unnecessary. The database it is drawn from is not
changing thousands of times a day.
In the following example any page that displays the
database needs to only transform the XML application variables, not hit the database.
Very speedy. If the data changes or gains new records, a trigger mechanism could
be added to sense data changes and only rebuild the list box if records were
added or changed. Here is a demo script that displays the listboxes without
re-querying the database.
DisplayCity.asp is the main script.
Simple enough.
filename=/learn/test/xml/DisplayCity.asp
<!--#include file="XMLLibrary.asp"-->
<html>
<head>
<title>Display XMLized Listbox</title>
</head>
<body bgcolor="#FFFFFF">
<b>City Table:</b> <br>
<% DisplayCityTable() %><br><br>
<b>City List:</b> <br>
<% DisplayCityList() %><br><br>
</body>
</html>
ListBox.xsl is the style
sheet to give it a list box look
filename=/learn/test/xml/ListBox.xsl
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<xsl:template match="/">
<select>
<xsl:for-each select="xml/rs:data/z:row">
<option><xsl:value-of select="@city"/></option>
</xsl:for-each>
</select>
</xsl:template>
</xsl:stylesheet>
Table.xsl is the style sheet
to give it a list box look
filename=/learn/test/xml/Table.xsl
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/TR/WD-xsl">
<xsl:template match="/">
<table cellpadding="2" cellspacing="0" border="1" width="100%">
<xsl:for-each select="xml/rs:data/z:row">
<tr><td><xsl:value-of select="@city"/></td></tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>
XMLlibrary.asp is the
library that does most of the work.
filename=/learn/test/xml/XMLlibrary.asp
<%
adPersistXML=1
Function GenXML (objRs)
set stmXML = CreateObject("ADODB.Stream")
If Not objRs.EOF Then
objRs.Save stmXML, adPersistXML ' needs ADO 2.5
End If
GenXML = stmXML.ReadText
Set stmXML = Nothing
End Function
Function GetCityXML ()
' Database connection - Usually kept in an include file, but presented for display purposes
strConnection = "DSN=student;UID=student;PWD=magic;"
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open strConnection
strSQL = "SELECT DISTINCT city FROM publishers"
Set objRs = objConn.Execute (strSQL)
GetCityXML = GenXML(objRs)
Set objConn = Nothing
Set objRs = Nothing
End Function
Function RenderXML (strXML,strStyleSheet)
Set objXML = CreateObject("MSXML.DOMDocument")
Set objXSL = CreateObject("MSXML.DOMDocument")
objXML.loadXML(strXML)
objXSL.load(Server.MapPath(strStyleSheet))
Response.Write objXML.transformNode (objXSL)
Set objXML = Nothing
Set objXSL = Nothing
End Function
Sub DisplayCityTable()
if Application("City") = "" then Application("City") = GetCityXML()
strStyleSheet = "table.xsl"
Call RenderXML (Application("City"),strStyleSheet)
End Sub
Sub DisplayCityList()
if Application("City") = "" then Application("City") = GetCityXML()
strStyleSheet = "listbox.xsl"
Call RenderXML (Application("City"),strStyleSheet)
End Sub
%>
XMLcacheclear.asp is called
to force a fresh database display.
filename=/learn/test/xml/XMLcacheclear.asp
<%
Application("City") = ""
%>
|