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 OLE 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:
Figure 1 shows their relationships.
Figure 1. The 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 parameter 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 Field collections and the Field objects.
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 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 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.Close
The 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"
' 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 by itself. It depends on the Connection object and errors from below ADO (OLE DB providers, ODBC drivers, and data sources) 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 example shows retrieving 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
A SQL Server connection is established through the pubs data source. The select * from TableDoesnotExist query string is issued with the Execute method of the Connection object. An error occurs as the result of executing the query string because it attempts to select data from a nonexisting 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, prepare query strings, and associate parameters with query strings. The actual command language and features that are 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 area at the Microsoft Universal Data Access Web site (http://www.microsoft.com/data/).
The Command object can either open a new connection or use an existing connection to perform queries, depending on what is specified in the ActiveConnection property. If the ActiveConnection property is set with a reference to a Connection object, the Command object uses the existing connection from the Connection object. If the ActiveConnection property is specified 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 record set, multiple record sets, or no record set at all. For example, executing data definition language queries does not generate a record set. Executing a single SELECT statement may generate a record set, and executing a batch of SELECT statements or a stored procedure may generate more than one record set.
A query string can be specified in the CommandText property. A query string can be a standard SQL data manipulation language, such as SELECT, INSERT, DELETE, or UPDATE, or any data definition language, such as CREATE or DROP. A query string can also be the name of a stored procedure or table.
The type of a query string can be specified in the CommandType property. There are adCmdText, adCmdTable, adCmdStoreProc, and adCmdUnknown values for the CommandType property. When a query string is a SQL statement, the adCmdText value should be specified for the CommandType property. The adCmdStoreProc or adCmdTable values should be specified if the query string is the name of a stored procedure or table name.
If the adCmdStoredProc value is specified, the Command object executes the query string with the {call procedure=name} ODBC escape function syntax. If the adCmdTable value is specified, the Command object executes the query string with the select * from tablename syntax. If the adCmdUnknown value is specified, the Command object must perform extra steps to determine the type of query string, thereby degrading performance.
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 more than once, 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 can also be useful when repeatedly executing a parameterized query string. Different parameter values can be substituted at each execution instead of reconstructing the query string. The Parameter object can be created through the CreateParameter method. For more information, see the "Using Prepared Statements" section later in this paper.
The example shows executing 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 a 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 show executing 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:
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, the AppendChunk method can be use to write data in chunks. For more information, see the "Using Long Data Types" section later in this paper.
The following examples show creating parameters for a stored procedure.
The stored procedure syntax is:
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:
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 is used to create 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 is used to append 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. The Recordset object allows you to add, update, delete, and scroll through records in the record set. Each record can be retrieved and updated using the Fields collection and the Field objects. Updates on the Recordset object can be in an immediate or batch mode. When a Recordset object is created, a cursor is automatically opened.
The Recordset object allows you to specify the cursor type and cursor location for fetching 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 a Recordset object can be scrolled forward/backward or updated. The cursor type also affects the visibility of changed records.
By default, the cursor type is read and forwarded 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 plays a big role in disconnected record sets. For more information, see the "Using Server Cursors" section later in this paper. A Recordset object can be created through the Execute method of the Connection or Command object.
The example shows using 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 record set, then loops through the record set to print the data in the first field for each row in this record set.
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 method of the Recordset object to apply the new or changed data.
Unlike RDO, there is no explicit Edit method that must be specified. Updating on the Field object is as simple as changing the data; no explicit update method is required.
The example shows using 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, the Fields collection can be retrieved. The example loops through the Fields collection to retrieve each Field object. The Name, Type, and Value property of each Field object are printed.
The Properties collection and Property object provide information about the characteristics of the Connection, Command, Recordset, and Field object. The Properties collection can be accessed through the Connection, Command, Recordset, and Field object, and the Property object can be accessed through the Properties collection using the default indexing method.
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, there is ConnectionTimeout, a property that provides information on the number of seconds to wait for establishing connection before returning a time-out error. There is a CommandTimeout property for the Command object, Updatability property for the Recordset and DEFAULTVALUE for the Field object.
The example shows retrieving 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.