Breaking Out the Data Access Layer

Duwamish Books, Phase 2

Dale Smith
Microsoft Developer Network

July 1998

Summary: Explains the process of removing data access code from an application and replacing it with calls to a data access COM component. (5 printed pages) Discusses:

Introduction

Phase 2 of the Duwamish Books sample is designed to demonstrate how to break out the data access layer from the individual applications into a component that will be common to all of the applications. The approach that we took to remove data access from the applications is similar to the approach that would be taken when developing a new application—gathering information, analyzing, planning, and then implementing. This process became a cooperative effort between the application developers and the developer responsible for designing the data access component. See Robert Coleridge's articles "Designing a Data Access Layer," for an examination of the issues involved in separating the data access component, and "Duwamish Books Data Access Layer API," for detailed specifications of the component's methods and properties. This article describes the process of replacing the integrated data access layer in each desktop application with calls to methods in a separate COM component—the Duwamish Books Data Access Layer (DBDAL).

The first step in the process was to assess the data access requirements in each of the applications and isolate the data access code segments. The code segments representing the basic functions associated with data access—getting, adding, and editing records—were then grouped together.

The fact that each developer in our group took a different approach to the task of data access required us to analyze the techniques used. For that reason, after all data access code segments were isolated, we examined each of the basic functions to find their basic similarities, allowing us to determine which functions could be shared and used in the data access component. Because all of the needs of each application could not be accommodated exactly, some additional modifications to each application's code were required.

POS Application: Inserting a Sale

In the Phase 1 Point of Sale (POS) application, inserting a sale required the grouping of several data changes as a single transaction because of the parent/child relationship between sale and detail records. This required special data access code. The code that was developed in Phase 1 helped to determine some of the methods that are used in the Phase 2 component. The following code illustrates the insertion of a sale and its details. Notice, in particular, the oSale.PKId = oRset!PKId statement. This retrieves the primary key (PKId) of the recently inserted sale record. This PKId is then used as the foreign key for the SaleDetail records that are subsequently added:

Public Function InsertSale(oSale As cSale, oSaleDetItems As cSaleDetItems, oAddress As cAddress, oOrderStatus As cOrderStatus, oOrderType As cOrderType) As Boolean
Dim oDAL As New dbdal.CDataAccess
Dim oRset As ADODB.Recordset
.
.
.
' Insert sale.
Set oRset = New ADODB.Recordset
If oDAL.OpenTRecordset(scSALES, oRset, LT_Optimistic, OO_UseTable) Then
        oRset.AddNew
    With oSale
        oRset!CustomerId = .CustomerId
.
.
.
        oRset!Ship = .Ship
    End With
    oRset.Update
    ' Record PKId of sale record for use with sale details
    oSale.PKId = oRset!PKId
  
    If oDAL.CloseTRecordset(oRset) Then
        Set oRset = Nothing
    Else
        Err.Number = icRECORDSET_CLOSE_ERROR
        Err.Description = scRECORDSET_CLOSE_ERROR
        GoTo ErrorHandler
    End If
Else
    Err.Number = icRECORDSET_OPEN_ERROR
    Err.Description = scRECORDSET_OPEN_ERROR
    GoTo ErrorHandler
End If

' Insert sale details.
Set oRset = New ADODB.Recordset
If oDAL.OpenTRecordset(scSALE_DETAILS, oRset, LT_Optimistic, OO_UseTable) Then
    For Each oSaleDetItem In oSaleDetItems
        oRset.AddNew
        With oSaleDetItem
            oRset!SaleId = oSale.PKId
            oRset!ItemId = .ItemId
            oRset!UnitPrice = .UnitPrice
            oRset!Quantity = .Quantity
            oRset.Update
            .PKId = oRset!PKId
        End With
    Next
    If oDAL.CloseTRecordset(oRset) Then
        Set oRset = Nothing
    Else
        Err.Number = icRECORDSET_CLOSE_ERROR
        Err.Description = scRECORDSET_CLOSE_ERROR
        GoTo ErrorHandler
    End If
Else
    Err.Number = icRECORDSET_OPEN_ERROR
    Err.Description = scRECORDSET_OPEN_ERROR
    GoTo ErrorHandler
End If
.
.
.

' Commit all pieces of the transaction as one.
If oDAL.CommitTrans(TT_DropConnection) Then
    ' Everything worked so set success and exit
    InsertSale = True
Else
    Err.Number = icTRANSACTION_CLOSE_ERROR
    Err.Description = scTRANSACTION_CLOSE_ERROR
    GoTo ErrorHandler
End If
Set oDAL = Nothing
.
.
.
End Function

The Catalog Application

In the Phase 1 Catalog application, data access was accomplished through a custom function named GetRecordset. The GetRecordset function in the Catalog application, although defined differently, shared the same name as a method in the common data access application programming interface (API). Because the procedures were not compatible, we needed to remove the function contained in the application. This function served a dual purpose, providing either an updatable or a nonupdatable recordset.

The GetRecordset method in the Phase 2 component only returns a nonupdatable recordset. For those cases where an updatable recordset is required, the set of methods BeginTrans, OpenTRecordset, and CommitTrans are provided in the component. The following code illustrates this process:

Private Sub btnAddAuthor_Click()
.
.
.
oDAL.BeginTrans (sDSN)
oDAL.OpenTRecordset scTABLE_AUTHORS, oRec, adLockOptimistic, adCmdTable
' Create new author
oRec.AddNew
oRec!FirstName = strFirst
oRec!LastName = strLast
oRec.Update
mnAuthorID = oRec!PKId
oRec.Close
oDAL.CommitTrans
Set oRec = Nothing
.
.
.
End Sub

The following code illustrates the GetRecordset method in the component:

Private Sub DisplayTotalItems()
Dim oDatabaseAPI As New CDataAccess
Dim oRec As ADODB.Recordset

If oDatabaseAPI.GetRecordset(sDSN, "SELECT PKId From Items", oRec) Then
   mnTotalItems = oRec.RecordCount
   StatusBar.SimpleText = "0 of " & mnTotalItems & " items."
   oRec.Close
   Set oRec = Nothing
End If
End Sub

Shipping and Receiving

In Phase 1, the Shipping and Receiving application accessed data by calling functions in a data access API module that compiled with the application. This module became the basis for the component in Phase 2. Because of this, conversion was relatively easy. I simply included MSDN: Duwamish 2, VB Data Access Layer as a reference in my project. I then removed the data access API module from the list of files included in my project and replaced the calls previously made to functions in the module with calls to methods in the new component. The functions that comprised the module were included in DBDAL for Phase 2. The following code illustrates the call to ExecQuery in Phase 1 and then the same call in Phase 2. CDataAccess is the only class in DBDAL.

Phase 1:

bReturn = DataAccessAPI.ExecQuery(scCONNECT, sQry)

Phase 2:

Dim oDataAccessAPI As New CDataAccess
bReturn = oDataAccessAPI.ExecQuery(scCONNECT, sQry)

Conclusion

In Phase 1 we used Microsoft® ActiveX® Data Objects (ADO) to handle data access. This gave us a common technology to use for data handling throughout the life cycle of the Duwamish Books Sample. If you attempt to isolate a data access component and convert to ADO from a classic desktop application that uses Data Access Objects (DAO) or Remote Data Objects (RDO), with or without bound controls, you will be required to do more conversion. Although some of the syntax is similar among the technologies, some of the functionality present in DAO and RDO is not available in ADO. ADO does not support bound controls without the Remote Data Service (RDS) AdvancedDataControl. Besides, as you migrate away from desktop applications to multitier applications, bound controls become less usable, so it is a good idea to make the break at this time.

In Phase 1 of the Duwamish Books sample different developers designed each application with few guidelines. Therefore, different coding techniques were used in each of the various applications. In Phase 2 our task was to separate the data access layer from the applications and share a COM component. This required the combining and refining of the data access methods so they could be shared by all of the applications. As you have seen illustrated, each application required some modifications, as well.

This wraps up Phase 2 of our sample. In Phase 3 we will replace the business logic in each application with a COM component shared by all. As you may imagine, the process will be similar to what was done with the data access layer in Phase 2, but more extensive.