by Joyce Chen and Richard Patterson
Consistently accessing data within the enterprise is a challenge for today’s business applications. ODBC provides the first step toward overcoming this challenge by enabling applications to access relational databases. However, as developers and system architects want to include nonrelational data sources and to work in environments such as the Internet, they encounter the dilemma of either developing their own data-access paradigms or working with application program interfaces (APIs) that are incompatible in the new environments. Microsoft®ActiveX® Data Objects (ADO) along with OLE DB solves this dilemma by providing a single model that works with all data sources in a variety of environments.
ADO provides consistent, high-performance access to data, whether you’re creating a front-end database client or middle-tier business object using an application, tool, language, or even an Internet browser. ADO is the single data interface you need to know for 1- to n-tier client/server and Web-based, data-driven solution development.
This paper introduces ADO and the ADO programming model for application developers who are targeting Microsoft SQL Server. Particular attention is given to taking advantage of SQL Server features with ADO, such as stored procedures and server cursors. The concepts presented in the sections titled “The ADO Object Model” and “Using ADO with Visual Basic®, Visual Basic, Scripting Edition (VBScript), Visual C++®, and Java” are applicable to all ADO programmers.
The Microsoft Visual Basic OutputParam project is referenced and included with this paper. The OutputParam project contains examples on how to use the ADO object model with SQL Server. OutputParam contains two files:
ADO was first introduced as the data access interface in Microsoft® Internet Information Server. ADO is implemented with a small footprint to provide minimal network traffic in key Internet scenarios, and a minimal number of layers between the front-end and data source. ADO is easy to use because it is called using a familiar metaphor: the Automation interface, available from just about any tool and language on the market today. Because of its popularity as an easy-to-use, lightweight interface to all kinds of data, and the growing need for an interface spanning many tools and languages, ADO is being enhanced to combine the best features of, and eventually replace RDO and DAO, the data access interfaces in widest use today. ADO is in many ways similar to RDO and DAO, for example, it uses similar language conventions. ADO provides simpler semantics, which makes it easy to learn for today’s developers.
ADO is designed to be the application-level interface to OLE DB, Microsoft’s newest and most powerful data access paradigm. OLE DB provides high-performance access to any data source. Together ADO and OLE DB form the foundation of the Universal Data Access strategy. OLE DB enables universal access to any data. ADO makes it easy for developers to program. Because ADO is built on top of OLE DB, it benefits from the rich universal data access infrastructure that OLE DB provides.
OLE DB is an open specification designed to build on the success of ODBC by providing an open standard for accessing all kinds of data throughout the enterprise. OLE DB is a core technology supporting universal data access. Whereas ODBC was created to access relational databases, OLE DB is designed for the relational and nonrelational information sources, such as mail stores, text and graphical data for the Web, directory services, and IMS and VSAM data stored in the mainframe. OLE DB components consist of data providers, which expose their data; data consumers, which use data; and service components, which process and transport data (for example, query processors and cursor engines). These components are designed to integrate smoothly to help OLE DB component vendors quickly bring high-quality OLE DB components to market. OLE DB includes a bridge to ODBC to enable continued support for the broad range of ODBC relational database drivers available today.
There are two types of OLE DB applications: consumers and providers. A consumer can be any application that uses or consumes OLE DB interfaces. For example, a Microsoft Visual C++ application that uses OLE DB interfaces to connect to database server is an OLE DB consumer. The ADO object model that uses OLE DB interfaces is an OLE DB consumer. Any application that uses the ADO object model uses OLE DB interfaces indirectly through the ADO objects.
An OLE DB provider implements OLE DB interfaces; therefore, an OLE DB provider allows consumers to access data in a uniform way through a known set of documented interfaces. In a sense, an OLE DB provider is similar to an ODBC driver that provides a uniform mechanism for accessing relational data. OLE DB providers not only provide a mechanism for relational data but also for nonrelational types of data. Furthermore, OLE DB providers are built on top of OLE Component Object Model (COM) interfaces that allow more flexibility; where a ODBC driver builds on top of a C API specification.
Microsoft OLE DB SDK version 1.1 shipped two OLE DB providers: the ODBC Provider and sample text provider. The sample text provider serves as an example that demonstrates the implementation detail of an OLE DB provider. The ODBC Provider is an OLE DB provider for ODBC drivers. This provider provides mechanism for consumers to use the existing ODBC drivers without having to rush to implement new OLE DB providers to replace existing ODBC drivers. For more information about OLE DB and OLE DB providers, see the OLE DB area at http://www.microsoft.com/data/.
The ODBC Provider maps OLE DB interfaces to ODBC APIs. With the ODBC Provider, OLE DB consumers can connect to a database server through the existing ODBC drivers. A consumer calls an OLE DB interface on the ODBC Provider. The ODBC Provider invokes corresponding ODBC APIs and sends the requests to an ODBC driver.
Because the ODBC Provider allows OLE DB consumers to use existing ODBC drivers, there may be some performance concern about the additional layer of the ODBC Provider on top of the existing ODBC driver manager. The design goal of the ODBC Provider is to implement all the functionality of the ODBC driver manager; therefore, the ODBC driver manager is not needed. However, with the ODBC Provider version 1.1, the ODBC driver manager is still required to support connection pooling with ODBC applications.
The ADO object model defines a collection of programmable objects that can be used in Visual Basic, Visual C++, VBScript, Java, and any platform that supports both COM and Automation. The ADO object model is designed to expose the most commonly used features of OLE DB.
The ADO object model contains seven objects:
and four collections:
The illustration shows their relationships.
ADO Object Model
The Properties collection and Property object that are not shown in the illustration are available through the Connection, Recordset, and Command objects. The Properties collection and Property object contain provider properties that can be read-only or read/write.
The Connection, Recordset, and Command objects are the centerpieces of the ADO object model. An ADO application can use the Connection object to establish connections with the database server, the Command object to issue commands, such as queries, updates, and so on to the database, and the Recordset object to view and manipulate the returned data. The command language used with the Command object is dependent on the underlying provider for the database. In the case of relational databases, the command language is generally SQL.
The Command object may be optional if the underlining OLE DB provider does not implement the ICommand interface. Because OLE DB providers can exist on top of relational or nonrelational databases, the traditional SQL statements may not be available in a nonrelational database to query data; therefore, the Command object is not required. If the Command object contains parameters, the parameters information can be viewed or specified through the Parameters collection and the Parameter object. The Parameter object describes parameter information for the Command object.
In ADO versions 1.0 and 1.5, all objects can be created except the Error, Field, and Property objects. The Error collections and Error object can be accessed through the Connection object after a provider error occurs. The Field collections and Field object can be accessed through the Recordset object after data exists in the Recordset object. The metadata information of the Recordset object can be viewed through the Fields collection and the Field object.
The Connection object allows you to establish connection sessions with data sources. The Connection object provides a mechanism for initializing and establishing the connection, executing queries, and using transactions.
The underlying OLE DB provider used for connecting is not limited to the ODBC Provider; other providers can also be used for connecting. A provider can be specified through the Provider property. If none is specified, MSDASQL is the default ODBC provider used for the connection.
The Open method of the Connection object is used to establish a connection. With the ODBC Provider, an ADO application can use the ODBC connection mechanism to connect to a database server. ODBC allows applications to establish a connection through the ODBC data sources, or by explicitly specifying the data source information (commonly referred to as DSN-Less connection). For more information about DSN-Less connections with databses, such as SQL Server, see “Using ODBC with SQL Server” in the Microsoft SQL Server Developer’s Resource Kit.
Before establishing connections, applications can set up a connection string, connection time-out, default database, and connection attributes. The Connection object also allows you to set up the CommandTimeout property for all the command objects associated with this connection. Query strings can be issued through the Execute method.
Transactions can be controlled through the Connection object. The BeginTrans, CommitTrans, and RollbackTrans methods are provided for using transactions.
The example shows using the ODBC Provider, the default OLE DB provider in ADO, to connect to SQL Server:
Dim Cn As New ADODB.Connection
Cn.ConnectionTimeout = 100
' DSN connection
#Cn.Open "pubs", "sa"
' DSN-Less connection for SQL Server
' Cn.Open "Driver={SQL Server};Server=Server1;Uid=sa;Pwd=;Database=pubs"
Cn.CloseThe example first sets the connection time-out value to 100 seconds, and then opens a connection using the pubs ODBC data source that points to a SQL Server. A user ID is required for the SQL Server specified in the pubs data source; therefore, the sa user ID is provided as the second parameter of the Open method. There is no password, so the third parameter is omitted.
In addition to using an ODBC data source, this example also provides a commented line that shows how to connect to SQL Server without an ODBC data source. The SQL Server ODBC driver, {SQL Server}, is used to connect to a SQL Server called Server1. The user ID to connect to Server1 is sa, and there is no password for sa. The default database for this connection is the pubs database.
The example shows using the Provider property to specify an alternative OLE DB provider:
Dim Cn As New ADODB.Connection
Dim rs As ADODB.Recordset
Cn.Provider = "sampprov"
Cn.Open "data source=c:\sdks\oledbsdk\samples\sampclnt\"
Set rs = Cn.Execute("customer.csv")An OLE DB provider, sampprov, is specified in this example. In addition to the ODBC Provider, OLE DB SDK shipped a sample text provider. The text provider allows an application to retrieve data from a text file. In this example, a connection is established by specifying the data source directory c:\sdks\oledbsdk\samples\sampleclnt\, and the data in the customer.csv file is returned as the result of the Execute method.
The example shows using the BeginTrans and the CommitTrans or RollbackTrans methods:
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
' Open connection.
Cn.Open "pubs", "sa"