Page #56 (Chapter 7 - Controlling Program Flow) | Visual Basic Developer[ap]s Guide to ASP and IIS

	Chapter 7 - Controlling Program Flow
	Visual Basic Developers Guide to ASP and IIS
	A. Russell Jones


	Repurposing WebClasses


	You've already written a WebClass that requires a sign-on and password in the SecuredSite project (see Chapter 5). Because the requirements for sign-on/password protected sites are so similar, you can reuse or repurpose that WebClass in this project, with minor changes. Repurposing differs from reuse; repurposing implies that you will change the code, whereas reuse implies that you can use the same code, without changes, in multiple situations.


	Add the Signon WebClass from the SecuredSite project to this project. Be sure to save it in the AccountInfo directory. You will need to copy the signon.htm template manually.


	Warning		You should be aware that VB stores file locations in the project (.vbp) file. When you add an existing file, VB dutifully writes the location of the file into the project directory. If you then make changes to the file, you are changing the original file, not a copy. In some cases—such as a module containing well-tested, generic routines—that may be exactly what you want, but usually, you'll want to modify the code. To avoid overwriting the original file when you add an existing file, you should immediately right-click the filename, then select Save <File> As from the pop-up menu. Save the file in your current project directory.


	Think back to the SecuredSite application. In that project, you saved sign-on and password information in a file. In this application, sign-on and password information resides in a database. Therefore, to repurpose the WebClass, you'll need to modify the code that reads and writes sign-on information. Open the code window for the Signon WebClass. Click the Search icon and search for the findSignon function.


	The original findSignon function opened a text file and looked for a specific sign-on. If it found the sign-on, it optionally created a comma-delimited string containing the sign-on and password. You'll do two things differently with this database version. Instead of reading a file, you'll open a database connection and attempt to retrieve the record containing the sign-on entered by the user. If the record exists, the function returns True. Optionally, rather than returning a comma-delimited string, you'll return the record data in a Dictionary object. Replace the original findSignon function with this one:


	Private Function findSignon(aSignon, Optional dRet As _

Dictionary) As Boolean

Dim conn As Connection

Dim SQL As String

Dim R As Recordset

Dim F As Field

On Error GoTo Err_FindSignon

Set conn = New Connection

conn.ConnectionString = Application("ConnectionString")

conn.Mode = adModeRead

conn.CursorLocation = adUseClient

conn.Open

SQL = "SELECT * FROM Customers WHERE Signon='" & _

aSignon & "'"

Set R = conn.Execute(SQL)

If Not R.EOF Then

findSignon = True

If Not IsMissing(dRet) Then

Set dRet = recordToDictionary(R)

End If

R.Close

Set R = Nothing

conn.Close

Set conn = Nothing

Exit_FindSignon:

Exit Function

Err_FindSignon:

Err.Raise Err.Number, Err.Source, Err.Description

Resume Exit_FindSignon

End Function


	The new findSignon function does three things. First, it creates an ADO Connection object, sets its properties, and opens it. In this case, the connection string has already been created and stored in an Application variable. You'll see that code shortly:


	Set conn = New Connection

conn.ConnectionString = Application("ConnectionString")

conn.Mode = adModeRead

conn.CursorLocation = adUseClient

conn.Open


	The ConnectionString property must be set before you open the connection. I've opened the connection in read-only mode (adModeRead). Although you don't have to set this property to open a read-only recordset, the default value is adModeUnknown, which decreases performance. You don't have to set this property explicitly, but it improves readability. I set the CursorLocation property to adUseClient. The default CursorLocation is adUseServer, which tells SQL Server to create a server-side cursor. Server-based cursors are less network-intensive than client-side cursors, but are also less flexible.


	Second, the function sets up a query. Queries sent to the database server as strings are called dynamic SQL. When you send a query this way, the database engine parses the string, creates an execution plan, compiles the query, and (finally) retrieves the data. It's much more efficient to create stored procedures whenever possible. On the other hand, it's sometimes harder to update stored procedures than to update dynamic SQL. I'll show you how to call a stored procedure in Chapter 10; for now, we'll live with the performance penalty. The SQL query attempts to retrieve an entire row from the Customers table where the sign-on matches the sign-on entered by the user.


	SQL = "SELECT * FROM Customers WHERE Signon='" & _

aSignon & "'"


	Note that the variable aSignon is the parameter passed from the Signon_frmSignon routine. Because it's a string value, you need to surround it with single quotes.


	You use the Execute method of the Connection object to execute a dynamic SQL statement. The Execute method returns a Recordset object containing the data retrieved from the database if the sign-on matched a sign-on in the Customers table.


	Set R = conn.Execute(SQL)


	The most convenient way to package a database row is in a Dictionary object, because it has key-value pairs—similar to the fields of a recordset. You can use the Recordset itself instead, but Recordset objects carry a great deal of overhead, and (unless you disconnect them from the Connection object) use expensive database connection resources. By transferring the data to a Dictionary, you can get in, get the data, and get out of the database as fast as possible, thus freeing the connection for another request. The new findSignon function calls a general purpose routine called recordToDictionary that moves a row of data from a Recordset to a Dictionary object:


	Private Function recordToDictionary(R As Recordset) _

As Dictionary

Dim d As Dictionary

Dim F As Field

Set d = New Dictionary

d.CompareMode = TextCompare

If R.State = adStateOpen And Not R.EOF And _

Not R.BOF Then

For Each F In R.Fields

If (F.Attributes And adFldLong) = _

adFldLong Then

If Not IsNull(F.value) Then

d.Add F.Name, F.GetChunk(F.ActualSize)

Else

d.Add F.Name, vbNullString

End If

Else

If Not IsNull(F.value) Then

d.Add F.Name, F.value

Else

d.Add F.Name, vbNullString

End If

Set recordToDictionary = d

End Function


	The function loops through each field in the recordset. It tests explicitly for fields that contain text (Memo in Microsoft Access) or image (OLE Object in Microsoft Access) data, because you retrieve data from those field types using the GetChunk method rather than the Value method. It also tests for null field values and substitutes null strings instead.


	When the findSignon function returns, the original Signon_frmSignon code expects an array. You'll need to change a few lines. In the original code, replace the lines that dimension the array and retrieve the password from the array with this code:


	Original Code:


	Dim arrSignonInfo as variant

arrSignonInfo = Split(arrSignonInfo, ",")

If StrComp(aPassword, arrSignonInfo(1), vbTextCompare) _

= 0 Then


	New Code:


	Dim dSignonInfo as Dictionary

If StrComp(aPassword, dSignonInfo("Password"),

vbTextCompare) = 0 Then


	It's convenient and considerably more readable to retrieve the password by name than by position. Code dependent on position makes me nervous because the position of data within resources tends to change over time. For instance, I see a great deal of code in which people use index numbers instead of field names to access field values in recordsets—for example, rs(0) rather than rs("UserID"). True, using the index numbers is marginally faster, but that should be an optimization of last resort; the loss in readability outweighs the performance gain in all but the most demanding circumstances. If you control both the code and the database, and you're absolutely sure that you'll never add a column to the database and that you'll never change the order in which a SQL statement or stored procedure returns the data, it's probably OK to write code based on the position of a resource. I just feel sorry for the poor programmer who inherits the code.