The ADO object model defines a collection of programmable objects that you can use in Visual Basic, Visual C++, Microsoft Visual Basic, Scripting Edition, 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 types of collections:
The illustration shows their relationships.
Figure 1. ADO Object Model
The Properties collection and Property object that are not shown in the illustration are available through the Connection, Recordset, Field 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 underlying 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, view or specify the parameter information through the Parameters collection and the Parameter object. The Parameter object describes parameter information for the Command object.
In ADO, you can create all objects independently except the Error, Field, and Property objects. Access the Errors collection and Error object through the Connection object after a provider error occurs. After data exists in the Recordset object, access the Fields collection and Field object through 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 a communication link with a data source. 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; you can also use other providers for connecting. Specify a provider through the Provider property. If none is specified, MSDASQL (the ODBC provider) is the default provider used for the connection.
Use the Open method of the Connection object 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 to explicitly specify the data source information (commonly referred to as DSN-less connection). For more information about DSN-less connections with databases, 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 commands executed on this connection. You can issue query strings through the Execute method.
You control transactions through the Connection object. The BeginTrans, CommitTrans, and RollbackTrans methods are provided for using transactions.
The example demonstrates how to use 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.Close
The example first sets the connection time-out value to 100 seconds, and then opens a connection using the pubs ODBC data source. A user ID is required for 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, a commented line in the example 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 following example demonstrates how to use 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 following example demonstrates how to use 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"
' Open titles table.
rs.Open "Select * From titles", Cn, adOpenDynamic, adLockPessimistic
Cn.BeginTrans
' make changes
Cn.CommitTrans
' or rollback
' cn.RollbackTrans
Cn.Close
After the connection is established, this example begins a transaction. The data changed in this transaction can be committed or rolled back.
The Errors collection and Error object allow you to retrieve provider error information when an error occurs. Errors can be generated by a method call or property of the Connection, Command, or Recordset objects, but are always retrieved from the Connection object. The Errors collection does not exist on its own. It depends on the Connection object and errors from underlying technologies (OLE DB providers, ODBC drivers, data sources, and so on) to go to the Errors collection. Invalid property values or use of the ADO interfaces do not add an Error object to the collection.
The Errors collection also stores warnings. A warning does not stop code execution, and a warning's positive number values differentiate it from a real error. The Error object allows you to retrieve the error description and source of the error. The SQLSTATE and database-native error information is also available from the Error object when working with the ODBC Provider.
The following example demonstrates how to retrieve SQLSTATE, native-error, and error descriptions from the Error object when using the ODBC Provider:
Dim Cn As New ADODB.Connection
Dim Errs1 As ADODB.Errors
Dim rs As New ADODB.Recordset
Dim i As Integer
Dim StrTmp
On Error GoTo AdoError
Cn.Open "pubs", "sa"
Set rs = Cn.Execute("Select * From TableDoesnotExist")
Done:
' Close all open objects.
Cn.Close
' Destroy anything not destroyed yet.
Set Cn = Nothing
' We're outta here.
Exit Sub
AdoError:
Dim errLoop As Error
Dim strError As String
' Enumerate Errors collection and display properties of
' each Error object.
Set Errs1 = Cn.Errors
For Each errLoop In Errs1
Debug.Print errLoop.SQLState
Debug.Print errLoop.NativeError
Debug.Print errLoop.Description
Next
GoTo Done
The pubs data source establishes a SQL Server connection. The Execute method of the Connection object issues the SELECT * FROM TableDoesnotExist query string. An error occurs as the result of executing the query string because it attempts to select data from a nonexistent table.
After the error occurs, program execution jumps to AdoError. The Errors collection is retrieved from the Connection object and assigned to the Errs1 Errors collection. The example then loops through the Errs1 Errors collection to retrieve the SQL Server native error, ODBC SQLSTATE mapped by the SQL Server ODBC driver, and the error message.
The Command object allows you to issue commands to the database. These commands can be, but are not limited to, query strings, prepared query strings, and associated parameters with query strings. The actual command language and features supported are dependent on the underlying provider for the database. The information and examples contained here focus on the Microsoft ODBC Provider that supports a wide variety of relational databases. For more information about OLE DB and OLE DB providers, see the OLE DB section of the Microsoft Data Access Web page at //www.microsoft.com/data/.
The Command object either opens a new connection or uses an existing connection to perform queries, depending on what you specify in the ActiveConnection property. If you set the ActiveConnection property with a reference to a Connection object, the Command object uses the existing connection from the Connection object. If you specify the ActiveConnection property with a connection string, a new connection is established for the Command object. More than one Command object can use the connection from the same Connection object.
Executing query strings can generate a Recordset object, multiple Recordset objects, or no result set at all. For example, executing data definition language queries does not generate a result set. Executing a single SELECT statement may generate a Recordset, and executing a batch of SELECT statements or a stored procedure may generate more than one Recordset.
Specify a query string in the CommandText property. A query string can be a standard SQL data manipulation language statement, such as SELECT, INSERT, DELETE, or UPDATE, or any data definition language statement, such as CREATE or DROP. A query string can also be the name of a stored procedure or table.
You can specify the type of a query string in the CommandType property using the following values: adCmdText, adCmdTable, adCmdStoredProc, adCmdFile, adCmdTableDirect, adExecuteNoRecords, and adCmdUnknown. When a query string is a SQL statement, specify the adCmdText value for the CommandType property. Specify the adCmdStoredProc or adCmdTable values if the query string is the name of a stored procedure or table name. A variation on adCmdTable is adCmdTableDirect, which is new to ADO 2.0, and is designed specifically for OLE DB providers which support both SQL Statements and direct opening of tables by name (via the IOpenRowset method on the provider).
If you specify the adCmdStoredProc, the Command object executes the query string with the {call procedure=name} ODBC escape function syntax. If you specify the adCmdTable value, the Command object executes the query string with the select * from tablename syntax.
Use the value adCmdFile when working with ADO 2.0. It can be used to load a Recordset which had been saved to disk using the Recordset.Save method. Persisting Recordset objects isn't covered in this paper, but see the ADO 2.0 online documentation for more information.
If the adCmdUnknown value is specified, the Command object must perform extra steps to determine the type of query string, thereby degrading performance.
In ADO 2.0, another option has been made available which can increase performance. When working with a command which will not return a Recordset, you can include the value adExecuteNoRecords in with the CommandType property. This value works only with adCmdText and adCmdStoredProc CommandTypes.
You can also specify whether or not to prepare query strings with the Prepared property. Setting the Prepared property allows a query plan to be created at the first execution. The query plan is then used for subsequent executions to enhance performance. A query string should be prepared only when executed several times, because it may take more time to create a query plan than to execute the query string directly. The performance is enhanced only when you execute the query string the second, third, or nth time.
The Prepared property is also useful when repeatedly executing a parameterized query string. Substitute different parameter values at each execution instead of reconstructing the query string. Create the Parameter object through the CreateParameter method. For more information, see the "Using Prepared Statements" section later in this paper.
The following example demonstrates how to execute a SELECT statement that returns a Recordset object:
Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
' Use a connection string or a Connection object.
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "select * from titles"
Cmd.CommandTimeout = 15
Cmd.CommandType = adCmdText
Set rs = Cmd.Execute()
rs.Close
The DSN=pubs;UID=sa ODBC connection string is specified in the ActiveConnection property. The SELECT * FROM titles statement is specified in the CommandText property, and because it is an SQL statement, the CommandType property is set to adCmdText. The CommandTimeout is set to 15 seconds. Executing the SELECT * FROM titles SQL statement may return a Recordset object, which is assigned to the rs Recordset object.
The following examples demonstrate how to execute a stored procedure that does not return a Recordset object.
The stored procedure syntax is:
drop proc myADOProc
go
create proc myADOProc as
create table #tmpADO (id int not NULL, name char(10) NOT NULL)
insert into #tmpADO values(1, 'test')
The myADOProc stored procedure creates the tmpADO temporary table and inserts a row into this table. Both the CREATE and INSERT SQL statements do not generate any result sets; therefore, no result sets are returned by executing the myADOProc stored procedure.
The Visual Basic code is as follows:
Dim Cmd As New ADODB.Command
' Use a connection string or a Connection object.
Cmd.ActiveConnection = "DSN=pubs;UID=sa"
Cmd.CommandText = "myADOProc"
Cmd.CommandTimeout = 15
Cmd.CommandType = adCmdStoredProc
Cmd.Execute
The myADOProc stored procedure is specified in the CommandText property. The CommandType property is set to adCmdStoredProc to reflect the type of object executed, and the {call myADOProc} SQL statement is generated as a result. Because there are no result sets generated from executing this stored procedure, no Recordset object is assigned.
The Parameters collection provides parameter information and data for the Command object. The Parameters collection consists of Parameter objects. Both the Parameters collection and Parameter object are needed only when the query string in the Command object requires parameters.
Individual parameter information (for example, size, data type, direction, and value) can be read or written through each Parameter object. There are four types of parameter directions: input, output, both input and output, and return value. A Parameter object can serve as an input parameter, an output parameter that holds data, or a stored procedure return value. The Refresh method on the parameters collection can force providers to update parameter information. However, this can be time consuming.
When dealing with long data types, use the AppendChunk method to write data in chunks. For more information, see the "Using Long Data Types" section later in this paper.
The following examples demonstrate how to create parameters for a stored procedure.
The stored procedure syntax is as follows:
drop proc myADOParaProc
go
create proc myADOParaProc
@type char(12)
as
select * from titles where type = @type
The myADOParaProc takes one @type input parameter and returns data that match the specified type. The data type for the @type parameter is character, and the size is 12.
The Visual Basic code is as follows:
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim prm As ADODB.Parameter
' Define a Command object for a stored procedure.
cmd.ActiveConnection = "DSN=pubs;uid=sa"
cmd.CommandText = "myADOParaProc"
cmd.CommandType = adCmdStoredProc
cmd.CommandTimeout = 15
' Set up new parameter for the stored procedure.
Set prm = Cmd.CreateParameter("Type", adChar, adParamInput, 12, "Business")
Cmd.Parameters.Append prm
' Create a record set by executing the command.
Set rs = Cmd.Execute
While (Not rs.EOF)
Debug.Print rs(0)
rs.MoveNext
Wend
The ActiveConnection, CommandText, CommandType, and CommandTimeout are specified with the same values as used in the previous example. The myADOParaPro stored procedures expects an input parameter whose data type is character and size is 12. The CreateParameter method creates a Parameter object with the corresponding characteristics: The data type is adChar for character, parameter type is adParamInput for input parameter, and data length is 12. This Parameter object is also given the name Type, and because it's an input parameter, the data value Business is specified.
After a parameter is specified, the Append method appends the Parameter object to the Parameters collection. The myADOParaProc stored procedure is executed, and a Recordset object is created.
The Recordset object provides methods for manipulating result sets; it allows you to add, update, delete, and scroll through records in the result set. You can retrieve and update each record using the Fields collection and the Field object. You can make updates on the Recordset object in an immediate or batch mode. When you create a Recordset object, a cursor is automatically opened.
The Recordset object allows you to specify the cursor type and cursor location for retrieving the result set. With the CursorType property, you can specify whether the cursor is forward-only, static, keyset-driven, or dynamic. The cursor type determines whether you can scroll through a Recordset object forward or backward, or whether you can update it. The cursor type also affects the visibility of changed records.
By default, the cursor type is read-only and forward-only. If you only need to read the data once in a forward fashion, you do not have to change the default cursor type. Otherwise, you can choose one of the cursors to suit your need.
You can also specify whether to use the server or client cursor with the CursorLocation property. The cursor location is important when using disconnected Recordset objects. For more information, see the section titled, "Using Server Cursors" later in this paper. A Recordset object can be created through the Execute method of the Connection or Command object, or the Open method of the Recordset object.
The following example demonstrates how to use the Recordset object to open a connection and retrieve a result set:
Dim rs As New ADODB.Recordset
rs.Open "select * from titles", "DSN=pubs;UID=sa"
While (Not rs.EOF)
Debug.Print rs(0)
rs.MoveNext
Wend
rs.Close
The example opens a connection, creates a Recordset, then loops through the Recordset to print the data in the first field for each row in this Recordset.
The Fields collection and Field object allow you to access each data column of the current record. The Fields collection can be accessed through the Recordset object and the Field object can be accessed through the Fields collection using the default indexing method. You can use the Field object to compose a new record or change existing data, and use the AddNew, Update, or UpdateBatch methods of the Recordset object to apply the new or changed data.
Unlike RDO, there is no explicit Edit method that you must specify. Updating on the Field object is as simple as changing the data; no explicit update method is required.
The following example demonstrates how to use the Field object to retrieve the name, type, and values of each data field of the current record:
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
rs.Open "select * from titles", "DSN=pubs;UID=sa"
Set Flds = rs.Fields
Dim TotalCount As Integer
TotalCount = Flds.Count
i = 0
For Each fld In Flds
Debug.Print fld.Name
Debug.Print fld.Type
Debug.Print fld.Value
Next
rs.Close
After the Recordset object is created for the SELECT * FROM titles query string, you can retrieve the Fields collection. The example loops through the Fields collection to retrieve each Field object. It prints the Name, Type, and Value property of each Field object.
The Properties collection and Property object provide information about the characteristics of the Connection, Command, Recordset, and Field objects. The Properties collection can be accessed through the Connection, Command, Recordset, and Field objects, and the Property object can be accessed through the Properties collection using the default indexing method.
Property objects allow providers to expose provider-specific functionality dynamically.
The Properties collection consists of Property objects. In addition to returning the value and type for a property, the Property object provides attributes of a property. Attributes describe things such as whether or not the specific property of an object is supported, required, or can be read or written. For example, the ConnectionTimeout property provides information about the number of seconds to wait while establishing a connection before returning a time-out error.
The following example demonstrates how to retrieve the ConnectionTimeout, CommandTimeout and Updatability properties:
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim rs As New ADODB.Recordset
Cn.Open "pubs", "sa"
' Find out ConnectionTimeout property.
Debug.Print Cn.Properties("Connect Timeout")
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = "titles"
Cmd.CommandType = adCmdTable
Set rs = Cmd.Execute()
' find out CommandTimeout property.
Debug.Print Cmd.Properties("Command Time out")
Debug.Print rs.Properties("Updatability")
The Properties collection is retrieved through the Connection, Command, and Recordset objects in this example. The ConnectionTimeout property of the Connection object is printed. The same steps are performed for the Command and Recordset objects.