Scott Stanfield
Vertigo Software, Inc.
September 1998
Summary: Discusses the four server components used by the FMCorp Web application to handle business logic. (36 printed pages) Includes:
Introduction
Overview of Server Components
Use Cases
Visual Modeler Diagram
Component Design
Implementation Language
Generic Code
Login Methods
Upload Methods
Approve/Decline Methods
Development Tips and Tricks
Conclusion
About the Author
More Information
The FMCorp Web application uses four middle-tier components to handle business logic. The FMCorp architecture document describes the components' role in a Microsoft® Windows® Distributed interNet Applications Architecture (Windows DNA) Web application.
The four components are described in Table 1. The component column lists the component ProgID that is used by the Web pages and test application. The third column states whether a component must be part of a transaction. In general, the components that make modifications to the database require a transaction.
Table 1. FMCorp Server Components
| Component | Comments | Transaction support |
| FMCorp.ExpenseReports | High-level routines to parse and approve/decline expense reports. | Required |
| FMCorp.ExpenseWriteDB | Three methods to add a new expense report and its items, and to change the status of an expense report. | Required |
| FMCorp.ExpenseReadDB | Read-only routines to login, get the UserID from an e-mail address, and return expense report details. | Not supported |
| FMCorp.Mail | Sends simple mail messages using the SMTP service. | Not supported |
Before the first line of code was written, the developers asked themselves, "What should these components do?" We came up with the following feature list:
The middle-tier components expose functionality to the Web pages. So it is appropriate to infer what the Web pages will need. We learned that the methods we needed to support fell into three categories:
We later determined that there are four classes of Web pages, three of which use the middle-tier components directly:
Uses ExpenseReports.ApproveReport().
Uses ExpenseReports.SubmitXMLFile().
Uses ExpenseReadDB.Login(), ExpenseReadDB.NTLogin(), and ExpenseReadDB.GetUserID().
Doesn't use any custom components. Active Server Pages (ASP) scripts work directly with the database through Microsoft's ActiveX® Data Object (ADO) library.
Based on the preceding scenarios, we generated a diagram using the Visual Modeler tool, as shown in Figure 1.
Figure 1. Visual Modeler diagram
We isolated all the read-only methods in a single component called FMCorp.ExpenseReadDB. These methods do not require transactional support from Microsoft Transaction Server (MTS). Their lifetime is, however, managed by MTS.
The methods that execute stored procedures that change the database were placed in FMCorp.ExpenseWriteDB. This component, along with a higher-level wrapper component, FMCorp.ExpenseReports, do require transactions. The final component, FMCorp.Mail, simplifies the process of sending e-mail.
We specifically did not burden these components by adding support for every read-only database access. Instead, we reasoned that there would be no additional overhead for ASP pages to make these requests directly to the database. Because they didn't need transaction support, placing them in Microsoft Visual Basic® components would only complicate the entire architecture.
For that reason, you will find some stored procedures are used only by the components, while others are used strictly by the Web pages. The ones used by the Web pages typically take one or two parameters (UserID or ExpenseReportID) and return a select statement that spans several tables.
The four components were written in the Visual Basic 6.0 development system. They could have been written in any language that supports the Component Object Model (COM). In fact, a few of the components were originally written in the Microsoft Visual C++® development system and Microsoft Active Template Library (ATL).
While there are a few peculiar aspects in building Visual Basic 6.0 COM components, they were much easier to build than their ATL counterparts. The ATL code was about twice as long, and more difficult to write, than the Visual Basic version.
Every interface method that talks to ADO uses the same set of generic code. There is more work to do if we want the component to be part of a transaction. The generic code will be explained in two sections: Login() and ApproveReport().
Instead of explaining each component separately, I'll explain each method in the three main contexts:
When a user first visits the FMCorp site, they are prompted for their e-mail address and password. If the user has a valid Microsoft Windows NT® account, they can choose to let the browser authenticate their network credentials automatically.
Both login methods require their own middle-tier implementation that calls stored procedures on the database. Let's start with the first method, ExpenseReadDB.Login().
Login() tests the validity of an e-mail and password combination by using the sp_Login stored procedure. It is used by the LoginAction.asp Web page.
Every public method in all the components takes a data source name (DSN) as the first parameter. The DSN is simply a string that contains enough information to connect to a database. It includes the database server name, user name, password, default database, database driver, and so on. The DSN we use for all connections in FMCorp is defined in one place, the global.asa file in the Web project. The following code shows the DSN:
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Application_OnStart
'==Visual InterDev Generated - startspan==
'--Project Data Connection
...
Application("FMCorp_ConnectionString") = "DRIVER=SQL Server;SERVER=ahi;UID=sa;
APP=Microsoft Development Environment;WSID=AHI;DATABASE=FMCorp;User Id=sa;PASSWORD=;"
...
End Sub
We don't type this string in by hand. If you read the FMCorp Setup documentation, you'll see that we start the Web project with an empty global.asa file. You use the database connection dialog box in the Microsoft Visual Studio® development system to create the DSN.
The e-mail address and password are the next two parameters passed by the Web page. A simple HTML form gathers these two text strings. The final three parameters (UserID, FullName, and ManageCount) are outgoing parameters. These values are filled in if the e-mail and password combinations check out.
Like every method that deals with a database, an ADO Command object is created, called cmd. The With cmd Visual Basic operator allows us to leave out the word "cmd," which would normally be in front of every Command method.
The first three lines in the With block set up the database connection and the stored procedure name. The Refresh method makes a round-trip to the server to discover the names of each stored procedure parameter, along with its data type. There is an alternate way to code this that avoids a round-trip. The round-trip version is easier to read, but it does require more work on the database server. An alternate way to set up the Email parameter, for instance, would look like this:
cmd.Parameters.Append .CreateParameter("@email", adVarChar, adParamInput, 255, email)
You have to know ahead of time the type and size of each parameter. This method also requires you to specify the outgoing parameters, even though you don't use them until after cmd.Execute.
After setting up the parameters, the stored procedure is executed with cmd.Execute. The three return values (UserID, FullName, and ManageCount) are passed back to the caller through the ByRef method parameters.
If the UserID is not 0, the stored procedure call was successful. The FullName will be a concatenated string consisting of the first and last name. The ManageCount is simply a number that indicates how many employees are managed by this particular user.
If there are no run-time errors, the cmd object is explicitly freed and the function exits. If there are run-time errors, the code after Login_Error will be executed. The return value here is set to false, the error is bubbled up using Err.Raise, and control returns to the normal function exit procedure.
You will see that almost all components use a similar structure:
Public Function Login(ByVal dsn As Variant, ByVal Email As Variant, _
ByVal Password As Variant, ByRef UserID As Variant, _
ByRef FullName As Variant, ByRef ManageCount As Variant) As Boolean
On Error GoTo Login_Error
Dim cmd As New ADODB.Command
Dim ret As Variant
With cmd
.ActiveConnection = dsn
.CommandType = ADODB.adCmdStoredProc
.CommandText = "sp_Login"
.Parameters.Refresh
.Parameters("@Email").Value = Email
.Parameters("@Password").Value = Password
.Execute
UserID = .Parameters("@UserID")
FullName = .Parameters("@FullName")
ManageCount = .Parameters("@ManageCount")
End With
' sp_Login indicates failure by setting @UserID = 0
If UserID = 0 Then
Login = False
Else
Login = True
End If
Login_Exit:
If Not cmd Is Nothing Then
Set cmd = Nothing
End If
Exit Function
Login_Error:
Login = False
Err.Raise Err.Number, "FMCorp.ExpenseReadDB.Login", Err.Description
Resume Login_Exit
End Function
LoginAction.asp uses the following method as shown in the following Visual Basic Scripting Edition (VBScript) code:
<%@ LANGUAGE=VBScript %>
<%
Response.Buffer = true
Response.Clear
Session("UID")= ""
email = trim(Request("login"))
If email <> "" Then
set login = Server.CreateObject("FMCorp.ExpenseReadDB")
dim UserID, ManageCount, FullName
if login.Login(Application("FMCorp_ConnectionString"), email,
trim(Request("password")), UserID, FullName, ManageCount) then
Session("UID") = UserID
Session("name") = FullName
if ManageCount > 0 then
Session("Manager") = true
end if
else
Response.Redirect("login.asp")
end if
set login = nothing
Response.Redirect ("home.asp?refresh=1")
else
Response.Redirect ("login.asp")
end if
%>
Login() tests the validity of an email and password combination by using the sp_Login stored procedure. It is used by the LoginAction.asp Web page.
The NTLogin() method is slightly different from Login(). It only needs one parameter in addition to the DSN, the Windows NT authenticated login string. If you are running this Web site through Microsoft Internet Explorer and your browser attempts a connection to a Web page that specifically disallows anonymous access, the browser will negotiate your Windows NT credentials with the Web server.
By the time control gets to the LoginNT.asp Web page, the Web server has already received the Windows NT login information. The string looks like "domain\user." We simply take this string and pass it to ExpenseReadDB.NTLogin() to see if it corresponds to a particular UserID. This method returns the same parameters as the preceding Login().
Public Function NTLogin(ByVal dsn As Variant, ByVal Login As Variant, _
ByRef UserID As Variant, ByRef FullName As Variant, _
ByRef ManageCount As Variant) As Boolean
On Error GoTo NTLogin_Error
Dim cmd As New ADODB.Command
Dim ret As Variant
With cmd
.ActiveConnection = dsn
.CommandType = ADODB.adCmdStoredProc
.CommandText = "sp_NTLogin"
.Parameters.Refresh
.Parameters("@NTLogin").Value = Login
.Execute
UserID = .Parameters("@UserID")
FullName = .Parameters("@FullName")
ManageCount = .Parameters("@ManageCount")
End With
If UserID = 0 Then
NTLogin = False
Else
NTLogin = True
End If
NTLogin_Exit:
If Not cmd Is Nothing Then
Set cmd = Nothing
End If
Exit Function
NTLogin_Error:
NTLogin = False
Err.Raise Err.Number, "FMCorp.ExpenseReadDB.NTLogin", Err.Description
Resume NTLogin_Exit
End Function
The primary job of the whole system is to allow employees to submit expense reports. They do this by filling in a Microsoft Excel template on their local computer. When they save the XLS file, a special macro in the template saves the file as an XML document. Later, when the employee uploads the XML file to the server, the following methods and components parse the XML file and add it to the database.
SubmitXMLFile() is probably the longest and most complicated method. It is responsible for adding an expense report to the database. Some of the details are delegated to helper methods in other components, which will be described later.
Note By the time SubmitXMLFile is called, the posting acceptor has already transferred the XML file from the client's computer to the post folder on the server.
SubmitXMLFile can be summarized with the following pseudo-code:
Add some error checking, transaction server methods, and variable declarations, and you'd have this code:
Public Function SubmitXMLFile(ByVal dsn As Variant, ByVal XMLFilePath As Variant, _
ByRef ExpenseReportID As Variant, ByRef StatusCode As Variant) As Boolean
On Error GoTo SubmitXMLFile_Error
Dim context As ObjectContext
Set context = GetObjectContext
Dim scode As Variant
' Get the description, email and XMLDocument object from the XML File
Dim xml As IXMLDocument
Dim Email As String
Dim Description As String
If InitialParse(XMLFilePath, xml, Email, Description) = False Then
scode = "Unable to parse XML file '" & XMLFilePath & "'"
GoTo SubmitXMLFile_Error
End If
' Create the two database components
Dim readdb As FMCorp.ExpenseReadDB
Dim writedb As FMCorp.ExpenseWriteDB
If Not (context Is Nothing) Then
Set readdb = context.CreateInstance("FMCorp.ExpenseReadDB")
Set writedb = context.CreateInstance("FMCorp.ExpenseWriteDB")
Else
Set readdb = CreateObject("FMCorp.ExpenseReadDB")
Set writedb = CreateObject("FMCorp.ExpenseWriteDB")
End If
' Does this email correspond to a valid UserID?
Dim UserID As Long
UserID = readdb.GetUserID(dsn, Email)
If UserID = 0 Then
scode = "Unable to find a user with email '" & Email & "'"
GoTo SubmitXMLFile_Error
End If
' Create a new expense report
Dim erid As Long
If writedb.NewExpenseReport(dsn, UserID, Description, erid, scode) = True Then
Dim root As IXMLElement
Dim item As IXMLElement
Dim icat As String
Dim idesc As String
Dim idate As String
Dim icost As String
Dim eiid As Long
Set root = xml.root
For Each item In root.children.item("ITEMS").children
icat = item.children.item("TYPE").Text
idesc = item.children.item("DESCRIPTION").Text
idate = item.children.item("DATE").Text
icost = item.children.item("COST").Text
If writedb.AddExpenseItem(dsn, erid, idesc, idate, icost, _
icat, eiid, scode) = False Then
GoTo SubmitXMLFile_Error
End If
Next
Else
GoTo SubmitXMLFile_Error
End If
SubmitXMLFile = True
ExpenseReportID = erid
If Not (context Is Nothing) Then
context.SetComplete
End If
SubmitXMLFile_Exit:
If Not (xml Is Nothing) Then
Set xml = Nothing
End If
If Not (context Is Nothing) Then
Set context = Nothing
End If
If Not (readdb Is Nothing) Then
Set readdb = Nothing
End If
If Not (writedb Is Nothing) Then
Set writedb = Nothing
End If
StatusCode = scode
Exit Function
SubmitXMLFile_Error:
' check cmd object ActiveConnection for NativeErrors
SubmitXMLFile = False
If Not (context Is Nothing) Then
context.SetAbort
End If
Resume SubmitXMLFile_Exit
End Function
You might notice the liberal use of GoTos in this method and just about every other interface we expose. This programming style is mostly due to the error handling syntax of the Visual Basic "On Error" construct. At the top of the SubmitXMLFile, we tell the Visual Basic run time to jump to the SubmitXMLFile_Error marker if anything strange happens. This could occur if one of the components couldn't be created, or one of the components raised an error or aborted the transaction.
The Error block sets the return value to false. Next, if the component is part of a transaction, it calls SetAbort to signal MTS to abort the entire transaction. The Resume statement jumps back to the normal exit point, SubmitXMLFile_Exit.
The Exit block releases any components that may have been created by setting them to "nothing." Finally, error messages in code are returned to the caller through the last parameter, StatusCode.
Once again, this pattern of Error blocks and Exit blocks will be repeated in almost all methods.
Now that you understand the Exit and Error blocks, the rest of the code is straightforward.
The first block enlists the InitialParse helper method to examine the version tag in the XML file. If the XML file passes this initial step, the Email and Description will be extracted. If the XML parser detects this is an invalid file, InitialParse will return false.
The rest of the code uses two FMCorp components. These components are instantiated differently if SubmitXMLFile is part of a transaction. If so, readdb and writedb are created within the current object context. This allows ExpenseWriteDB to inherit the current object context. Otherwise, the objects are created using the standard CreateObject call.
This two-way creation makes it easy to debug the components with the Visual Basic test program without needing the Windows NT 4.0 Service Pack 4 that allows MTS component debugging.
The next block of code ensures that the e-mail address in the XML file matches a real user in the user table. A return value of 0 indicates no match was found.
Next, a new expense report record is created using the description from the XML file, the UserID returned from GetUserID and the current DSN. If successful, the IXMLDocument interface allows us to enumerate the individual expense items in a loop. Each item is added to the ExpenseItems table through AddExpenseItem.
SubmitXMLFile is the fundamental reason why FMCorp is so successful. Why? A lot of things can go wrong in this method—and I'll show you why it doesn't matter:
Now we can't prevent these problems from occurring, but we can handle them gracefully. And the right way to handle these cases is to wrap everything in a transaction.
Consider the following case: Dolores uploads a valid XML file containing three expense report items from her trip to Las Vegas, Nevada (InitialParse). Her UserID is pulled out of the database using her e-mail address as a unique key (GetUserID). A new expense report is generated, say, with an ExpenseReportID of 42 (NewExpenseReport). The first expense item, a hotel room at the Marriott, is added to the database with 42 as the foreign key ExpenseItem.ExpenseReportID (AddExpenseItem).
Then, for example, we'll say that the next line item has a typo. It says she rented a Ford Mustang for $1500. Because she has a maximum expense report limit of $500, the tr_ExpenseItems_iu trigger rolls back the stored procedure that tried to add the offending rental car to ExpenseItems.
What do we do with ExpenseReportID 42 and the hotel room? Absolutely nothing. The ExpenseReports and ExpenseWriteDB components require a transaction. When the trigger rolled back the transaction, ExpenseWriteDB picked up on the fact that the query failed. It calls the MTS object context method SetAbort to signal to the other parties that all bets are off. Then ExpenseReports.SubmitXMLFile exits.
Notice that the component doesn't have to "clean up after itself." Microsoft SQL Server™ 6.5 uses Distributed Transaction Controller (DTC) to communicate with MTS. The Web page that initiates the call to SubmitXMLFile is tagged at the top to load all the components in one transaction. Because the components are registered and controlled by MTS, everything—from the Web page down to the trigger—functions as one transaction.
This example was fairly simple, but imagine if SubmitXMLFile wrote to your Accounts Payable database. The more components you add to the picture, the tougher it is to keep everything synchronized. With MTS, all you have to do is a bit of housekeeping with SetComplete and SetAbort to keep everyone notified of the transaction state. Everything else is automatic.
Now for something much simpler. How exactly is the new expense report created?
NewExpenseReport is simply a middle-tier wrapper for the stored procedure sp_NewExpenseReport. It takes a DSN, the UserID, and a description for the new report, and returns the new ExpenseReportID. If there are any problems along the way, StatusCode will contain a string with an error message:
Public Function NewExpenseReport(ByVal dsn As Variant, ByVal UserID As Variant, _
ByVal Description As Variant, ByRef ExpenseReportID As Variant, _
ByRef StatusCode As Variant) As Boolean
On Error GoTo NewExpenseReport_Error
Dim helper As New CHelper
helper.Init (dsn)
With helper.cmd
.CommandText = "sp_NewExpenseReport"
.Parameters.Refresh
.Parameters("@UserID").Value = UserID
.Parameters("@Description").Value = Description
.Execute
ExpenseReportID = .Parameters("@ERID")
End With
NewExpenseReport = True
helper.SetComplete
NewExpenseReport_Exit:
helper.Cleanup
Exit Function
NewExpenseReport_Error:
NewExpenseReport = False
StatusCode = helper.SetAbort()
Resume NewExpenseReport_Exit
End Function
NewExpenseReport uses a custom class called CHelper to initialize a database connection with the DSN. The name of the stored procedure is set in the ADODB.Command object.
All three ExpenseWriteDB methods use a helper class, called CHelper, to assist with the housekeeping. Without CHelper, a lot of boilerplate code can get in the way of the parameter wrangling.
The source code for CHelper is small:
Public context As ObjectContext
Public cmd As ADODB.Command
Public scode As Variant
Public Function SetAbort() As String
If cmd.ActiveConnection.Errors.Count > 0 Then
Dim error As ADODB.error
For Each error In cmd.ActiveConnection.Errors
scode = "(" & error.NativeError & ") " & error.Description
Next
Else
scode = Err.Description
End If
If Not (context Is Nothing) Then
context.SetAbort
End If
SetAbort = scode
End Function
Public Sub SetComplete()
If Not (context Is Nothing) Then
context.SetComplete
End If
End Sub
Public Sub Cleanup()
If Not (cmd Is Nothing) Then
Set cmd = Nothing
End If
If Not (context Is Nothing) Then
Set context = Nothing
End If
End Sub
Public Sub Init(ByVal dsn As String)
Set context = GetObjectContext
Set cmd = CreateObject("ADODB.Command")
scode = ""
cmd.ActiveConnection = dsn
cmd.CommandType = ADODB.adCmdStoredProc
End Sub
An instance of CHelper manages the database connection, the MTS object context, and the status string. Its usage requires the Init function to be called before accessing the public member variables. SetComplete and SetAbort mimic the MTS commands with a little more error checking. If for some reason the object context can not be obtained (such as when the code is exercised with the Visual Basic test program), the code still works smoothly.
AddExpenseItem inserts a new record into the ExpenseItem table. It needs the following parameters:
The method returns the new ExpenseItemID and a StatusCode in the two ByRef parameters. If there are any errors or the query fails, the function returns false.
If you compare this function to NewExpenseReport, they only differ in the name of the stored procedure and parameters. The error handling is identical:
Public Function AddExpenseItem(ByVal dsn As Variant, ByVal ExpenseReportID As Variant, _
ByVal Description As Variant, ByVal ItemDate As Variant, _
ByVal ItemCost As Variant, ByVal Category As Variant, _
ByRef ExpenseItemID As Variant, ByRef StatusCode As Variant) As Boolean
On Error GoTo AddExpenseItem_Error
Dim helper As New CHelper
helper.Init (dsn)
With helper.cmd
.CommandText = "sp_NewExpenseItem"
.Parameters.Refresh
.Parameters("@ERID").Value = ExpenseReportID
.Parameters("@Description").Value = Description
.Parameters("@Date").Value = CDate(ItemDate)
.Parameters("@Amount").Value = CCur(ItemCost)
.Parameters("@CategoryDescription").Value = Category
.Execute
ExpenseItemID = .Parameters("@ItemID")
End With
AddExpenseItem = True
helper.SetComplete
AddExpenseItem_Exit:
helper.Cleanup
Exit Function
AddExpenseItem_Error:
AddExpenseItem = False
StatusCode = helper.SetAbort()
Resume AddExpenseItem_Exit
End Function
Notice the item date and cost are cast to their respective data types. This is necessary because the Value property is expecting a correctly typed Variant.
GetUserID retrieves a DSN and e-mail address and returns the corresponding UserID:
Public Function GetUserID(ByVal dsn As Variant, ByVal Email As Variant) As Long
On Error GoTo GetUserID_Error
Dim cmd As New ADODB.Command
Dim ret As Variant
With cmd
.ActiveConnection = dsn
.CommandType = ADODB.adCmdStoredProc
.CommandText = "sp_GetUserID"
.Parameters.Refresh
.Parameters("@email").Value = Email
.Execute
ret = .Parameters("@UserID")
End With
If IsNull(ret) Then
GetUserID = 0
Else
GetUserID = ret
End If
GetUserID_Exit:
If Not cmd Is Nothing Then
Set cmd = Nothing
End If
Exit Function
GetUserID_Error:
GetUserID = 0
Err.Raise Err.Number, "FMCorp.ExpenseReadDB GetUserID", Err.Description
Resume GetUserID_Exit
End Function
If no UserID is found, GetUserID returns a 0. The IsNull test right after the Execute method determines if the UserID parameter is NULL. In order to really understand this check and why it is necessary, we need to examine the stored procedure code:
Create Procedure sp_GetUserID
(
@email varchar(255),
@UserID int OUTPUT
)
As
select @UserID=UserID
from users
where email = @email
What happens in the case where no corresponding User record can find an e-mail address? The output parameter, @UserID, will be NULL instead of 0.
Because this method lives in a nontransaction-enabled component, ExpenseReadDB, this method doesn't use the CHelper class.
Only managers can approve or decline expense reports. It's up to the user interface to enforce this security restriction. After the expense report status changes, an e-mail message is sent to the employee as confirmation.
ApproveReport either approves or declines a given ExpenseReportID. It sends an e-mail message to the employee who submitted the expense report indicating what happened.
After setting up the four components used by this method (object context, ReadDB, WriteDB, and Mail), ApproveReport first verifies the ExpenseReportID. It extracts the UserID and Email address by calling GetExpenseReport, a helper method in ExpenseReadDB.
Next, ApproveExpenseReport does the work to approve or decline the report, depending on the parameter, Approve. If successful, ApproveReport uses the e-mail component ExpenseMail to send a confirmation message.
The standard Error and Exit blocks are at the bottom of the code. Because ApproveReport is in a transaction-enabled component, if any step along the way fails, the whole transaction is rolled back:
Public Function ApproveReport(ByVal dsn As Variant, ByVal ExpenseReportID As Variant, _
ByVal Approve As Boolean, StatusCode As Variant) As Boolean
On Error GoTo ApproveReport_Error
' Setup the 3 FMCorp objects and the MTS object context
Dim context As ObjectContext
Set context = GetObjectContext
Dim readdb As FMCorp.ExpenseReadDB
Dim writedb As FMCorp.ExpenseWriteDB
Dim mail As FMCorp.ExpenseMail
If Not (context Is Nothing) Then
Set readdb = context.CreateInstance("FMCorp.ExpenseReadDB")
Set writedb = context.CreateInstance("FMCorp.ExpenseWriteDB")
Set mail = context.CreateInstance("FMCorp.ExpenseMail")
Else
Set readdb = CreateObject("FMCorp.ExpenseReadDB")
Set writedb = CreateObject("FMCorp.ExpenseWriteDB")
Set mail = CreateObject("FMCorp.ExpenseMail")
End If
' Make sure this ERID is valid and retrieve the associated UserID and email
Dim UserID As Integer
Dim Email As String
If readdb.GetExpenseReport(dsn, ExpenseReportID, UserID, Email) = False Then
StatusCode = "Could not locate expense report ID " & ExpenseReportID
GoTo ApproveReport_Error
End If
' Approve or decline the expense report
If writedb.ApproveExpenseReport(dsn, ExpenseReportID, _
Approve, StatusCode) = False Then
GoTo ApproveReport_Error
End If
' Send out the appropriate notification
Dim ret As Boolean
Dim subject As String
Dim body As String
If Approve Then
subject = "Expense Report " & ExpenseReportID & "approved."
body = "Your expense report (" & ExpenseReportID & ") was approved."
Else
subject = "Expense Report " & ExpenseReportID & "declined."
body = "Your expense report (" & ExpenseReportID & ") was declined."
End If
ret = mail.SendMail(Email, subject, body)
ApproveReport = True
If Not (context Is Nothing) Then
context.SetComplete
End If
ApproveReport_Exit:
If Not (mail Is Nothing) Then
Set mail = Nothing
End If
If Not (context Is Nothing) Then
Set context = Nothing
End If
If Not (readdb Is Nothing) Then
Set readdb = Nothing
End If
If Not (writedb Is Nothing) Then
Set writedb = Nothing
End If
Exit Function
ApproveReport_Error:
If StatusCode = "" Then
StatusCode = Err.Description
End If
ApproveReport = False
If Not (context Is Nothing) Then
context.SetAbort
End If
Resume ApproveReport_Exit
End Function
SendMail sends a simple e-mail message.
SendMail uses the Collaborative Data Objects (CDO) for Microsoft Windows NT Server version 1.2. This library is installed if you choose the SMTP Services when installing the Windows NT 4 Option Pack.
If you are trying to compile this component on a Windows NT Workstation, you won't have the CDO component. Remove the comment that defines the SMTP variable as an object and add a comment to the next line and compile. The DLL will compile the same, but you won't have the benefit of Microsoft IntelliSense® source code:
Public Function SendMail(ByVal toEmail As String, ByVal subject As String, _
ByVal body As String, Optional _
ByVal fromEmail As String = "admin@fmcorp.com") As Boolean
On Error GoTo SendMail_Error
' The CDONTS.NewMail object can be found under Project | References
' Microsoft CDO for NTS 1.2 Library
' in c:\winnt\system32\cdonts.dll
' You can install this option on your server by running NT 4.0 Option Pack
' and choosing the SMTP service.
' If you don't have this library installed on your development machine
' Remove the comment from the next line and comment the following line.
' Dim smtp As Object 'really a CDONTS.NewMail object
Dim smtp As CDONTS.NewMail
Set smtp = CreateObject("CDONTS.NewMail")
smtp.Send fromEmail, toEmail, subject, body, 1
Set smtp = Nothing ' not allowed to reuse it for another message
SendMail = True
Exit Function
SendMail_Error:
If Not smtp Is Nothing Then
Set smtp = Nothing
End If
SendMail = False
End FunctionDuring the course of developing these components, we came up with a list of tips and tricks that you might find useful.
The first time you run the Web site and login, Windows NT Server's MTS or Internet Information Server (IIS) technologies will lock FMCorp.dll for an undetermined amount of time. If you're in the code-compile-test cycle, it can be frustrating.
If you can't compile the DLL, try restarting the Web server. I use this batch file to make restarting IIS easy from a command prompt:
@echo off
net stop iisadmin /y
net start w3svc
net start smtpsvc
The IntelliSense feature in Visual Basic or the Microsoft Visual InterDev™ Web development system might have the component open in order to examine its type library. If you suspect that might be the case, quit the applications and try to recompile FMCorp.dll in Visual Basic.
Theoretically, IIS shouldn't have a direct lock on the components because they should be registered and controlled by MTS. You can "shut down" the FMCorp package in MTS to force it to unload, as shown in Figure 2. Otherwise, it will unload FMCorp.dll after three minutes of idle time.
Figure 2. Shut down a package
We have noticed that the first time a method in FMCorp is used (usually one of the login methods), it takes up to 10 seconds to kick in. You can configure the package to stay in memory while idle, instead of being shut down after five minutes, as shown in Figure 3. This will ensure that the package is loaded and ready to go.
Figure 3. FMCorp package properties
Here are three suggestions that make Visual Basic easier to use:
Figure 4. Changing Visual Basic options
ADO supports two methods for passing parameters to stored procedures: Parameters.Refresh and Parameters.Append.
FMCorp uses the Refresh method because it is easier to read and code, but it does have some performance problems.
Parameters.Refresh implements a run-time binding that makes building a parameter list easy. There is one downside for scalable, production-quality applications using this method to build parameter lists. Refresh causes ADO to make many round-trips to the database to determine parameter data types.
The NewExpenseReport method in ExpenseWriteDB uses the stored procedure sp_NewExpenseReport. The Refresh call makes seven separate queries—one call to sp_cursorfetch for every parameter, plus one more for sp_sproc_columns—before making the final call in Execute. You can examine the SQL trace results in Figure 5.
Figure 5. SQL trace for sp_NewExpenseReport
The Parameter.Refresh code block from NewExpenseReport is shown next. (Notice that you don't need to specify output parameters before Execute is called):
With helper.cmd
.CommandText = "sp_NewExpenseReport"
.Parameters.Refresh
.Parameters("@UserID").Value = UserID
.Parameters("@Description").Value = Description
.Execute
ExpenseReportID = .Parameters("@ERID")
End With
Parameter.Append offers an alternative method for passing parameters to stored procedures. The following code could have been used in place of the With block from the preceding code:
With helper.cmd
.CommandText = "sp_NewExpenseReport"
.Parameters.Append .CreateParameter("@UserID", adInteger, _
adParamInput, 8, UserID)
.Parameters.Append .CreateParameter("@Description", adVarChar, _
adParamInput, 255, Description)
.Parameters.Append .CreateParameter("@ERID", adInteger, adParamOutput, _
8, ExpenseReportID)
.Execute
ExpenseReportID = .Parameters("@ERID")
End With
This code is more tightly bound to the in and out parameters of the stored procedure. However, it doesn't require the eight extra calls to SQL Server. We recommend using the first method for quick prototyping, and then migrating to the CreateParameter method.
Sometimes debugging middleware components requires old tricks. You can use the scripting components to record the status of a running component. We used the following code to debug a timing problem:
Set fso = CreateObject("Scripting.FileSystemObject")
Set debugfile = fso.CreateTextFile("c:\debugwrite.log")
debugfile.writeline ("Starting " & Time)
' The CDONTS.NewMail object can be found under Project | References
debugfile.writeline ("Sstopping " & Time)
debugfile.Close
Visual Basic 6.0 coupled with Service Pack 4 for Windows NT Server allows a developer to debug components directly in Visual Basic. Only a beta version of this service pack was available during the development of FMCorp.
Throughout the development of the FMCorp components, we kept a separate Visual Basic test program around to exercise the new methods. Whenever we needed to test a new FMCorp method, we dropped another button on the FMCorpTest form and wrote "driver" code to test the method. Figure 6 shows the final version of the application.
Figure 6. FMCorp test app
The easiest way to keep the test program handy was to create a Visual Basic project group. This group contained the FMCorp COM DLL project (FMCorp.vbp) and the FMCorpTest application (FMCorpTest.vbp). Figure 7 shows the Project Group window.
Figure 7. FMCorp project group
Granted, the test program didn't look as pretty as the final version in Figure 6. Most of the time we just dragged a random button onto the form, double-clicked to drop into source mode, and then dashed out a few lines of code.
For example, in order to test the new mail method ExpenseMail.SendMail, it only takes a few lines of Visual Basic code:
Private Sub SendMail_Click()
Dim mail As New FMCorp.ExpenseMail
Dim ret
ret = mail.SendMail("scott@VertigoSoftware.com", "A Tesst Subject", _
"Hello from SendMail.")
List1.AddItem ("Sent mail using SendMail method. Return value=" & ret)
End Sub
You'll find it easier to test and track bugs using this method, rather than writing ASP VBScript code. Without Service Pack 4, though, you won't be able to test the object context functions.
The test program has a list box called List1. All driver methods dump the return value into this list box instead of the Visual Basic debug window. This allows you to run the application as a stand-alone executable and still track the results.
The test program came in handy in a number of cases. Usually, after setting up a new FMCorp test server, we'd run the compiled FMCorpTest program and click the Login button. Of course, with a hard-coded DSN, we'd sometimes have different versions of the application.
If you are testing FMCorp.dll with FMCorpTest and using the Web site at the same time, you might need to reregister FMCorp.dll with MTS. Visual Basic changes the registry settings when debugging the FMCorpTest program. You can verify this for yourself by locating FMCorp.dll in the registry and watching the changes when you press F5 to start debugging.
Reregistering FMCorp with MTS is easy. Using the MTS Explorer, simply select the four component icons in MTS and press DELETE. Then drag the FMCorp.dll back into the FMCorp package. If the package is missing, create a new one using the default settings.
Most of the components were developed using a "spiral" model. We followed the five steps listed here for each area of functionality (login, submittal, approval, and so on). Once we satisfied our testing criteria, we repeated the steps for the next main area.
For example, we needed a login procedure that uses the e-mail address and password. We wrote a rough query in step 1 that looked like this:
select UserID, FirstName + ' ' + LastName
from Users
where Email = "alice@fmcorp.com"
and password = ""
The first conversion to a stored procedure looked like this:
create Procedure sp_Login
(
@Email varchar(50),
@Password varchar(50),
@FullName varchar(100) output,
@UserID int output
)
As
select @UserID = 0
select @UserID = UserID, @FullName = FirstName + ' ' + LastName
from Users
where Email = @email
and password = @Password
go
The first select initializes the output parameter @UserID to 0. If no matching records were found, @UserID would have been NULL, making the interpretation in Visual Basic a little harder to handle.
After testing the stored procedure, we realized there were a couple of deficiencies. First, the e-mail address had to match completely. You couldn't pass "alice" expecting to match "alice@fmcorp.com". We didn't want to force users to enter the full e-mail address each time. It was acceptable to match just enough to make the login unique.
Second, we needed to know if this user managed any employees. The easiest way to do this was to return a numeric count of the number of managed employees.
The final version of the stored procedure looks like this:
create Procedure sp_Login
(
@Email varchar(50),
@Password varchar(50),
@FullName varchar(100) output,
@UserID int output,
@ManageCount int output
)
As
select @UserID = 0
select @UserID = UserID, @FullName = FirstName + ' ' + LastName
from Users
where Email like @email + '%'
and password = @Password
-- Exactly one unique user should be found, otherwise fail
if @@rowcount <> 1
begin
select @UserID = 0
return
end
select @ManageCount = count(*)
from Users
where ManagerID = @UserID
We examined the Visual Basic code that calls this stored procedure. It is repeated here:
Public Function Login(ByVal dsn As Variant, ByVal Email As Variant, ByVal Password As Variant, ByRef UserID As Variant, ByRef FullName As Variant, ByRef ManageCount As Variant) As Boolean
On Error GoTo Login_Error
Dim cmd As New ADODB.Command
Dim ret As Variant
With cmd
.ActiveConnection = dsn
.CommandType = ADODB.adCmdStoredProc
.CommandText = "sp_Login"
.Parameters.Refresh
.Parameters("@Email").Value = Email
.Parameters("@Password").Value = Password
.Execute
UserID = .Parameters("@UserID")
FullName = .Parameters("@FullName")
ManageCount = .Parameters("@ManageCount")
End With
' sp_Login indicates failure by setting @UserID = 0
If UserID = 0 Then
Login = False
Else
Login = True
End If
Login_Exit:
If Not cmd Is Nothing Then
Set cmd = Nothing
End If
Exit Function
Login_Error:
Login = False
Err.Raise Err.Number, "FMCorp.ExpenseReadDB.Login", Err.Description
Resume Login_Exit
End Function
The login code tests both Login and NTLogin. The source code looks like this:
Private Sub TestLogin_Click()
Dim l As New FMCorp.ExpenseReadDB
Dim UserID As Variant
Dim ManageCount As Variant
Dim FullName As Variant
On Error GoTo LoginError
If l.Login(g_dsn, "alice", "", UserID, FullName, ManageCount) Then
List1.AddItem ("Login successful for alice with UserID=" & UserID)
Else
List1.AddItem ("Login failed: " & Err.Description)
End If
' Try an NT Login now
If l.NTLogin(g_dsn, "fmcorp\alice", UserID, FullName, ManageCount) Then
List1.AddItem ("NTLogin successful.")
Else
List1.AddItem ("Login failed: " & Err.Description)
End If
LoginExit:
Set l = Nothing
Exit Sub
LoginError:
List1.AddItem "Error occurred while adding expense report:"
List1.AddItem Err.Description
Resume LoginExit
End Sub
The return values are posted to the list box embedded in the form. This code is also using the On Error blocks. These will catch errors raised by the components.
Two Web pages are needed to test this method. The first is a simple form that captures the e-mail name and password. The form's action posts the information to the "action" page that actually performs the login. The LoginAction.asp page looks like this:
<%@ LANGUAGE=VBScript %>
<%
Response.Buffer = true
Response.Clear
Session("UID")= ""
email = trim(Request("login"))
If email <> "" Then
set login = Server.CreateObject("FMCorp.ExpenseReadDB")
dim UserID, ManageCount, FullName
if login.Login(Application("FMCorp_ConnectionString"), email,_
trim(Request("password")), UserID, FullName, ManageCount) then
Session("UID") = UserID
Session("name") = FullName
if ManageCount > 0 then
Session("Manager") = true
end if
else
Response.Redirect("login.asp")
end if
set login = nothing
Session("page") = "home.asp"
Response.Redirect ("MainPage.asp")
else
Response.Redirect ("login.asp")
end if
%>
During step 4, I usually run the SQL Trace utility to make sure I'm passing the correct arguments. If the method is transactional, I watch the transaction statistics window. Although Login is not a transaction method, I would still expect to see the ExpenseReadDB component object count go to one and quickly drop back to zero, as shown in Figure 8.
Figure 8. Activation during login
The major ideas presented in this article include:
Scott Stanfield is the president of Vertigo Software, Inc. Vertigo Software is a San Francisco Bay Area-based consulting firm that specializes in the design and development of Windows DNA applications and components. He can be reached at scott@vertigosoftware.com or on the Web at http://www.vertigosoftware.com/.