ADO 2.0 Adds Flexibility, Performance
Tapping into the variety of data sources in your company might daunt even the best developers. ADO version 2.0 can help.by Dan Fox
Reprinted with permission from Visual Basic Programmer's Journal, 7/98, Volume 8, Issue 8, Copyright 1998, Fawcette Technical Publications, Palo Alto, CA, USA. To subscribe, call 1-800-848-5523, 650-833-7100, visit www.vbpj.com, or visit The Development Exchange at www.devx.com
For most corporate developers, the techniques we use to get and manipulate data form the core of the solutions we provide to our customers. For Visual Basic developers, this theme has been emphasized recently with the release of ActiveX Data Objects (ADO). This data-access interface has upped the ante and made the choices for data access more complex.
When released, ADO 2.0 will offer greater flexibility and performance with events, persisting of recordsets, and interfacing with SQL 7 and Oracle (see the sidebar, "What's New in ADO 2.0"). I'll explore the purpose and positioning of ADO, show you its architecture, and review a few techniques you might be using in Data Access Objects (DAO) and Remote Data Objects (RDO) and how they map to ADO.
| What's New in ADO 2.0 |
|---|
|
Here are the top three new features of ADO 2.0:
|
In today's corporate world, data doesn't reside exclusively in relational or mainframe databases. In a recent data modeling project, I found essential corporate information residing in Microsoft Access, Oracle, Btrieve, Excel workbooks, MSMail, Word documents, and in one case, even the Windows Cardfile. Your company likely faces a similar situation.
How does a corporate developer access this information so it can be used across the enterprise? One approach is to identify and extract this information from its current locations and import it into a Relational Database Management System (RDBMS), such as SQL Server or Oracle. This approach, called "Universal Data Storage," has been embraced by database vendors Sybase and Informix, among others. While this approach centralizes data, it does so at the expense of easily manipulating the data in its native format. Microsoft has used the Universal Data Access (UDA) approach, rather than centralized data storage.
UDA means accessing data where it lives. OLE DB, which is a set of Component Object Model (COM) interfaces that provides a standard way to access a data provider, lies at the center of this strategy. These interfaces are analogous to the standard Open Database Connectivity (ODBC) APIs used today. However, COM's location transparency-its ability to run in multiple tiers through Distributed Component Object Modeling (DCOM)-and its extensibility through the addition of interfaces make COM a perfect vehicle for representing and integrating data from a variety of formats.
Consequently, OLE DB, through data providers created by application vendors, can access data from such diverse sources as the upcoming NT5 directory services, Oracle, and AS/400. To see what's possible with OLE DB, you can access a live demo of ISG Navigator from International Software Group at http://www.isgsoft.com/navdemo. This product integrates OLE DB providers for multiple data sources into a complete solution that allows heterogeneous queries across those data sources. For more information on UDA, see http://www.microsoft.com/data.
OLE DB the easy way
UDA and OLE DB are great concepts, but how do VB or Visual Basic for Applications (VBA) developers tap into all that data? This is where ADO comes in. ADO is simply a set of wrappers that exposes the underlying OLE DB interfaces to VB through object models similar to those VB developers use today. ADO is strikingly simpler and more flexible than either RDO or DAO. The ADO 1.5 object model is composed of only seven objects and four collections, compared to the 10 objects and nine collections in RDO 2.0, and the 17 objects and 16 collections of DAO 3.5.
The ADO objects and collections are Connection, Command, Recordset, Parameter(s), Field(s), Error(s), and Property(ies) (see Figure 1). Note that this diagram is not strictly hierarchical, as are the object models for RDO and DAO. In ADO, you can create the Connection, Command, Parameter, and Recordset objects independently using either the New keyword or CreateObject statement. You can then freely associate these objects with each other at run time. This flexible model makes ADO great for applications that access multiple data sources or work with disconnected recordsets. While reading the example code below, you can look at the table to quickly map the primary ADO objects, properties, and methods to their counterparts in RDO and DAO (see Table 1).
| ADO | DAO with ODBCDirect | RDO |
| N/A | DBEngine | rdoEngine |
| N/A | Workspace | rdoEnvironment |
| Connection | Connection | rdoConnection |
| Open | Workspace.OpenConnection | EstablishConnection |
| Close | Close | Close |
| OpenSchema | N/A | N/A |
| Command | QueryDef | rdoQuery |
| Created with New | Connection.CreateQueryDef | rdoConnection.CreateQuery |
| Execute | Execute | Execute |
| CommandText | SQL | SQL |
| Recordset | Recordset | rdoResultset |
| Open | Connection.OpenRecordset | rdoConnection.OpenResultset |
| ActiveConnection | Connection | ActiveConnection |
| UpdateBatch | Update(adUpdateBatch) | BatchUpdate |
| NextRecordset | NextRecordset | MoreResults |
| GetRows | GetRows | GetRows |
| N/A | StillExecuting | StillExecuting |
| Parameter (Created with New and appended to the Command) | Parameter (Automatically added to the collection for the QueryDef) | rdoParameter (Automatically added to the collection for the rdoQuery) |
| Direction | Direction | Direction |
| Type | Type | Type |
| Value | Value | Value |
| Field | Field | rdoColumn |
| Value | Value | Value |
| UnderlyingValue | VisibleValue | BatchConflictValue |
| OrginalValue | OriginalValue | OrginalValue |
| Error | Error | rdoError |
| Description | Description | Description |
| Number | Number | Number |
| Source | Source | Source |
| Property | N/A | N/A |
| Table 1 Sizing Up ADO. This table shows the primary ADO 1.5 objects, methods, and properties and how they relate to RDO 2.0 and DAO 3.5. ADO's Property object and the OpenSchema method of the Connection object have no equivalents in RDO and DAO and aren't discussed in this article. | ||
For this article, I developed a small Selective Dissemination of Information (SDI) application in VB5 that you can use to compare common data-access techniques using the ADO 1.5 object model with RDO 2.0 and DAO 3.5 using ODBCDirect. The app allows you to choose a data-access method and click Connect. The code then instantiates an object from one of the application's three internal classes, each of which exposes eight methods (see Listing 1; download Listings 2 and 3 from the free, Registered Level of The Development Exchange). By selecting one of the techniques and clicking Go, the code calls a method in the object to exercise the technique (see Figure 2). The method calls take advantage of the polymorphism made available by the Implements keyword to minimize the amount of code required.
Listing 1
| VB5 |
|
| Listing 1 Implementing the clsData Blueprint. This listing shows the eight methods for the clsADO15 class module. It implements clsData, which acts as the blueprint for the three data-access classes. |
As the application manipulates or retrieves data, it reports results through message boxes, a text box, or a list-view control on the main form. A standard code module includes a global procedure used to load the list-view control from an array. All the techniques manipulate the sample pubs database in SQL Server 6.5 with the addition of three stored procedures and one table documented in the material that you can download from the free, Registered Level of The Development Exchange (see the Code Online box at the end of this article for details). With all the preliminaries out of the way, let's dig into the code.
GET CONNECTED
Establishing a connection with ADO is straightforward and similar to the method used in RDO. Each class in the application exposes Connect and Disconnect methods to set up and tear down the connection. In both ADO and RDO, you instantiate a Connection object, set a property to hold the connect string, and call a method to open the connection:
Set cnADO = New ADODB.Connection
strConnect = "driver={SQL Server};" & _
"uid=sa;server=kcweb;database=pubs"
cnADO.Provider = "MSDASQL"
cnADO.ConnectionString = strConnect
cnADO.ConnectionTimeout = 10
cnADO.CursorLocation = adUseNone
cnADO.Open
I'm using a "DSN-less" connection that fully specifies the driver, server, database, and login information. File DSNs are more flexible than traditional ODBC data sources because they don't require the client computer to persistently store the connection information in the registry. This new type of ODBC data source isn't a feature of ADO but is provided by ODBC 3.x for use with any of the three data-access methods.
You might wonder why I'm using an ODBC driver instead of an OLE DB provider. Actually, I'm using the OLE DB provider for ODBC-MSDASQL-which Microsoft ships with ADO. It's the default provider for a Connection object. The code sets the Provider property just for illustration. MSDASQL is an interim solution that allows you to access any existing ODBC data source until providers are widely available. For example, SQL Server 7 and ADO 2.0 will ship with a native OLE DB provider so you can set the Provider to SQLOLEDB and use a connect string that simply specifies the server, database, and login information.
As in the other data-access methods, you can specify a cursor type for the Connection. In this application, I used cursorless resultsets where possible for efficiency. Establishing a connection is different in DAO, where you first create a Workspace object for ODBCDirect and then use the OpenConnection method to create and open the connection.
Once the connection is open, you can try a few techniques. The simplest technique in the application is to open a resultset from a client-generated SQL statement as the application does in the ExecSQL method. To do this in ADO, simply instantiate a Recordset object and call the Open method:
Set rsResults = New ADODB.Recordset
rsResults.Open "SELECT * FROM authors", _
cnADO, adOpenForwardOnly, _
adLockReadOnly, adCmdText
If rsResults.EOF And rsResults.BOF Then
MsgBox "No rows returned"
Else
myRows = _
rsResults.GetRows(adGetRowsRest)
'Populate column headings
For i = 0 To _
rsResults.Fields.Count - 1
ReDim Preserve myHeadings(2, i)
myHeadings(1, i) = _
rsResults.Fields(i).Name
myHeadings(2, i) = _
rsResults.Fields(i).DefinedSize
Next
'close the resultset
rsResults.Close
Set rsResults = Nothing
End If
The interesting aspect of the ADO technique is that you don't create the recordset with a method of the Connection object, as in RDO and DAO. Instead, you create it independently and then associate it with the Connection object through an argument provided by the Open method. Instead of checking the RowCount or RecordCount properties to determine if the recordset is populated, ADO sets both the EOF and BOF properties to True if the recordset is empty. If records are returned, use the exposed GetRows method of the recordset to load the rows into a two-dimensional array. With ADO, you can use the constant adGetRowsRest to retrieve all the remaining rows, instead of having to loop or hard-code a parameter, as with the GetRows methods in RDO and DAO.
The Fields collection, analogous to the rdoColumns and Fields collections in RDO and DAO respectively, exposes column information in ADO. You can then use the Field object to determine the column name and size to load an array or grid control with data, as the sample application does.
COMMAND YOUR DATA
Because of the maintainability and performance of stored procedures, applications often use them extensively. Happily, ADO makes calling stored procedures simpler and more efficient than in either RDO or ADO. The sample application exposes an ExecSPArg method to compare the techniques for calling a stored procedure that has one input and one output parameter and issues a SQL INSERT against the database. To call the procedure, simply instantiate a Command object and set its CommandText and CommandType properties:
Set cmADO = New ADODB.Command
With cmADO
.CommandText = "AddDepartment"
.CommandType = adCmdStoredProc
End With
The CommandType allows you to specify what the CommandText contains so that ADO doesn't have to query the ODBC driver or the server to set up the query. A simple check using SQL Server's SQL Trace utility verifies that an extra server round-trip is required for both RDO and DAO. For stored procedures that accept and return arguments, you then create Parameter objects for each argument and append them to the Command object:
Set pmADO = _
cmADO.CreateParameter("name", _
adVarChar, adParamInput, _
Len(strName), strName)
cmADO.Parameters.Append pmADO
Set pmADO = _
cmADO.CreateParameter("dept_id", _
adInteger, adParamOutput, 4)
cmADO.Parameters.Append pmADO
The parameter objects can fully specify the data type, direction, and length of each argument. RDO and DAO don't create the parameters independently, but expose them as collections of the query objects. Although this means you don't have to create and append them yourself, it doesn't allow them to be reused with multiple queries. When the command is ready to run, associate it with a Connection object and execute it with the Execute method:
cmADO.ActiveConnection = cnADO
cmADO.Execute
ADO always returns a recordset from the Execute method, but in this case you'll discard it because the stored procedure performs an INSERT statement, which doesn't return a resultset. This method is simpler than either RDO or DAO, which mandate that you use the more complex ODBC "call" syntax when specifying the procedure in the rdoQuery and QueryDef objects respectively:
Dim qdRDO as rdoQuery
Set qdRDO = cnRDO.CreateQuery("", _
"{ call AddDepartment (?, ?) }"
UPDATABLE RECORDSETS
Although modifying the database through stored procedures is the recommended method, ADO also lets you update both connected and disconnected recordsets. To compare updating a connected recordset, the sample application includes an ExecTranUpdate method. It uses the recordset's Open method to create an updatable recordset using the adLockPessimistic or adLockOptimistic option. As in the other object models, ADO's Recordset supports move methods that allow you to position the recordset to a particular row. Once you position the recordset, you can use the BeginTrans method of the Connection object to begin a transaction on the server to coordinate multiple updates, deletes, or inserts that must be executed together:
rsResults.MoveFirst
cnADO.BeginTrans
To modify the recordset, you can access the Fields collection directly by using the column's number or name. Then, reposition the recordset and make other changes before calling the Update method:
rsResults.Fields(1).Value = strNewName
rsResults.MoveNext
rsResults.Fields(1).Value = strNewName2
rsResults.Update
Depending on the outcome of the update, use the CommitTrans or RollbackTrans method of the Connection object to make the changes permanent or discard them. By using the Requery method, you can then repopulate your recordset:
cnADO.CommitTrans
rsResults.Requery
The ADO technique is simpler than either RDO or DAO because you don't have to call either the Edit method of the recordset or the Update method to allow editing after each change. Although RDO's Connection object and DAO's Workspace object also support BeginTrans and CommitTrans methods, DAO's didn't affect my server. As a result, I had to use the Execute method of DAO's Connection object to issue the "BEGIN TRANSACTION" and "COMMIT TRANSACTION" statements on the server.
All three data-access techniques can also update disconnected recordsets cached on the client. After updating the recordset, the application can reassociate a Connection object with the recordset and send the updated records. Although this technique isn't useful in all situations-since it generates collisions-you can use it in applications that effectively partition data or don't support persistent connections to the server. Using batch updates will become more widespread when ADO 2.0 supports persistent recordsets, allowing the application to not only disconnect from the server but shut down and re-start while maintaining the recordset (see the sidebar, "What's New in ADO 2.0"). The method ExecQBatch in the sample application compares the implementation of batch updates across the three object models. In ADO, open a recordset using the adLockBatchOptimistic option with a client-side static or keyset cursor:
rsResults.CursorLocation = adUseClient
rsResults.Open "SELECT * FROM authors", _
cnADO, adOpenStatic, _
adLockBatchOptimistic, adCmdText
In RDO, set the CursorDriver property of the rdoConnection object to rdUseClientBatch, and set the locking option on the rdoResultset to rdConcurBatch. In DAO, set the DefaultCursorDriver property of the Workspace object to dbUseClientBatch-Cursor, and set the locking option of the Recordset object to dbOptimisticBatch.
After you've opened the recordset, discard the connection by setting the ActiveConnection of the ADO recordset to Nothing. Then, navigate through the recordset and update it at will. When you're ready to send the changes, simply reassociate the recordset with a valid Connection object and call the UpdateBatch method:
Set rsResults.ActiveConnection = cnADO
rsResults.UpdateBatch adAffectAll
The adAffectAll constant specifies that ADO's UpdateBatch method sends all modified records to the server. To deal with this, set the ActiveConnection property before calling BatchUpdate, and set the Connection property before calling Update-just like in RDO and DAO.
Although the update is similar, the technique for dealing with collisions-rows that the server rejects-differs in ADO from the other models. In ADO, use the Filter property of the recordset to traverse the records that were modified, checking their Status property to determine if the update succeeded on all rows. ADO raises a trappable error only if the server rejects all the records in the update:
rsResults.Filter = adFilterAffectedRecords
Do While Not rsResults.EOF
If rsResults.Status <> _
adRecUnmodified And _
rsResults.Status <> adRecOK Then
'this record had a problem
End If
rsResults.MoveNext
Loop
rsResults.Filter = adFilterNone
RDO and DAO handle collisions differently. The rdoResultset and Recordset objects contain BatchCollisionCount properties that indicate how many rows weren't successfully processed by the server. The rdoResultset BatchCollision-Rows and the Recordset BatchCollisions properties then return an array of bookmarks that point to rows that weren't updated successfully.
BE FRUITFUL AND MULTIPLY
Some applications require the ability to process multiple resultsets returned by the server from a single query containing two or more SELECT statements. To test the process for dealing with this, I created an ExecSPMult method in each object to test retrieving two sets of results from a stored procedure. All three cases use a similar technique to deal with this. In ADO, use the Command object to execute the stored procedure. After retrieving the records with GetRows or navigating the recordset, call the NextRecordset method of the recordset to clear the current recordset and populate the next recordset:
Set rsResults = rsResults.NextRecordset
If rsResults Is Nothing Then
MoreRecordsets = False
Else
MsgBox "Ready for the next one?"
End If
The MSDASQL provider doesn't support addressability on multiple recordsets, although future OLE DB 2.0 providers might. In other words, you can't assign the next recordset to a different object and navigate both recordsets independently, because the first recordset is always closed when you call NextRecordset.
Both RDO and DAO are slightly simpler in this regard because they allow you to call a method and check its return value in a single statement. However, this simplicity bars them from supporting multiple addressability as well. In RDO, check the MoreResults method of the rdoResultset, and in DAO, check the NextRecordset method of the Recordset object. If they return True, these methods automatically populate your existing rdoResultset or Recordset object with the next set of results.
The three data access methods diverge the most in asynchronous operations. ADO 1.5 doesn't support asynchronous operations when used in VB. The full ADO implementation with events is one of the features slated for version 2.0 and is available in the mid-April beta. Async is supported in both RDO and DAO, so I've implemented ExecAsync methods to show you how it works.
In both cases the method executes a stored procedure called AsyncTest, which accepts an argument that causes the procedure to pause before sending back a small result set. Both RDO and ADO 2.0 support asynchronous operations through events that are exposed when you declare objects with the WithEvents keyword. By declaring the rdoConnection object WithEvents, RDO adds six events to the class module. One of these events, QueryComplete, fires when an asynchronous query has finished passing back a reference to the rdoQuery object that executed the query. Within this event you can reference the populated rdoResultset and process it.
DAO isn't as sophisticated and doesn't expose events. After executing a query with the dbRunAsync option, you must continuously check the StillExecuting property of the recordset to detect the end of the query:
Do While rsResults.StillExecuting
DoEvents
Loop
ABORT, RETRY, IGNORE
All three data-access methods also expose similar collections that store error information. Frequently, the ODBC driver and database return multiple levels of errors, so it makes sense to catch these errors in a collection. In ADO the Connection object exposes an Errors collection that contains error objects with common properties such as Number, Source, and Description. RDO exposes a global rdoErrors collection with similar properties; the DAO DBEngine object exposes an Errors collection as well. In certain cases, such as batch updating, you'll want to check this error information although a trappable error doesn't occur.
Overall, ADO 1.5 allows you to do almost everything you can currently do with RDO and DAO. The RDO and DAO interfaces will be around for a while, so don't panic and start converting your existing apps that work with RDO and DAO. But, you should con-sider ADO for new development. In this vein you'll want to check out the white paper on choosing a data access interface at http://www.microsoft.com/vbasic/techmat/whitepapers/choosing/. In addition, ISG has also released a data control for ADO that you can download from its Web site at http://www.isgsoft.com/products/ISGData to help jump-start your development.
Although I didn't test for performance, my experience indicates that ADO 1.5 with the MSDASQL provider performs at least on a
par with RDO 2.0. Adding native OLE DB providers in the future should allow ADO 2.0 performance to surpass that of RDO.
Dan Fox lives in Overland Park, Kansas, and is a consultant and trainer for Solutech Inc. He's a Microsoft Certified Systems Engineer, Solution Developer, and Trainer developing client/server and intranet solutions. He also teaches Visual Basic, SQL Server, and Visual InterDev. Reach Dan on the Internet at danlfox@email.msn.com.