Scott Stanfield
Vertigo Software, Inc.
July 1999
Summary: Describes the design and implementation of the FMStocks Data Access Layer (DAL). (16 printed pages)
FMStocks funnels all database access through a single Microsoft® Visual Basic® component called FMStocks_DB.dll. This Component Object Model (COM) component offers several benefits:
Our best performance gains came from a finely tuned Data Access Layer. What you’ll find here is a list of best practices that you can apply to your own designs.
We chose Visual Basic as our implementation language over Microsoft Visual C++® for these reasons:
The source for our DAL is contained in a single Visual Basic ActiveX or COM project called FMStocks_DB. Its class names mirror database tables; its methods mirror stored procedures. There are two reusable Basic modules that make the coding much easier to read. Figure 1 below shows the three modules and seven COM classes.
Figure 1. FMStocks_DB project
| Filename | Purpose |
| Database.bas | Reusable functions to simplify the handling of stored procedures through ADO. It takes advantage of disconnected recordsets to minimize resource usage and enhance scalability. |
| Context.bas | Contains three helper functions that wrap the major MTS methods: SetComplete, SetAbort, and CreateInstance. Useful if MTS is not installed or available. Contains helper functions to simplify error handling. |
| Account.cls | Encapsulates three Account_* stored procedures: Validate a logon, add a new account, and show an account summary. |
| Broker.cls | Two functions that call Broker_Buy and Broker_Sell, the stored procedures that actually execute orders. |
| Position.cls | Manages access to the Position table through three stored procs. |
| Ticker.cls | Handles current price retrieval, and list by company name or ticker symbol. Another function returns the fundamental data. |
| Tx.cls | Two functions queue buy and sell requests to the Transaction table. GetByID returns transaction details for a given TransactionID. |
| TxNew.cls | Isolates the SetTxType function in its own MTS mode that requires a new transaction. Used in a specific way by the ExecuteTransaction method in the business component. |
| Version.cls | Three debugging functions return the connection string, file version, and computer name. |
The division of stored procedures into these classes was fairly arbitrary. They could be lumped into a single class, but we divided them along semantic groups that mapped to how they are used.
Of the seven COM classes, we’re only going to discuss the Account.cls file. The other six are similar—they all wrap SQL stored procedures the same way.
Once you understand a few key points, writing your own DAL to insulate the database will give you better performance and reusability.
Let’s dive in and take a look at a piece of the code. Understanding how one method works will unlock the design pattern used throughout the code.
The Account.Add method wraps access to the Account_Add stored procedure. Before we examine the code, let’s see how it might be used directly by a snippet of Visual Basic, Scripting Edition (VBScript) code:
' Put this code in a file called AddAccount.vbs. Double-click to test.
' All prudent error handling code was removed for your safety.
dim obj
set obj = WScript.CreateObject("FMStocks_DB.Account")
dim accountid
accountid = obj.Add("Mike", "Hanley", "password", _
"mike@vertigosoftware.com")
set obj = nothing
MsgBox ("A new account was added with the ID of " & accountid)
Assuming the FMStocks_DB component is correctly installed, the first line creates an instance of the Account class. The Add method takes the first and last name, password and e-mail address and returns the new account number. This is not rocket science—you’d expect a high-level method like this to handle the database details.
And it does:
Memorize this code pattern. It is used in almost every method in FMStocks_DB.
| 1. | The function signature takes the five parameters necessary to add a new account. It returns the new account ID. If this function fails, it raises an error, rather than returning a 0 or –1 as the return value. |
| 2. | We always set up an error handler. This is Visual Basic’s version of exception handling—if any problems occur during the execution of the stored procedure, control will pass to line 6. |
| 3. | We’ll get to RunSPReturnInteger later. For now, know that it will run a store procedure and return the @retval output parameter as a variant. It needs the name of the stored procedure and zero or more parameter arrays. |
| 4. | Tell MTS that it’s ok to lobotomize us when we exit the function. This is the essence of stateless programming. CtxSetComplete is a wrapper that defaults to nothing if the object is not running under MTS. |
| 5. | If you forget this line (like I have many times), you’ll fall through to the error handling code. |
| 6. | CtxRaiseError is a helper function that uses the MTS SetAbort method to notify the world that something bad has happened. It also uses the Visual Basic Err.Raise method for signaling the error. We’re handing off the module name and function name to build the error string for easier debugging. |
Now, repeat this code for every stored procedure and you’re done with your Data Access Component!
For a clearer understanding of the code above and how the data access component fits into the big picture, it might be good to see an entire feature, end-to-end. We’ll take a closer look at the Web page that looks up all the ticker symbols for a given company. This Web page uses a business component that passes through the request to our DAL, which uses a stored procedure to do the search.
The code below is a simplified version of the full TickerList.asp page. This ASP snippet uses a form to get the company name from the user. It returns a list of matching companies and their respective ticker symbols.
Figure 2. Simplified TickerList.asp code
If you’re a 2-tier ASP programmer, you would probably build a SQL query right in the page and use ADODB.Recordset to run it. You’d still end up with a recordset in the end. The difference is that our model abstracts the database from the client into a true 3-tier model.
Figure 3 shows the page in action.
Figure 3. Simple ticker search page
The full business logic component, FMStocks_Bus, is discussed in detail in "Fitch & Mather Stocks: Business Logic Layer." The code for ListByCompany simply calls into the DAL to do the dirty work:
' ListByCompany
'
Public Function ListByCompany(ByVal company As String) As ADODB.Recordset
On Error GoTo errorHandler
Dim obj As FMStocks_DB.Ticker
Set obj = CtxCreateObject("FMStocks_DB.Ticker")
Set ListByCompany = obj.ListByCompany(company)
Set obj = Nothing
CtxSetComplete
Exit Function
errorHandler:
Set obj = Nothing
CtxRaiseError g_modName, "ListByCompany"
End Function
The design pattern for the business tier is similar to that used by the DAL code. However, the business tier doesn’t have any code for accessing a database. It’s merely a consumer of recordsets. Most of the time, the business tier just passes the recordsets back to the next client in line—in our case, ASP. However, the middle-tier code could be used by others, like a stand-alone Visual Basic or MFC program.
Now we’re back at the code covered by this document. The implementation for ListByTicker looks similar to that of Account.Add. The Ticker_ListByCompany stored procedure returns a recordset, whereas Account.Add returned a simple long value for the new AccountID.
Public Function ListByCompany(ByVal Company As String) As ADODB.Recordset
On Error GoTo errorHandler
Set ListByCompany = RunSPReturnRS("Ticker_ListByCompany", _
Array("@Company", adVarChar, 60, Company))
CtxSetComplete
Exit Function
errorHandler:
CtxRaiseError g_modName, "ListByCompany"
End Function
You might recall this from the "Fitch & Mather Stocks: Database," the source code for Ticker_ListByTicker. It simply returns a list of ticker symbols and companies that start with a given string.
CREATE PROCEDURE Ticker_ListByTicker
(
@Ticker varchar(12)
)
AS
select ticker, company, exchange
from stocks
where ticker like @Ticker + '%'
The records returned by the select statement above are handled by ADO and turned into a Recordset object. This object is the return value from RunSPReturnRS. Maybe a more descriptive name for this method could have been RunStoredProcedureAndReturnTheResultsAsARecordSet, but I’d quickly wear out my keyboard.
The really interesting code in the DAL lies behind the RunSP* functions defined in Database.cls. If my computer crashed, the only file I would recover is Database.cls: it represents the most tuned piece of code in this application, next to the database indices.
All ADO and database access code is funneled through this one file. Having a single point of access in an application makes it easier to tune and track down database bugs.
RunSPReturnRS lives in Database.cls, along with RunSP, RunSPReturnInteger, and RunSPReturnRS_RW. These four functions handle almost all of the different ways you would want to execute a stored procedure.
The RunSPReturnRS function is used the most and therefore deserves the most treatment. It executes a stored procedure, handles the parameter passing and returns the results as a read-only, disconnected recordset:
The major sections of code above are explained in the table below:
| 1. | All RunSP* functions have the same parameter signature. The first is the name of the stored procedure to execute. The second is a variable-length list of arguments for the stored procedure.
RunSPReturnRS returns a disconnected ADO Recordset. Table 1 shows the return values for all RunSP* functions. |
| 2. | Sets up the error-handling block at the bottom of the function. If, for example, the database is down or a trigger rolls back a stored procedure, then the error-handling block at the bottom will be executed. |
| 3. | Creates the two ADO objects used by this function, Recordset and Command. The Command object is necessary because it’s the only way to pull out values returned by a stored procedure’s output variables. |
| 4. | Hooks up the Command object to the database through the connection string. We also tell the database that we’ll be running a stored procedure whose name is stored in the parameter strSP. |
| 5. | CollectParams is a great helper function that parses the params object and builds up the Command object’s Parameter list. Since most stored procedures take a few arguments, building them using the Parameter.Add method can be quite tedious. The ParamArray makes the work much easier. It is discussed in more detail below. |
| 6. | This block of code is key to making this method as fast and scalable as possible.
The first line configures the recordset to use client-side cursors, a requirement for disconnected recordset use. The second line executes the stored procedure using the least-expensive methods, adOpenForwardOnly and adLockReadOnly. They offer the best performance for doing forward-only scans through records. Since we don’t need cursor scrolling and updatability, we’re not asking for it unnecessarily. The other RunSP* methods differ on the rs.Open line. RunSPReturnRS_RW returns a read/write recordset (hence the _RW suffix). It uses adOpenDynamic to create an updateable recordset. RunSP, however, doesn’t use a recordset at all—it uses the ADODB.adExecuteNoRecords parameter to Command.Execute to eliminate the overhead associated with creating an empty recordset. The next three lines free up our database connection from the pool, allowing it to be used by other threads. |
| 7. | RunSPReturnRS returns the new recordset on this line. |
| 8. | Error handling is made simple by calling a helper method RaiseError. It has the same parameter signature as CtxRaiseError, except it does not call the MTS function SetAbort. |
In addition to RunSPReturnRS, the other stored procedure helper functions and their return values are listed in Table 1. As you can see, RunSPReturnInteger and RunSPReturnRS are the most frequently used functions.
Table 1. RunSP* function return values
| Function | Return value | Times used |
| RunSPReturnInteger | A number cast to a variant: Assumes the stored procedure’s last parameter is a number output parameter called @retval. | 7 |
| RunSPReturnRS | A disconnected, read-only, ADO recordset. | 7 |
| RunSPReturnRS_RW | A connected, updateable ADO recordset. (RW means read/write.) | 1 |
| RunSP | Nothing. Useful for delete or update oriented stored procs. | 1 |
RunSP is used in cases where I don’t care about getting a return value. It’s invoked to call Tx_SetTxType, a stored procedure that changes the state of a record in the Transaction table. RunSP uses the ADO Command object to execute the stored procedure. It also uses a control flag that instructs ADO not to create and return a Recordset, which is handy because it would be empty! The ADODB.adExecuteNoRecords option allows ADO to avoid the unnecessary overhead associated with an empty Recordset. The code is shown below:
Function RunSP(ByVal strSP As String, ParamArray params() As Variant)
On Error GoTo errorHandler
' Create the ADO objects
Dim cmd As ADODB.Command
Set cmd = CtxCreateObject("ADODB.Command")
' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = GetConnectionString()
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
collectParams cmd, params
' Execute the query without returning a recordset
cmd.Execute , , ADODB.adExecuteNoRecords
' Clean up and return nothing
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Exit Function
errorHandler:
Set cmd = Nothing
RaiseError g_modName, "RunSP(" & strSP & ", ...)"
End Function
RunSPReturnInteger works with stored procs that return numbers. Our intent was to make a more generic function, but all of our stored procedures returned integers anyway.
Function RunSPReturnInteger(ByVal strSP As String, ParamArray params() _
As Variant) As Variant
On Error GoTo errorHandler
' Create the ADO objects
Dim cmd As ADODB.Command
Set cmd = CtxCreateObject("ADODB.Command")
' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = GetConnectionString()
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
collectParams cmd, params
' Assume the last parameter is outgoing and named @retval (specific
' for FMStocks)
cmd.Parameters.Append cmd.CreateParameter("@retval", adInteger, _
adParamOutput, 4)
' Execute without a resulting recordset and pull out the "return
' value" parameter
cmd.Execute , , ADODB.adExecuteNoRecords
RunSPReturnInteger = cmd.Parameters("@retval").Value
' Clean up and return
Set cmd.ActiveConnection = Nothing
Set cmd = Nothing
Exit Function
errorHandler:
Set cmd = Nothing
RaiseError g_modName, "RunSPReturnInteger(" & strSP & ", ...)"
End FunctionRunSPReturnRS_RW returns an updateable, connected recordset. The stock sell code in the BLL walks the recordset to delete or update individual portfolio positions for a given stock. RunSPReturnRS_RW differs from the read-only, disconnected functions in a few ways:
rs.CursorLocation = adUseClient
rs.Open cmd, , adOpenDynamic, adLockBatchOptimistic
Set cmd = Nothing
Although it sets the cursor location to the client (meaning don’t keep the cursor open on the database), we’re going to keep the recordset connected to the database. The only way to know that it is still connected is that the function does not explicitly set the cmd.ActiveConnection object to nothing.
The Open parameters, adOpenDynamic and adLockBatchOptimistic, allow us to modify the contents of the recordset, and then submit all changes back to the database in one batch.
This function could have been written to disconnect itself from the database. That would require the client to reconnect to the database before issuing an UpdateBatch.
The source code for RunSPReturnRS_RW is here:
Function RunSPReturnRS_RW(ByVal strSP As String, ParamArray params() As Variant) _
As ADODB.Recordset
On Error GoTo errorHandler
' Create the ADO objects
Dim rs As ADODB.Recordset, cmd As ADODB.Command
Set rs = CtxCreateObject("ADODB.Recordset")
Set cmd = CtxCreateObject("ADODB.Command")
' Init the ADO objects & the stored proc parameters
cmd.ActiveConnection = GetConnectionString()
cmd.CommandText = strSP
cmd.CommandType = adCmdStoredProc
collectParams cmd, params
rs.CursorLocation = adUseClient
' Execute the query as an updatable recordset and stay connected
rs.Open cmd, , adOpenDynamic, adLockBatchOptimistic
Set cmd = Nothing
' Return and quit
Set RunSPReturnRS_RW = rs
Exit Function
errorHandler:
Set rs = Nothing
Set cmd = Nothing
RaiseError g_modName, "RunSPReturnRS_RW(" & strSP & ", ...)"
End Function
CollectParams is a helper function that takes some of the tedium out of parameter passing in ADO. The best way to illustrate the value of this function is to show you an example of how it is used. I’ll use the Broker_Buy stored procedure as an example:
x = RunSPReturnInteger("Broker_Buy", _
Array("@TxID", adInteger, 4, TxID), _
Array("@AccountID", adInteger, 4, AccountID), _
Array("@Ticker", adChar, 12, Ticker), _
Array("@Shares", adInteger, 4, Shares), _
Array("@Price", adCurrency, 8, Price), _
Array("@Commission", adCurrency, 8, Commission))
All RunSP* functions expect the first parameter to be the name of the stored procedure. Anything after that is assumed to be an Array with four members: the name of the stored procedure variable (they begin with “@” in SQL Server), the ADO matching data type, the length in bytes and the default value.
The CollectParams helper function unpacks the parameter array and sets up the command object’s Parameter list.
The full source for CollectParams is shown below
Sub collectParams(ByRef cmd As ADODB.Command, ParamArray argparams() _
As Variant)
Dim params As Variant
params = argparams(0)
Dim i As Integer, v As Variant
For i = LBound(params) To UBound(params)
If UBound(params(i)) = 3 Then
' Check for nulls.
If TypeName(params(i)(3)) = "String" Then
v = IIf(params(i)(3) = "", Null, params(i)(3))
ElseIf IsNumeric(params(i)(3)) Then
v = IIf(params(i)(3) < 0, Null, params(i)(3))
Else
v = params(i)(3)
End If
cmd.Parameters.Append cmd.CreateParameter(params(i)(0), _
params(i)(1), adParamInput, params(i)(2), v)
Else
cmd.Parameters.Append cmd.CreateParameter(params(i)(0), _
params(i)(1), adParamInput, params(i)(2), params(i)(3)) End If
Next i
End Sub
As you might have noticed, each RunSP* function makes a call to GetConnectionString to setup the ADO Command object’s connection string. This simple function is capable of generating quite a bit of controversy:
Function GetConnectionString() As String
' Change the "(local)" part below to your database machine name
' If this component is deployed on your Web server, you don't
' need to change it.
GetConnectionString = "Provider=SQLOLEDB;Data Source=(local);" & _
"User Id=stocks_login;Password=password;"
Exit Function
End Function
There are numerous ways to handle connection strings, and I’ve probably tried them all. We’ve stored the connection string in the registry, as a DSN, read it from a file, passed it in as the first parameter, and so on. However, when it comes to performance, nothing beats a single line of code.
Hard-coding the DSN does present a deployment problem. If you’ve tried to install FMStocks on your own system, and you read the Setup documentation carefully, you may have changed your server name and recompiled. In the grand scheme of things, deployment issues were of lower priority than performance for FMStocks. Your mileage may vary.
We went with an OLEDB provider instead of the SQL ODBC driver. You can learn how to construct the OLEDB connection string by searching MSDN for the Knowledge Base article Q193135. I found it by searching for SQLOLEDB. Visual InterDev® 6.0 Service Pack 3 will build native OLEDB connection strings.
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/.