Using Cursors with ADO
ADO uses both client and server cursors to implement the cursor functionality required by an application. An ADO application controls the cursor behavior by using the CursorType, CursorLocation, LockType, and CacheSize properties of the Recordset object.
When these properties are set to their default values at the time an SQL statement is executed, the Microsoft OLE DB Provider for SQL Server (SQLOLEDB) does not use a server cursor to implement the result set; instead, it uses a default result set. If any of the values of these properties are changed from their default values at the time an SQL statement is executed, SQLOLEDB attempts to use a server cursor to implement the result set.
Cursor Options with SQL Server
Because ADO allows the setting of cursor properties, the following options exist for using cursors with ADO and Microsoft® SQL Server™ 2000:
- Leave all cursor properties set to their defaults.
If you use these settings, the provider uses default result set processing (forward only and read-only cursor). The default settings allow a program to execute any Transact-SQL statement; however, only one statement can be active on any connection at a time. The program must either fetch all the rows or cancel the result set before another statement can be executed on the same connection. Following those rules, a program can process Transact-SQL statements or stored procedures that allow multiple result sets.
- Change the default cursor type or lock type.
The provider uses SQL Server API server cursors to deliver the requested cursor functionality. Although this option provides a wide range of cursor functionality, it introduces some restrictions. For example, you cannot execute any Transact-SQL statement, batch, or stored procedure that returns more than one result set. However, it is possible to have multiple active statements on one connection (there can be pending results in the statement handle), provided they are all executed with API server cursors.
- Use an ADO client cursor (set the CursorLocation property to adUseClient).
ADO implements the cursor; therefore, the application can use only the capabilities supported by the ADO client cursors. The application cannot access the cursor capabilities of the underlying provider. Only a CursorType property of adOpenStatic (static cursor) is supported for a setting of adUseClient.
ADO Cursor Settings
An ADO application can control the cursor functionality using these Recordset properties.
| Property | Description |
|---|---|
| CursorType | Default: adOpenForwardOnly Indicates the type of cursor used: Forward-only/read-only (adOpenForwardOnly) Static (adOpenStatic) Keyset (adOpenKeyset) Dynamic (adOpenDynamic) |
| CursorLocation | Default: adUseServer Sets or returns the location of the cursor engine. If you set this property to adUseClient, you can open only a static cursor. |
| LockType | Default: adLockReadOnly Indicates the type of locks placed on rows during editing. |
| CacheSize | Default: 1 Controls how many rows the provider keeps in its buffer and how many rows to retrieve at one time into local memory. |