Data Access Layer

The most common question I get asked by Visual Basic developers is, "What type of DAL should I use? Data Access Objects (DAO), Open Database Connectivity (ODBC) API, ODBCDirect, Remote Data Objects (RDO), ActiveX Data Objects (ADO), VBSQL, vendor-specific library, smoke signals, semaphores, Morse code, underwater subsonic communications?" The answer is never easy. (Although in windier climates I would advise against smoke signals.)

What I do think is a good approach to data access is to create a simple abstract data interface for your applications. This way, you can change your data access method without affecting the other components in your application. Also, a simple data interface means that developers have a much lower learning curve and can become productive more quickly.

Remember that we are trying to create a design pattern for business object development in corporate database-centric applications. These applications typically either send or receive data or they request a particular action to be performed; as such, a simple, straightforward approach is required. I like simplicity—it leads to high-quality code.

So am I saying don't use ADO? No, not at all. Most Microsoft-based solutions are well suited to using ADO inside the DAL. But by concentrating your ADO code (or any database connection library for that matter) into one component, you achieve a more maintainable, cohesive approach. Besides, ADO is still a complex beast. If you take into account that many of ADO's features are restricted by the type of database source (such as handling text fields and locking methodologies), you'll find that the benefits of a stable, easily understood DAL far outweigh the cost of slightly reinventing the wheel.

Not only is abstracting the data access methodology important, but abstracting the input and output from this component are important as well. The DAL should return the same data construct regardless of the database source.

But enough theoretical waffling—let's look at something real.

Data Access Layer Specifics

The DAL presented here is essentially the same DAL that TMS uses in enterprise applications for its clients. I'll describe the operation of the DAL in detail here, but I'll concentrate only on using a DAL, not on building one. Remember that we want to concentrate on business object development, so this is the data object that our business objects will interface with.

The DAL below consists of only six methods. All other aspects of database operation (for instance, cursor types, locking models, parameter information, and type translation) are encapsulated and managed by the DAL itself.

cDAL Interface

Member	Description
OpenRecordset	Returns a recordset of data
UpdateRecordset	Updates a recordset of data
ExecuteCommand	Executes a command that doesn't involve a recordset
BeginTransaction	Starts a transaction
CommitTransaction	Commits a transaction
RollbackTransaction	Rolls back a transaction

What's all this recordset malarkey? Well, this is (surprise, surprise) an abstracted custom recordset (cRecordset). We must investigate this recordset carefully before we look at the operation of the DAL itself.

Recordset

The method TMS uses for sending and receiving data to and from the database is a custom recordset object (cRecordset). In this chapter, whenever I mention recordset, I am referring to this custom cRecordset implementation, rather than to the various DAO/RDO/ADO incarnations.

A recordset is returned from the DAL by calling the OpenRecordset method. This recordset is completely disconnected from the data source. Updating a recordset will have no effect on the central data source until you call the UpdateRecordset method on the DAL.

We could have easily returned an array of data from our DAL, especially since ADO and RDO nicely support GetRows, which does exactly that, but arrays are limited in a number of ways.

Array manipulation is horrible. In Visual Basic, you can resize only the last dimension of an array, so forget about adding columns easily. Also, arrays are not self-documenting. Retrieving information from an array means relying on such hideous devices as constants for field names and the associated constant maintenance, or the low-performance method of looping through indexes looking for fields.

Enabling varying rows and columns involves using a data structure known as a ragged array—essentially an array of arrays—which can be cumbersome and counterintuitive to develop against.

The advantage of using a custom recordset object is that we can present the data in a way that is familiar to most programmers, but we also get full control of what is happening inside the recordset. We can again simplify and customize its operation to support the rest of our components.

Notice the Serialize method, which allows us to move these objects easily across machine boundaries. More on this powerful method later. For the moment, let's look at the typical interface of a cRecordset.

cRecordset Interface

Member	Description
MoveFirst	Moves to first record
MoveNext	Moves to next record
MovePrevious	Moves to previous record
MoveLast	Moves to last record
Name	Shows the name of the recordset
Fields	Returns a Field object
Synchronize	Refreshes the contents of the recordset
RowStatus	Shows whether this row has been created, updated, or deleted
RowCount	Shows the number of records in the recordset
AbsolutePosition	Shows the current position in the recordset
Edit	Copies the current row to a buffer for modification
AddNew	Creates an empty row in a buffer for modification
Update	Commits the modification in the buffer to the recordset
Serialize	Converts or sets the contents of the recordset to an array

This table shows the details of the interface for the cField object, which is returned from the cRecordset object.

cField Interface

Member	Description
Name	Name of the field
Value	Contents of the field
Type	Visual Basic data type
Length	Length of the field

Retrieving a Recordset

So how can we utilize this cRecordset with our DAL? Here's an example of retrieving a recordset from the DAL and displaying information:

Dim oDAL     As New cDAL Dim oRec     As New cRecordset Set oRec = oDAL.OpenRecordset("Employees") oRec.MoveFirst MsgBox oRec.Fields("FirstName").Value 

Please forgive me for being a bit naughty in using automatically instantiated object variables, but this has been done purely for code readability.

Notice that the details involved in setting up the data connection and finding the data source are all abstracted by the DAL. Internally, the DAL is determining where the Employees data lives and is retrieving the data and creating a custom recordset. In a single-system DAL, the location of the data could be assumed to be in a stored procedure on a Microsoft SQL Server; in a multidata source system, the DAL might be keying into a local database to determine the location and type of the Employees data source. The implementation is dependent upon the requirements of the particular environment.

Also, the operation in the DAL is stateless. After retrieving the recordset, the DAL can be closed down and the recordset can survive independently. This operation is critical when considering moving these components to a hosted environment, such as Microsoft Transaction Server (MTS).

Statelessness is important because it determines whether components will scale. The term scale means that the performance of this object will not degrade when usage of the object is increased. An example of scaling might be moving from two or three users of this object to two or three hundred. A stateless object essentially contains no module-level variables. Each method call is independent and does not rely on any previous operation, such as setting properties or other method calls. Because the object has no internal state to maintain, the same copy of the object can be reused for many clients. There is no need for each client to have a unique instance of the object, which also allows products such as Microsoft Transaction Server to provide high-performance caching of these stateless components.

Serializing a Recordset

Two of the primary reasons for employing a custom recordset are serialization and software locking. Because passing objects across machines causes a considerable performance penalty, we need a way of efficiently moving a recordset from one physical tier to another.

Serialization allows you to export the contents of your objects (such as the variables) as a primitive data type. What can you do with this primitive data type? Well, you can use it to re-create that object in another environment—maybe in another process, maybe in another machine. All you need is the class for the object you have serialized to support the repopulation of its internal variables from this primitive data type. The process of serialization has tremendous performance advantages in that we can completely transfer an object to another machine and then utilize the object natively in that environment without incurring the tremendous performance cost that is inherent in accessing objects across machine boundaries.

The cRecordset object stores its data and state internally in four arrays. The Serialize property supports exposing these arrays to and receiving them from the outside world, so transferring a recordset from one physical tier to another is simply a matter of using the Serialize property on the cRecordset. Here's an example:

Dim oRec     As New cRecordset Dim oDAL     As New cDAL  ' Assume this DAL is on another machine. oRec.Serialize = oDAL.OpenRecordset("Employees").Serialize Set oDAL = Nothing MsgBox oRec.RecordCount 

Now we have a recordset that can live independently. It can even be passed to another machine and then updated by a DAL on that machine, if required.

Locking a Recordset

We need to keep track of whether the data on the central data source has changed since we made our copy. This is the job of one of the arrays inside the cRecordset, known affectionately as the CRUD array.

The CRUD array indicates whether this row has been Created, Updated, Deleted, or just plain old Read. Also stored is a globally unique identifier (GUID) for this particular row. This unique identifier must be automatically updated when a row is modified on the data source. These two parameters are used by the DAL in the UpdateRecordset method to determine whether a row needs updating and whether this row has been modified by someone since the client received the recordset. This process is a form of software locking, although it could be internally implemented just as easily using timestamps (if a given data source supports them).

Updating a Recordset

Updating a cRecordset object through the DAL occurs by way of the UpdateRecordset method. UpdateRecordset will scan through the internal arrays in the recordset and perform the required database operation. The unique row identifier is used to retrieve each row for modification, so if someone has updated a row while the recordset has been in operation this row will not be found and an error will be raised to alert the developer to this fact. The following is an example of updating a row in a recordset and persisting that change to the data source:

Dim oRec     As New cRecordset Dim oDAL     As New cDAL  ' Assume local DAL so don't need serialization Set oRec = oDAL.OpenRecordset("Employees") oRec.Edit oRec.Fields("FirstName") = "Adam Magee" oRec.Update oDAL.UpdateRecordset oRec 

Synchronizing a Recordset

After a cRecordset object has been used by UpdateRecordset to successfully update the data source, the cRecordset object needs to have the same changes committed to itself, which is accomplished by means of the Synchronize method.

Using the Synchronize method will remove all Deleted rows from the recordset and will set any Updated or Created rows to Read status. This gives the developer using the cRecordset object control over committing changes and also means the recordset state can be maintained in the event of an UpdateRecordset failure. Here is an example of synchronizing a recordset after a row has been updated:

oDAL.UpdateRecordset oRec oRec.Synchronize Parameters 

Simply supplying the name of a cRecordset object to OpenRecordset is usually not enough information, except maybe when retrieving entire sets of data, so we need a way of supplying parameters to a DAL cRecordset operation.

This is achieved by using a cParams object. The cParams object is simply a collection of cParam objects, which have a name and a value. The cParams object, like the cRecordset object, can also be serialized. This is useful if the parameters need to be maintained on another machine.

CParams Interface

Member	Description
Add	Adds a new cParam object with a name and a value
Item	Returns a cParam object
Count	Returns the count of collected cParam objects
Remove	Removes a cParam object
Serialize	Converts or sets the content of cParams object into an array

CParam Interface

Member	Description
Name	Name of the parameter
Value	Variant value of the parameter

Here is an example of retrieving a recordset with parameters:

Dim oDAL As New cDAL Dim oRec As New cRecordset Dim oPar As New cParams oPar.AddField "EmpID", "673" oPar.AddField "CurrentlyEmployed", "Yes" Set oRec = oDAL.OpenRecordset("Employees", oPar) MsgBox oRec.RecordCount 

We can see now that only two well-defined objects are parameters to the DAL—cRecordset and cParams—and both of these objects support serialization, giving a consistent, distributed operation-aware interface.

Commands

A lot of database operations do not involve, or should not involve, cRecordset objects. For instance, checking and changing a password are two operations that do not require the overhead of instantiating and maintaining a cRecordset. This is where you use the ExecuteCommand method of the DAL. The ExecuteCommand method takes as parameters both the name of the command to perform and a cParams object.

Any output cParam objects generated by the command are automatically populated into the cParams object if they are not supplied by default. Here's an example of checking a password:

Dim oDAL As New cDAL Dim oPar As New cParams oPar.Add "UserID", "637" oPar.Add "Password", "Chebs" oPar.Add "PasswordValid", ""     ' The DAL would have added this output                                  ' parameter if we had left it out. oDAL.ExecuteCommand "CheckPassword", oPar If oPar("PasswordValid").Value = "False" Then     Msgbox "Sorry Invalid Password", vbExclamation End If 

Transactions

Most corporate data sources support transactions; our DAL must enable this functionality as well. This is relatively easy if you are using data libraries such as DAO or RDO, since it is a trivial task to simply map these transactions onto the underlying calls. If your data source does not support transactions, you might have to implement this functionality yourself. If so, may the force be with you. The three transaction commands are BeginTransaction, CommitTransaction, and RollbackTransaction.

The DAL is taking care of all the specifics for our transaction, leaving us to concentrate on the manipulation code. In the case of a transaction that must occur across business objects, we'll see later how these business objects will all support the same DAL. Here's an example of updating a field inside a transaction:

Dim oRec As New cRecordset Dim oDAL As New cDAL  ' Assume local DAL so don't need serialization With oDAL     .BeginTransaction     Set oRec = oDAL.OpenRecordset("Employees")     With oRec         .Edit         .Fields("FirstName") = "Steve Gray"         .Update     End With     .UpdateRecordset oRec     .CommitTransaction End With Wrap Up 

So that's a look at how our abstracted data interface works. I hope you can see how the combination of cDAL, cRecordset, and cParams presents a consistent logical interface to any particular type of data source. There is comprehensive support for distributed operation in the sense that the DAL is completely stateless and that the input and output objects (cParams and cRecordset) both support serialization.