If you have a webserver that gets a lot of hits and you're hitting the database to retrieve the same data every time the same page loads to serve a new visitor, that's not going to scale well. And if you're going back to the database again for the SAME customer just because they've now hit a new page and you need the same data from the database all over again, well, that's just plain dumb!  I've been guilty of it -- it's just being lazy, that's all. You say, "Yeah, well when we have 10,000 hits a day, we'll re-write it".

You can cache frequently used data in Application scope easily and safely - provided you use a COM object that is free threaded. Microsoft have created a free- threaded "Lookup Table" object that they apparently use a lot, but after playing with it, I wasn't really that impressed. You can't really use it like an "Application scope session object", you have to load the data off the filesystem. However, there are a number of authors who have sample code or freeware "dictionary" objects that they've been kind enough to distribute without charge to the developer community at large. One of those I like is Caprock Consuting's free-threaded Dictionary object. Written in ATL and at only 44K, its well worth a look. Here's their URL:  http://www.caprockconsulting.com/comsoftware.asp , and I've included both a copy of the DLL and their excellent CHM helpfile in the download zip for this article.

What we're going to do here is show how to load a recordset into Application scope (using the Dictionary object of course) by serializing it into an XML string using the native Save, adPersistXML method and the ADO Stream object, and then retrieve the recordset from any ASP page and "walk the XML DOM" to get the data we need for that page.

First of course, register the component which will expose its ProgID, "caprock.dictionary". We'll instantiate the component into Application scope in our global.asa file using the OBJECT META Tag:

<OBJECT RUNAT=Server SCOPE=Application ID="Dict" ProgID="caprock.dictionary" ></OBJECT>

Now here's a page that retrieves an ADO recordset object, serializes it to a valid XML string, and stores it in the Application Dictionary object where it immediately becomes available to all the pages of your site. (We're gonna do this in Javascript, folks, no more VBScript for me, it died and went to heaven when .NET arrived.):

<%@Language="JScript" %>
<%

if(dict==null){
Application("dict")=Server.CreateObject("caprock.dictionary");
var dict=Application("dict");
}
var adOpenStatic = 3,adLockReadOnly = 1,adPersistXML = 1, adCmdText = 0x0001;
var sql = "SELECT Employeeid, lastname, firstname, title FROM employees order by lastname "
var Conn = Server.CreateObject("ADODB.Connection");
Conn.Open("Provider=SQLOLEDB.1;User ID=sa;Initial Catalog=northwind;Data Source=(local)");
var rstObj = Server.CreateObject("ADODB.Recordset");
try {
rstObj.Open(sql,Conn,adOpenStatic,adLockReadOnly,adCmdText);
if (rstObj.RecordCount > 0) {
var oStream = Server.CreateObject("ADODB.Stream");
oStream.Open;
rstObj.Save(oStream,adPersistXML);
oStream.Position = 0;
dict("recordset")=oStream.readText;

} else {
Response.Write("<root><b>No Records Found</b></root>");
}
}
catch (e) {
Response.Write("<root><b>Error: " + e.description + "</b></root>");
}

Response.write("Recordset is now stored in Application(\"dict\")");
%>
<BR> <a href=updatexml.asp>Get dict object</a> from a new page, using Application("dct")
object.

So what we've done above is set our Application("dict") object (after making sure it exists) to the page variable "dict". We then proceed to create and open a recordset containing the results of some of the fields in the Employees table from the sample Northwind SQL Server database.

Then, we open an ADO Stream and Save the recordset to the stream object as an XML String. Finally, we store the XML recordset (which is now in the Stream) into the dictionary object: (dict("recordset")=oStream.readText). The recordset is now safely stored in application state, it won't lock our IIS down to the single thread, and it is available to every page and every user of our site, without ever hitting the database again! I hope this makes sense, cause I don't want my poor old SQL Server to work any harder than it really needs to, especially doing dumb things like loading the same old recordset again and again for every customer.

Now the "proof of concept" page, "updateXML.asp":

<%@Language="JScript" %>
<%
if(dict==null){
Application("dict")=Server.CreateObject("caprock.dictionary");
dict=Application("dict");
}

var xmlDoc=Server.CreateObject("MSXML2.DomDocument.3.0");
xmlDoc.loadXML(dict("recordset"));
// nail down the rs:data section which is where the records are stored in the MS ADO XDR schema
// and stick it in a nodeList that we can traverse...
var tempNode=xmlDoc.selectSingleNode("//rs:data");
var newNode=tempNode.childNodes;
var len = newNode.length;
var tmp;
var output;
// let's just build our own HTML table from scratch, walking the DOM...
output= "<table align="center" cellspacing="2" cellpadding="2" border="0" ><TR>";
for(var i=0;i<=len-1;i++){
tmp=newNode(i);
output+="<TD bgcolor=#ffcc66>" + tmp.getAttribute("firstname") + "</td>";
output+="<TD bgcolor=#ffcc66>" + tmp.getAttribute("lastname") + "</TD>";
output+="<TD bgcolor=#ffcc66>" + tmp.getAttribute("title") + "</TD></TR>";
}
output+="</TABLE>";
Response.write(output);
output = "";
%>

What we do above is (once again) make sure our "dict" Application variable exists, and then simply instantiate a new XML DomDocument object and use its LoadXML method to load the XML STRING which is held in the dict("recordset") dictionary key.

Next, we nail down the rs:data nodeList of our XML which is where the actual records live, and we kinda "walk the nodes" using a combination of DOM scripting and XPATH to build ourselves a nice little proof-of-concept HTML Table.

And that's how you can store gobs of data in Application state! You can store STATE lists for your drop-down listboxes, site configuration data, lists of links, and yes, even whole recordsets. You can take the extra time and load off your database, and make your site SCALE.

I guess it all boils down to the words of my grandmother, Liilian Bromberg (who lived to be 100):
"Peter Darling, if we were all meant to get along, there would be no people who wait to start writing their check until all the groceries are rung up."   Don't expect your webserver and database to do everything for you!

Happy programming.

Download the code that accompanies this article.

Peter Bromberg is an independent consultant specializing in distributed .NET solutionsa Senior Programmer / Analyst at in Orlando and a co-developer of the NullSkull.com developer website. He can be reached at info@eggheadcafe.com