Page #30 (Chapter 3 - Building ASP Applications)

	Chapter 3 - Building ASP Applications
	Visual Basic Developers Guide to ASP and IIS
	A. Russell Jones

Copyright 1999 SYBEX Inc.

---

	Tying It All Together—Caching Table Data

	Imagine that, instead of changing a color value, you wanted to display the contents of a table. Sure, you can query a database for each request, but if the table data didn't change often, wouldn't it be nice if you could "cache" the table in an HTML file? Whenever the table data changed, you could re-create the HTML file.

	I'm going to present the project here despite the data access requirements. Those of you who are not familiar with ActiveX Data Objects (ADO) may want to return to this example after reading Chapter 8, "Maintaining State in IIS Applications." You can also read up on ADO by taking a look at the VB Developer's Guide to ADO by Mike Gunderloy (Sybex, 1999).

	This project consists of one ASP file. Each time you run the file, it lets you select a table from the pubs database. When you submit your selection, the program checks to see whether it already has the table data cached in an HTML file. If the table cache file exists, the ASP file simply returns the contents of the HTML file. If the table cache file does not exist, the program reads the table from the database, writes a cache file, then displays the contents of the file. The program also refreshes cached data if you pass a Refresh=True parameter in the URL. Administrators could use this feature to force the cached data to refresh.

	Note		The pubs database comes with SQL Server. If you don't have SQL Server, you can download a Microsoft Access database containing the tables of the pubs database from the Sybex Web site.

	Note		To download code (including the Access database), navigate to http://www.sybex.com. Click Catalog and search for this book's title. Click the Downloads button and accept the licensing agreement. Accepting the agreement grants you access to the downloads page for the book.

	The complete code for the program is in Listing 3.2 at the end of this chapter. Just like the code in the previous project in this chapter, the selectTable.asp file is a form that submits to itself. The first part of this file contains the logic needed to differentiate between a request containing form data (Submitted=True) and an unsubmitted request, before the user has selected a table to display. Unlike the previous project, though, this one gets all its information from a database by

	using ADO. To read database information, you need to open a connection to the database:

	Set conn = Server.CreateObject("ADODB.Connection")

conn.ConnectionString="pubs"

conn.CursorLocation= adUseClient

conn.Mode= adModeRead

conn.Open

	In this case, you create the Connection object and set its ConnectionString property to a valid Data Source Name (DSN), pubs. Next, you set the Connection object's CursorLocation property to adUseClient, which tells the Connection object that you're going to use open database connectivity (ODBC) client cursors rather than SQL Server server-side cursors for any recordsets you retrieve using the connection. Because you're only going to be reading data, not updating, you set the Mode property to adModeRead. Finally, you open the connection using the Open method of the Connection object.

	Next, you want to retrieve information about the tables in the database so you can populate the drop-down list. To do that, you need to get a recordset from the connection.

	set R = Server.CreateObject("ADODB.Recordset")

R.CursorLocation=aduseclient

call R.Open("SELECT Name FROM SysObjects WHERE " & _

     Type='U' ORDER BY Name ASC",conn, _

     adOpenForwardOnly, adLockReadOnly,adCmdText)

end if

	This code creates a Recordset object, tells it to use a client-side cursor, and to get a list of all tables in the pubs database. The rest of the code in the file displays the data from the recordset. It's fairly straightforward and similar to the code in the previous project, so I won't spend any time on it.

	When the user selects a table from the drop-down list, the code redirects to the relative URL showTable.asp. You'll use redirection extensively in Web applications to provide messages and feedback, and to process requests based on user selections or input. The Response.Redirect method sends a header to the client browser. The header essentially means that the browser can find the information requested at a new address. The browser immediately makes a new request to the server for the specified page. So, (right now) redirection requires a round-trip to the client.

	Note		Microsoft will soon provide server-side redirection, which won't require the round-trip to the client and therefore will be much more efficient.

	The showTable.asp file contains almost no static HTML—just the bare minimum markup and two placeholders for the table data.

	<html>

<head>

</head>

<body>

<!—-Start—-><!—-End-—>

</body>

</html>

	The first thing the showTable.asp file does is check the TableName parameter. If the parameter is empty, the program redirects the user "back" to the selectTable.asp file:

	if Request.QueryString("TableName") = "" then

     Response.Redirect "selectTable.asp"

end if

	This code exists because, as I stated earlier, you don't know and can't control the order in which a user might request files from your application. If users simply type the URL to showTable.asp into their browsers, the program wouldn't know which file to display. Simply displaying whichever table the file may currently contain might be confusing; therefore, the code forces the user to make a choice before displaying any table.

	Next, it caches the TableName parameter in a local variable:

	requestedTablename= Request.QueryString("TableName")

	If the requested table name is same as the previous request, the file simply shows the data already cached in the file; otherwise, it reads and formats the table data. It also refreshes the table data if you pass a "Refresh=True" parameter in the URL. You'll need some way to refresh the file if the table data changes. An "optional" parameter such as this gives you the opportunity to refresh the file if, for example, an administrator changes the data. Another way of doing this is to keep track of the data by looking at the highest Identity value (AutoNumber in Access), the date/time at which the table was last changed, or via a trigger that updates a row in a separate table whenever data in the main table is changed.

	In this project, you "change" the table data by selecting a different table or by passing a "Refresh=True" parameter to the ASP page:

	if requestedTablename <> tablename or _      Request.QueryString("Refresh") = "True" then

     ' open the file, change the contents to that

     ' of the new table, then save the file.

End if

	The process of opening a file is similar each time. You query the Server object by using the MapPath function to obtain the physical path for the file.

	set fs = server.CreateObject("Scripting.FileSystemObject")

aFilename = server.MapPath("showTable.asp")

set ts = fs.OpenTextFile(afilename,ForReading,false)

s = ts.readall

ts.close

set ts = nothing

	Tip		Microsoft's documentation states that one way to speed up sites is to limit or eliminate the use of the MapPath function. For greatest efficiency, you should cache path information in Application or Session variables when appropriate. Don't ever hard-code file paths in a Web application unless you're absolutely sure that the path will never change.

	Next, you want to replace the old table name with the selected table name. Find the old table name parameter and value in the file string. Use VB's new replace function to perform string replacements.

	s = replace(s, "tablename=" & chr(34) & tablename & _

     chr(34), "tablename=" & chr(34) & requestedTablename _

     & chr(34), 1, 1, vbBinaryCompare)

	Now replace the table data. Because browsers ignore comments, you can conveniently use them as markers inside HTML files. I've used the two comment tags <!—Start—-> and <!—End—-> to mark the beginning and end positions for the table data. To make the replacement, you need to find the markers.

	do

     startPos = instr(startPos+1, s, _

         "<!—-Start",vbBinaryCompare)

loop while mid(s, startPos + 9 ,1) <> "-"

startPos=startPos + len("<!—-Start") + 3

do

     endPos = instr(endPos + 1, s, _

     "<!—-End", vbBinaryCompare)

loop while mid(s, endPos + 7, 1) <> "-"

	To get the data, you create a database connection and read the data from the selected table.

	set conn = server.CreateObject("ADODB.Connection")

conn.ConnectionString="pubs"

conn.CursorLocation=aduseclient

conn.Mode= adModeRead

conn.Open

set R = Server.CreateObject("ADODB.Recordset")

R.CursorLocation=aduseclient

set R = conn.Execute("SELECT * FROM " & _

     requestedtablename,,adCmdText)

	Whenever you retrieve data, you should check to make sure that the data you think is there is actually there. The Execute method returns a recordset regardless of whether it retrieves any data. Always check the recordset's End-of-File (EOF) property. The property will return True if the recordset is empty—that is, if no rows were retrieved. If the recordset contains data, then you can format the column headers by using the Field.Name property to get the name of each column.

	if not R.EOF then

     tableData="<table align='center' border='1' _

     width='95%' COLS='" & R.Fields.Count & "'>"

     sTmp="<TR>"

     for each F in R.Fields                            

         sTmp = sTmp & "<TD><B>" & F.Name & "</B></TD>"

     next

     sTmp = sTmp & "</TR>"

     tableData = tableData & sTmp

end if

	At the end of this loop, the recordset is still on the first row. You loop until the EOF property becomes true, placing each field value in a table cell. Note that this is a nested loop; the outer loop creates the rows while the inner loop fills the columns with data.

	while not R.EOF

     sTmp = "<TR>"

     for each F in R.Fields

         if (F.Attributes and adFldLong) = adFldLong then

             if F.Type=adLongVarBinary then

                 sTmp = sTmp & _

                 "<TD valign='top'>(binary)</TD>"

             elseif F.ActualSize=0 then

                 sTmp = sTmp & _

                 "<TD valign='top'> </TD>"

             else

                 sTmp = sTmp & "<TD valign='top'>" & _

                 F.GetChunk(F.ActualSize) & "</TD>"

             end if

         else

             if isNull(F.Value) then

                 sTmp = sTmp & _

                 "<TD valign='top'> </TD>"

             else

                 sTmp = sTmp & _

                 "<TD valign='top'>" & F.Value _

                 & " </TD>"

             end if

         end if

     next

     sTmp = sTmp & "</TR>"

     tableData = tableData & sTmp

     R.MoveNext

Wend

	You need to decide what to do if the recordset does not contain any rows. In this case, the program returns a message in the first table row.

	tableData= "There is no data in the table: " & _

     requestedTablename & ".<BR>"

	Finally, don't forget to close the recordset and the connection and set them to Nothing. Setting them to Nothing frees up the memory. Strictly speaking, you don't have to do this at the end of a page because the ASP engine destroys the objects and frees the memory for variables created during page processing when the page ends. However, it's good practice for you to clean up explicitly. It also frees the memory somewhat sooner than the ASP engine can.

	R.Close

set R = nothing

conn.Close

set conn= nothing

	Finally, concatenate the table data into the file string between the start and end position markers in the file, then write the file string to disk.

	s = left(s, startPos) & tableData & _

     mid(s, endPos)

set ts = fs.OpenTextFile(afilename,ForWriting,false)

ts.write s

ts.close

set ts = nothing

set fs = nothing

	Now the file is ready to display, so you can redirect to the file you just wrote.

	Response.Redirect "showTable.asp?TableName=" & _

     requestedTablename

	Listing 3.2: Code for Providing Fast Access to Table Data ASP Project (selectTable.asp and showTable.asp)

	***************************************************

' The selectTable.asp file

***************************************************

<%@ Language=VBScript %>

<% option explicit %>

<%Response.Buffer=True%>

<%

dim submitted

dim tablename

dim R

dim conn

submitted=(Request("Submitted") ="True")

if submitted then

     Response.Redirect "showTable.asp?TableName=" & _
          Request("TableName")

Else

     set conn = server.CreateObject("ADODB.Connection")

     conn.ConnectionString="DSN=pubs;UID=sa;PWD="

     conn.CursorLocation=aduseclient

     conn.Mode= adModeRead

     conn.Open

     set R = Server.CreateObject("ADODB.Recordset")

     R.CursorLocation=aduseclient

     call R.Open("SELECT Name FROM SysObjects WHERE Type='U' _
ORDER BY Name ASC",conn,adOpenForwardOnly,adLockReadOnly,adCmdText)

end if

%>

<html>

<head>

<meta name="generator" Content="Microsoft Visual Studio 6.0">

</head>

<body>

<form name="frmTable" method="post"
     action="selectTable.asp?Submitted=True">

<table align="center" border="1" width="80%" cols="2">

     <tr>

          <td align="center" colspan="2" bgcolor="#FF0000">

               Select Table

                    </font>             

          </td>

     </tr>

     <tr>

          <td align="left" colspan="2" bgcolor="#FFFFFF">

               Select a table name from the list, then click
               the "Display Table" button.

          </td>

     </tr>

     <tr>

          <td align="right" valign="top" width="20%">

               <b>Table</b>:

          </td>

          <td align="left" valign="top" width="80%">

               <select name="TableName">

                    <%

                    do while not R.EOF

                         Response.Write "<option "

                         if R("Name").value = tableName then

                              Response.Write "selected "

                         end if

                         Response.Write "value='" & R("Name") &
                              "'>" & R("Name") & "</option>"

                         R.movenext

                    loop

                    R.close

                    set R = nothing

                    conn.Close

                    set conn=nothing

                    %>

               </select>

          </td>

     </tr>

     <tr>

          <td align="center" valign="bottom" colspan="2">

               <input type="submit" value="Display Table">

          </td>

     </tr>

</table>

</form>

</body>

</html>

***************************************************

' The showTable.asp file

***************************************************

<%@ Language=VBScript %>

<% option explicit %>

<%Response.Buffer=true%>

<%

dim submitted

dim tablename

dim tabledata

dim sTmp

dim requestedTablename

dim afilename

dim fs

dim ts

dim s

dim startPos

dim endPos

dim conn

dim R

dim F

tablename="authors"

if Request.QueryString("TableName") = "" then

     Response.Redirect "selectTable.asp"

end if

requestedTablename= Request.QueryString("TableName")

if requestedTablename <> tablename or _

     Request.QueryString("Refresh") = "True" then _

     set fs = server.CreateObject _

         ("Scripting.FileSystemObject")

     aFilename = server.MapPath("showTable.asp")

     set ts = fs.OpenTextFile(afilename,ForReading,false)

     s = ts.readall

     ts.close

     set ts = nothing

     s = replace(s, "tablename=" & chr(34) & _

         tablename & chr(34), "tablename=" & chr(34) & _

         requestedTablename & chr(34),1,1,vbBinaryCompare)

     do

          startPos = instr(startPos+1, s, _

          "<!—-Start",vbBinaryCompare)

     loop while mid(s, startPos + 9 ,1) <> "-"

     startPos=startPos + len("<!—-Start") + 3

     do

          endPos = instr(endPos + 1, s, _

             "<!-—End", vbBinaryCompare)

     loop while mid(s, endPos + 7, 1) <> "-"

     set conn = server.CreateObject("ADODB.Connection")

     conn.ConnectionString="pubs"

     conn.CursorLocation=aduseclient

     conn.Mode= adModeRead

     conn.Open

     set R = Server.CreateObject("ADODB.Recordset")

     R.CursorLocation=aduseclient

     set R = conn.Execute("SELECT * FROM " & _

         requestedtablename,,adCmdText)

     if not R.EOF then

          tableData="<table align='center' border='1' " & _

             "width='95%' COLS='" & R.Fields.Count & "'>"

          sTmp="<TR>"

          for each F in R.Fields             

               sTmp = sTmp & "<TD><B>" & F.Name & _

                   "</B></TD>"

          next

          sTmp = sTmp & "</TR>"

          tableData = tableData & sTmp

          while not R.EOF

               sTmp = "<TR>"

               for each F in R.Fields

                    if (F.Attributes and adFldLong) = _

                        adFldLong then

                        if F.Type=adLongVarBinary then

                             sTmp = sTmp & _

                                "<TD valign='top'>" & _

                                "(binary)</TD>"

                        elseif F.ActualSize=0 then

                             sTmp = sTmp & _

                             "<TD valign='top'>" & _

                             " </TD>"

                        else

                             sTmp = sTmp & _

                             "<TD valign='top'>" & _

                             F.GetChunk(F.ActualSize) & _

                             "</TD>"

                         end if

                    else

                        if isNull(F.Value) then

                             sTmp = sTmp & _

                             "<TD valign='top'>" & _

                             " </TD>"

                        else

                             sTmp = sTmp & _

                             "<TD valign='top'>" & _

                             F.Value & " </TD>"

                         end if

                    end if

               next

               sTmp = sTmp & "</TR>"

               tableData = tableData & sTmp

               R.MoveNext

          wend

     else

          tableData= "There is no data in the table: " _

             & requestedTablename & ".<BR>"

     end if

     R.Close

     set R = nothing

     conn.Close

     set conn= nothing

     s = left(s, startPos) & tableData & mid(s, endPos)

     set ts = fs.OpenTextFile(afilename,ForWriting,false)

     ts.write s

     ts.close

     set ts = nothing

     set fs = nothing

     Response.Redirect "showTable.asp?TableName=" & _

        requestedTablename

end if

%>

<html>

<head>

</head>

<body>

<!—-Start-—>

<!—-End-—>

</body>

</html>