Duwamish Books Data Access Layer API

Robert Coleridge
Microsoft Developer Network

June 1998

Summary: Describes the methods and properties of the Duwamish Books Data Access Layer (DBDAL) COM component for Phase 2. (11 printed pages) For each method and property, provides:

Visual Basic and Visual C++ signatures
Parameters
Code examples

Duwamish Books Phase 2 will ship with both a Microsoft® Visual C++® and a Microsoft Visual Basic® version of this component, named dbvcdal.dll and dbvbdal.dll, respectively.

Introduction

The DBDAL COM component for Phase 2 contains one class: CDataAccess. This object's methods can be divided into three rough categories. The first manipulates the data source via disconnected client-side recordsets, the second manipulates the data source via connected server-side recordsets, and the third doesn't use recordsets.

The methods in Table 1 manipulate the database via either a SQL statement or a disconnected recordset. These methods are either stateless or state-aware, depending on whether a connection string is passed in to the method. (The one exception is the CacheConnection method, which either creates and caches the connection or closes the previously cached connection.)

If the connection string is supplied, it is used to create a temporary connection to the data source. This connections lasts for the lifetime of the single method invocation. Using this mode allows for the method to be completely stateless.

If a connection is not supplied, the method uses the previously created and cached connection, instead of creating a temporary one. This mode allows you to determine whether to cache a connection and thus speed up access but maintain state information.

All of the methods in Table 1 are used with disconnected, hence client-side, recordsets.

Table 1. Disconnected and/or State-aware Methods

Method	Description
CacheConnection	Either creates and caches a connection or closes previously cached connection.
ExecQuery	Executes a supplied SQL statement.
GetRecordset	Executes a supplied query and returns a disconnected client-side recordset.
PutRecordset	Reconnects a disconnected recordset and performs an update.

The methods in Table 2 perform transacted changes to a database and, therefore, use server-side recordsets. Due to this restriction, they maintain state information across calls.

Table 2. Connected or State-full Methods

Method	Description
BeginTrans	Begins a transaction on a connection. If a connection string is not supplied, uses a previously created connection.
CommitTrans	Commits a transaction on a connection. If a connection string is supplied, the previously created connection is closed after the commitment.
RollbackTrans	Rolls back a transaction on a connection. If a connection string is supplied, the previously created connection is closed after rollback.
OpenTRecordset	Creates a server-side connected recordset according to supplied SQL statement.
CloseTRecordset	Closes a previously opened server-side recordset.

Table 3 contains the remaining, miscellaneous methods and properties.

Table 3. Miscellaneous Methods and Properties

Method	Description
IsInTransaction	Returns true if in transaction, otherwise it returns false.
DataComponentsInstalled	Returns true if data access components are installed correctly, otherwise it returns false.

Methods

The following details the methods and parameters available in the CDataAccess class of the DBDAL component. (The following Visual C++ signatures assume the use of the #import "dbvcdal.dll" statement.)

CacheConnection

Both creates and caches a connection to a specified database or data source name (DSN) in order to speed up subsequent data access. If the database or DSN is omitted, empty, or NULL, CacheConnection will close a previously cached connection.

The method takes only one parameter and returns a Boolean flag indicating success or failure.

Visual Basic signature

Public Function CacheConnection (Optional ByVal Connect As String) As Boolean

Visual C++ signature

BOOL CacheConnection (BSTR Connect);

Parameter

Connect: Contains the connection string, database name, DSN, or other relevant identifiers of the connection. If this parameter is either left out or has a zero length, the cached connection is closed.

Example

   'Some higher scoped location.
   Dim oDBDal as new DBDAL.CDataAccess
   …
   'establish connection
   oDBDal.CacheConnection("Duwamish")

   'process data
   …

   'Break connection.
   oDBDal.CacheConnection()

Remarks

This method caches the connection in an internal connection object. The other methods use this cached object only if specified. This caching allows you to potentially have two connections available: the cached one and a temporary one. The cached connection is available for the duration of your application or until you close it. The temporary connection is created if you pass a database or DSN into another method. The temporary connection only lasts for the lifetime of the method itself.

ExecQuery

Submits any valid SQL statement for processing. The method returns a Boolean value indicating success (true) or failure (false).

Visual Basic signature

Public Function ExecQuery(ByVal Connect As String, _
                          ByVal SQLQuery As String) As Boolean

Visual C++ signature

BOOL ExecQuery(BSTR Connect, BSTR SQLQuery);

Parameters

Connect: Contains the connection string, database name, DSN, or other relevant identifiers of the connection.
SQLQuery: Any valid SQL statement to be processed.

Example

Dim oDAL As New dbdal.CDataAccess

'establish connection to data source
oDAL.CacheConnection "Duwamish"

'update inventory information
oDAL.ExecQuery SQLQuery:="Select Price from Stock Update Price = Price * 1.10"
oDAL.ExecQuery SQLQuery:="Select Level from Stock Update Level = 0"

'...etc

'close cached connection
oDAL.CacheConnection

GetRecordset

Returns a disconnected recordset as a result of an SQL query.

Visual Basic signature

Public Function GetRecordset( _
                      ByVal Connect As String, _
                      ByVal SQLQuery As String, _
                      ByRef Recordset As ADODB.Recordset) As Boolean

Visual C++ signature

BOOL GetRecordset(BSTR Connect, BSTR SQLQuery, IDispatch ** Recordset);

Parameters

Connect: Contains the connection string, database name, DSN, or other relevant identifiers of the connection.
SQLQuery: Any valid SQL statement that generates a recordset.
Recordset: An uninitialized Microsoft ActiveX® Data Objects (ADO) recordset that is initialized and filled in with the results from the query.

Example

Dim oDAL As New dbdal.CDataAccess
Dim oRS As ADODB.Recordset

'get disconnect Recordset
oDAL.GetRecordset "Duwamish", "Select * from Inventory", oRS

'lower prices by 10%
Do While Not oRS.EOF
    oRS.Fields("Price") = oRS.Fields("Price") * 0.9
Loop

'put new prices back
oDAL.PutRecordset "Duwamish", oRS

PutRecordset

Submits a disconnected recordset to a database for update.

Visual Basic signature

Public Function PutRecordset(ByVal Connect As String, _
                             Recordset As ADODB.Recordset) As Boolean

Visual C++ signature

BOOL PutRecordset(BSTR Connect, IDispatch ** Recordset);

Parameters

Connect: Contains the connection string, database name, DSN, or other relevant identifiers of the connection.
Recordset: A prepopulated ADO recordset that is sent back to the data source.

Example

Dim oDAL As New dbdal.CDataAccess
Dim oRS As ADODB.Recordset

'Get disconnect Recordset.
oDAL.GetRecordSet "Duwamish", "Select * from Inventory", oRS

'Lower prices by 10%.
Do While Not oRS.EOF
    oRS.Fields("Price") = oRS.Fields("Price") * 0.9
Loop

'Put new prices back.
oDAL.PutRecordset "Duwamish", oRS

Remarks

It is perfectly acceptable to use GetRecordset to retrieve a recordset from one connection and do a PutRecordset to send the recordset to another connection. However, the rules defining data integrity and compliance must be followed.

DataComponentsInstalled

Verifies whether the data access components are installed correctly. (At the time of writing, this method checks for the ADO components.)

Visual Basic signature

Public Function DataComponentsInstalled( _
                 Optional ByVal Connect As String) As Boolean

Visual C++ signature

BOOL DataComponentsInstalled(BSTR Connect);

Parameter

Connect: Contains the connection string, database name, DSN, or other relevant identifiers of the connection.

Example

Private Sub Form_Load ()
Dim oDAL As New dbdal.CDataAccess

If Not oDAL.DataComponentsInstalled() Then
    MsgBox "Install data access components before running this application"
    End
End If
…

Remarks

If Connect is supplied, a connection is attempted. If this parameter is not supplied, the function merely tries to instantiate the relevant objects from the data access components. Although supplying a connection string and attempting a physical connection is the most time-consuming, it is also the most effective way to determine if the data components actually work.

BeginTrans

Creates a new connection and begins transaction processing on that connection or begins transaction processing on a previously established connection.

Visual Basic signature

Public Function BeginTrans(Optional Connect As String) As Boolean

Visual C++ signature

BOOL BeginTrans(BSTR Connect);

Parameter

Connect: Contains the connection string, database name, DSN, or other relevant identifiers of the connection.

Example

See the appendix for example code.

Remarks

If the connection string is not supplied, the method starts another transaction on the previously established connection. If one is supplied, a connection is made to that DSN and the connection is cached for future use.

CommitTrans

Commits changes to a data source. Optionally, the connections can be closed after this operation.

Visual Basic signature

Public Function CommitTrans(Optional ByVal KeepOpen As TRANSTYPES = TT_DropConnection) As Boolean

Visual C++ signature

BOOL CommitTrans (TRANSTYPES KeepOpen);

Parameter

KeepOpen: Specifies the actions to be taken after the transaction is committed. The following are valid options:

TT_DropConnection: Closes connection after the transaction is committed.
TT_KeepConnection: Maintains connection after the transaction is committed.

Example

See the appendix for example code.

Remarks

If the string is not supplied, the method merely commits the existing transaction and does not shut down the previously established connection. If a string is supplied, the transaction is committed and the connection is closed.

RollbackTrans

Rolls back the changes to a data source. Optionally, the connections can be closed after this operation.

Visual Basic signature

Public Function RollbackTrans(Optional ByVal KeepOpen As TRANSTYPES = TT_DropConnection) As Boolean

Visual C++ signature

BOOL RollbackTrans (TRANSTYPES KeepOpen);

Parameter

KeepOpen: Specifies the actions to be taken after the transaction is committed. The following is a list of valid options:

TT_DropConnection: Closes connection after the transaction is committed.
TT_KeepConnection: Maintains connection after the transaction is committed.

Example

See the appendix for example code.

Remarks

If the string is not supplied, the method merely rolls back the existing transaction and does not shut down the previously established connection. If a string is supplied, the transaction is rolled back and the connection is closed.

OpenTRecordset

Obtains a connected server-side recordset from a data source.

Visual Basic signature

Public Function OpenTRecordset(ByVal SQLQuery As String, _
            ByRef Recordset As ADODB.Recordset, _
            Optional ByVal LockType As LOCKTYPES = LT_Optimistic, _
            Optional ByVal Options As OPENTRECORDSETOPTIONS = _
            OO_UseDefault) As Boolean

Visual C++ signature

BOOL OpenTRecordset(BSTR SQLQuery, IDispatch ** Recordset, 
                    LOCKTYPES LockType, OPENTRECORDSETOPTIONS Options);

Parameters

SQLQuery: Any valid SQL statement that generates a recordset, whether it's empty or populated.
Recordset: An uninitialized ADO recordset that is initialized and filled in with the results from the query.
LockType: An optional flag indicating the type of locking to be used against the recordset. By default, the function uses an optimistic locking scheme. The following is a list of valid options:
- LT_ReadOnly: For read-only locking.
- LT_Optimistic: For optimistic locking.
- LT_Pessimistic: For pessimistic locking.
- LT_BatchOptimistic: For optimistic locking on a batch of records.
Options: An optional flag passed on to the ADO Recordset.Open method. See the ADO documentation in the MSDN Library (Tools and Technologies, Microsoft Data Access Components) for more details on these flags. The following is a list of valid options:
- OO_UseTable: Use when the SQLQuery string is the name of a table.
- OO_UseText: Use when the SQLQuery string is a SQL statement.
- OO_UseStoredProc: Use when the SQLQuery string is the name of a stored procedure.
- OO_UseUnknown: Use when the SQLQuery string is of an unknown nature.
- OO_UseDefault: Use the ADO default, which at the time of writing is equivalent to OO_UseUnknown.

Example

See the appendix for example code.

CloseTRecordset

Closes a server-side recordset connection from a data source.

Visual Basic signature

Public Function CloseTRecordset(Recordset As ADODB.Recordset) As Boolean

Visual C++ signature

BOOL CloseTRecordset(IDispatch * Recordset);

Parameter

Recordset: A prepopulated ADO recordset that is sent back to the data source.

Example

See the appendix for example code.

Properties

The following property is available in the CDataAccess class of the DBDAL component.

IsInTransaction

Returns a TRUE after the BeginTrans method has been invoked, and returns FALSE after CommitTrans or RollbackTrans has been invoked.

Visual Basic signature

Public Property Get IsInTransaction() As Boolean

Visual C++ signature

BOOL IsInTransaction();

Appendix

Sample code for BeginTrans, CommitTrans, RollbackTrans, OpenTRecordset, and CloseTRecordset.

Private Sub Update_Sale(SH As cSalesDetail, SD() As cSaleDetail)
Dim oDAL As New dbdal.CDataAccess
Dim oRS As ADODB.Recordset
Dim lParentPKId As Long
Dim iSale As Integer
    
    'Begin transactional updates.
    oDAL.BeginTrans "Duwamish"
    
    'Add sales header (parent) and obtain primary key id.
    oDAL.OpenTRecordset "Sales", oRS, adLockOptimistic, adCmdTable
    oRS.AddNew
    oRS.Fields("Customer") = SH.Customer
    '...
    lParentPKId = oRS.Fields("PKId")
    oDAL.CloseTRecordset oRS

    'Create sales detail Recordset (child) and add batch.
    oDAL.OpenTRecordset "SalesDetail", oRS, adLockBatchOptimistic, adCmdTable
    For iSale = 0 To UBound(SD, 1)
        oRS.AddNew
        oRS.Fields("ParentFK") = lParentPKId
        oRS.Fields("ISBN") = SD(iSale).ISDN
        oRS.Fields("Qty") = SD(iSale).Qty
        '...
    Next iSale

    'Close the new Recordset and commit changes.
    If oDAL.CloseTRecordset(oRS) Then
        oDAL.CommitTrans "Duwamish"
    Else
        oDAL.RollbackTrans "Duwamish"
    End If
    
End Sub