OLE DB Cursors (SQL Server Compact)

Microsoft SQL Server Compact 3.5 supports the base table, forward-only, and scrollable cursor types. When any one of these supported cursors is used, the client can have only one open row.

Base Table Cursor

The base table cursor is the fastest of the supported cursors and the only cursor that interacts directly with the storage engine. This cursor supports updates, bookmarks, and indexes, and the Seek and SetRange methods of IRowsetIndex. The base table cursor optionally supports delayed update mode by displaying the IRowsetUpdate interface when the client passes DBPROP_IRowsetUpdate as TRUE. This cursor has dynamic membership; both DBPROP_OTHERINSERT and DBPROP_OTHERUPDATEDELETE properties are TRUE.

Base table cursors can be opened only by using IOpenRowset::OpenRowset. Using a "SELECT * FROM table" yields a query processor cursor, forward-only or scrollable, and not a base table cursor. Only base table cursors support updates.

Forward-Only Cursor

Although the forward-only read-only cursor is the fastest query processor cursor and the second fastest cursor overall, this cursor provides the least amount of functionality. It does not support bookmarks or updates.

Scrollable Cursor

The scrollable read-only cursor is the most functional query processor cursor. The result set of this cursor is cached. This provides fast cursor restarts but makes it more resource intensive, especially for queries with large result sets. Because the results are cached, changes to the underlying base tables for a query are not reflected in the query results unless the query is re-executed.

Required Property Settings

The following table shows the required property settings for each cursor type.

Property setting	Base table	Forward-only	Scrollable
DBPROP_BOOKMARKS	VARIANT_TRUE	VARIANT_FALSE	VARIANT_TRUE
DBPROP_OWNUPDATEDDELETE	VARIANT_TRUE	VARIANT_FALSE	VARIANT_FALSE
DBPROP_OWNINSERT	VARIANT_TRUE	VARIANT_FALSE	VARIANT_FALSE
DBPROP_OTHERUPDATEDELETE	VARIANT_TRUE	VARIANT_FALSE	VARIANT_FALSE
DBPROP_OTHERINSERT	VARIANT_TRUE	VARIANT_FALSE	VARIANT_FALSE
DBPROP_CANFETCHBACKWARDS	VARIANT_TRUE	VARIANT_FALSE	VARIANT_TRUE
DBPROP_QUICKRESTART	VARIANT_TRUE	VARIANT_FALSE	VARIANT_TRUE