Share via

Visual Basic Concepts

  • Article

Creating RDO Cursors

Not all queries you create need to be returned in the form of a cursor. As a matter of fact, cursors are a particularly expensive way to fetch data and rarely used in production-class applications. If you set the rdoDefaultCursorDriver property to rdUseNone, all result sets created by your application will be created as if you used the OpenResultset method with the rdOpenForwardOnly and rdConcurReadOnly options set, and with RowsetSize set to 1. This is often the most efficient way to pass data from the remote server to your application.

It is also possible to create low-impact result sets that are also updatable through use of the Edit/Update methods. However, in most situations this approach is impractical because the base tables are not directly updatable, so creating an updatable cursor is not possible. Whenever you create a result set with a stored procedure, the result set is not updatable — at least not using the Edit/Update methods. In these cases, you can use the WillUpdateRows method to execute an action query that performs the actual update operation(s).

Regardless of how you create a cursor, you can usually update the data using one of several techniques — even if the cursor is not updatable:

  • Executing a stored procedure that updates a selected row based on a code-provided key.

  • Executing an action query that changes specifically addressed rows. In this case your code creates a suitable WHERE clause used in the query.

  • Using the WillUpdateRows event to trap update operations and substitute appropriate stored procedure calls to perform the actual changes.

In summary, there are not very many situations where cursors are an optimal or viable way to access your data — especially on large production databases. However, there are situations where you need to have the ability to:

  • Scroll forwards and backwards (browse) through a limited result set.

  • Move to a specific row based on a saved value (a bookmark).

  • Move to the 'nth' row of a result set in absolute or relative terms.

  • Update limited result sets created against base tables using the RemoteData control.

There are even fewer situations that justify inclusion of a query that simply creates an unrestrained cursor against one or more base tables. For example, "SELECT * FROM Table" is an example of an unrestrained query. Not only is this not a permissible option in protected systems, but it can cause serious concurrency problems as you attempt to scale your application to more than a few users. Whenever you create a cursor, be sure to limit the scope to the fewest number of rows possible. In interactive systems (where there is a human operator), fetching more than a few hundred rows is often counterproductive and leads to increasingly complex concurrency problems.