Michael Pizzo
Microsoft Data Access Group
July 1998
Summary: Outlines key new features and updates to ADO, OLE DB, ODBC, and Remote Data Service (RDS). (5 printed pages) Covers:
In August 1996, Microsoft released OLE DB 1.0, along with a prerelease version of Microsoft® ActiveX® Data Objects (ADO) 1.0. These two core components comprised the foundation of the new Microsoft Universal Data Access strategy for providing easy and robust access to all types of data from the desktop to the enterprise.
The 1.0 release was followed in September 1996 by the 1.1 release. The 1.1 release included a release version of ADO and a set of remoting components known at the time as Advanced Data Connector (ADC).
Since then, these core technologies have matured into a feature-rich architecture and set of components for building robust, scalable solutions. This article reviews some of the key new features added to ADO and OLE DB, as well as the evolution of ADC into what is now known as Remote Data Service (RDS), and the continued enhancement of the Microsoft de facto standard for accessing purely relational data, Open Database Connectivity (ODBC).
A major area of enhancement in Microsoft Data Access 2.0 is performance and scalability. All of the 2.0 components, from the ODBC Driver Manager and drivers to the OLE DB providers and components through ADO, have been profiled and tuned to deliver optimal performance and scalability.
In addition to tuning the individual components, the 2.0 release includes resource pooling at the OLE DB level. Connections and transaction enlistment are preserved between uses. Because of this, middle-tier applications that rely on quick, individual database actions can share connections, rather than create and release connections for each action. This sophisticated resource pooling maintains multiple, homogeneous pools of connections, reducing contention and improving throughput. Additional information, such as the provider's initialization properties, support for aggregation, and even the provider's class factory, are cached after the first connection to the provider to further improve performance and scalability.
Performance monitors for accessing ODBC data have been added to the ODBC Driver Manager, and ODBC driver pooling and other options are now configurable from Control Panel. In addition, support for the Microsoft Visual Studio™ Analyzer has been added to both the ODBC Driver Manager and the ADO libraries.
Additional performance enhancements at the ADO level include native language bindings. The C++ bindings enable developers to bind directly to C-language data types without overhead for describing metadata or performing VARIANT conversions. Similarly, with the release of Windows Foundations Classes (WFC), Java developers can bind directly to native Java data types. Finally, ADO has added options to optimize how commands are executed: You can specify adExecuteNoRecords to remove the overhead of asking for a rowset on non-row–returning commands; and adCmdTableDirect forces ADO to use IOpenRowset rather than attempt to open the rowset by building and executing a Command object.
In OLE DB 1.0, providers and consumers both had to be free-threaded in order to execute commands asynchronously. OLE DB 1.5 added a mechanism for connecting to providers, executing commands, and fetching results through either polling or callback. This mechanism provides additional information about the status of execution without requiring provider and consumer to be multithreaded. ADO 2.0 adds asynchronous support for connecting to a provider, executing a command, and fetching data.
OLE DB rowsets are Component Object Model (COM) objects, and OLE DB rowsets can contain COM objects. This provides an interesting way to model hierarchical data by binding to a child rowset as a cell within a parent rowset. However, this means that a separate rowset must be created, and accessors, notifications, and other per-rowset states must be specified for each child rowset.
In OLE DB 2.0, a single rowset can be used to retrieve data for each level in a hierarchical query. The parent rowset contains a "chapter-valued" column that identifies the set of rows within the single child rowset that pertains to that parent. Specifying this chapter value when calling row-retrieval methods on the child rowset effectively limits the rowset to those rows that pertain to the given chapter.
ADO 2.0 supports binding to hierarchical data by exposing "tear-off" recordsets. Binding to a column in a recordset that represents nested data returns a child recordset for that data. The way this recordset works is identical to how any other recordset in ADO works.
The ability to model data hierarchically is provided through the new Data Shaping Provider, shipped as part of the Microsoft Data Access 2.0 components. This provider can be accessed through ADO or through OLE DB directly, and it works like any other provider. When connecting to the Data Shaping Provider, the user specifies the underlying provider from which the data to shape is obtained. The Data Shaping Provider supports a special syntax for relating multiple resultsets from the underlying provider to each other, similar to a relational join except that the results are returned as a hierarchical OLE DB rowset or ADO recordset. Alternatively, the Data Shaping Provider can be used to aggregate values and return the results as hierarchical data.
OLE DB 1.5 added the ability to find a record within a rowset according to a column value. This provides the ability to support simple find operations and list box support. In addition, the specification defined a concept of views that enable simple providers that do not support textual commands to expose simple filtering and sorting mechanisms, where appropriate. For command-based providers, applying a view to an existing rowset allows consumers to do postprocessing of results obtained from a query. The consumer does not have to reexecute the query and make an additional round trip to the server to reretrieve a subset of the data already obtained.
ADO 2.0 fully supports finding, filtering, and sorting on recordsets. Where the OLE DB provider supports these extensions, ADO uses the provider to perform these operations for optimal performance. Where not supported by the provider, common services under ADO provide these extensions to ensure that the ADO functionality works when accessing data from any OLE DB provider.
With the 1.1 release of OLE DB, data could be remoted across the Internet or intranet by using a set of components called Advanced Data Connector. ADC required a separate programming model for obtaining the data from the remote server, but the result was an OLE DB rowset or ADO recordset that could be consumed like any other rowset or recordset.
For the 2.0 release, the ADC functionality has been subsumed in a core set of data access services called Remote Data Service. RDS exposes remoting to the ADO 2.0 consumer in the form of a Remote Provider, which is accessed like any other provider. When connecting to the Remote Provider, the ADO user specifies the name of the remote server, along with connection information for the provider on the remote server. In this way, the ADO 2.0 consumer can access remote data in the same way as any other data, without writing special code. In addition, ADO 2.0 provides much richer support for conflict resolution when attempting to update remote data that may have changed since it was retrieved. Finally, ADO 2.0 allows the consumer to persist a recordset, including changes, to disk. The recordset can later be loaded and used like any other recordset and reconnected to the server to submit updates. The next release of Remote Data Service will extend this integration to the OLE DB level by enabling OLE DB programmers to directly use the Remote Provider, just as they use any other OLE DB provider.
The 2.0 release of the Remote Data Service has some additional enhancements, such as a customization handler for the RDS.DataFactory object. Server administrators can use the customization handler to control the operations that can be executed by RDS through the default RDS.DataFactory object. Also, RDS 2.0 has added a cross-domain security feature that honors the security settings in the browser. Depending on the specified level of security, users are now restricted to connect to a database on the server from which the page was downloaded.
Microsoft Data Access Components 2.0 include a rich set of high-performance data providers to common data formats.
The OLE DB Provider for ODBC has shipped in OLE DB from version 1.0 on. This provider has been enhanced in 2.0 to provide significantly better performance and robustness. In addition, many of the restrictions that applied to handling of binary large objects (BLOBs) in previous versions have been relaxed to provide better interoperability when retrieving large data values.
In addition to the updated OLE DB Provider for ODBC, three new providers have been added in version 2.0:
In addition to the ADO libraries, RDS and ODBC components, and core drivers and providers, the Microsoft Data Access Components (MDAC) include a set of core services that can be shared by multiple providers and consumers.
The core services include a common service for enumerating registered OLE DB providers and a common data conversion component that can be used by any OLE DB provider to perform the conversions specified in the OLE DB specification. This component, which shipped as part of the OLE DB 1.0 Software Development Kit (SDK), has been enhanced and now supports a number of additional conversions.
New in MDAC 2.0 is a universal Data Link component that provides a common user interface for specifying connection information to any provider. The connection information can be represented by using a common string format. Applications can use the Universal Data Link dialog box to collect information from the user, and users can use the Universal Data Link dialog box to create or edit connection information persisted in a universal Data Link (.udl) file. Because this component is used by ADO to parse the connection string, ADO users can pass a connection string directly or can specify a .udl file containing the connection information.
MDAC 2.0 also includes a common set of service components for providing guaranteed functionality against any provider. One such service is the previously described resource pooling component. In addition, the 2.0 service components support automatic transaction enlistment in a Microsoft Transaction Server environment. A set of service component managers provide guaranteed functionality—such as scrolling, find support, and updatability of SQL data—by invoking components that provide such functionality when requested by the consumer if not supported by the provider.
The redistributable Microsoft Data Access Components are available as a separate download, or as part of the unified Data Access SDK. The SDK includes the comprehensive specification, along with tools and samples for writing, testing, and debugging providers or consumers. For 2.0, Microsoft has combined the OLE DB, ADO, and remoting components that previously shipped in the OLE DB SDK, along with the components from the ODBC SDK, into a single Data Access SDK.
The 2.0 release of the Data Access SDK includes updated OLE DB test tools for new interfaces, a comprehensive set of test suites and source code for verifying OLE DB providers, a Rowset Viewer application for ad hoc visual testing of the provider, and an updated Local Test Manager (LTM) for automating execution of test suites. Additionally, the Sample OLE DB Provider has been significantly updated to expose additional functionality and to pass the comprehensive test suites.
The 2.0 release of the Microsoft Data Access Components and Data Access SDK represents a significant upgrade in performance and scalability, feature completeness, and component integration. In addition to the areas highlighted, the 2.0 release includes enhanced OLE DB interfaces for handling constraints, persisting commands, and navigating integrated indexes, the ability to fabricate recordsets on the fly in ADO, full Unicode support in ODBC, and additional enhancements too numerous to describe here. With this release, Microsoft Universal Data Access becomes not just a technology, but a mature, robust solution to accessing all types of data from the desktop through the enterprise.