Types of Cursors
Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) cursors are similar to cursors used in SQL Server. The differences are documented in this section. For a complete explanation about database cursors, see SQL Server Books Online.
SQL Server Compact Edition supports the following types of cursors:
- Base table
- Static
- Forward-only
- Forward-only/Read-only
- Keyset-driven
Base Table Cursors
Base table cursors are the lowest level of cursor available. These cursors work directly against the storage engine and are the fastest of all supported cursor types. Base table cursors can scroll forward or backward with minimal cost, and can be updated.
You can also open a cursor directly on an index. Indexes are supported to order the rows in a table, to enable seeking on particular values, and to restrict the rows based on a range of values within an index.
Base table cursors have dynamic membership. This means that two cursors opened over the same table can immediately see insertions, deletions, and changes to the data, assuming both are in the same transaction scope. Because you can update base table cursors, a client can use this kind of cursor to make changes to the underlying data.
Base table cursors cannot represent the result of a query. Results of queries, such as SELECT * FROM tablename, are not returned through a base table cursor. Instead, one of the supported query result cursors is used.
The following is an example of how to obtain a base table cursor by using ADO .NET:
//Base Table Cursor
cmd.CommandText = "tablename";
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
The following is an example of how to obtain an index cursor by using ADO .NET:
cmd.CommandText = "tablename";
cmd.IndexName = "indexname";
cmd.CommandType = CommandType.TableDirect;
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
Static Cursors
A static cursor, referred to as a scrollable query cursor in earlier versions of SQL Server Compact Edition, creates and stores a complete copy of the result set. The exception to this is long-value data that is retrieved when a user explicitly requests it. This result set is filled only as needed. This is different from SQL Server, which populates the result set at cursor creation. Static cursors support scrolling backward and forward, but they do not support updates. Static cursors do not see external changes to the data that is insensitive. Query results are cached for the lifetime of the cursor. Although static cursors are more functional than forward-only cursors, static cursors are slower and use more memory. We recommend that you consider static cursors only if scrolling is required and a keyset cursor is not appropriate.
The following is an example of how to obtain a static cursor by using ADO.NET:
cmd.CommandText = "Select * from tablename";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Insensitive);
Forward-only Cursors
The forward-only cursor is the fastest cursor that you can update, but it does not support scrolling. It supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. The effects of all INSERT, UPDATE, and DELETE statements made by the current user or committed by other users that affect rows in the result set are visible as the rows are fetched from the cursor. Because the cursor cannot be scrolled backward, changes made to rows in the database after the row was fetched are not visible by using the cursor.
Forward-only and forward-only/read-only cursors are the fastest query-based cursors. They should be used in scenarios in which speed and memory footprint are most important.
The following is an example of how to obtain a forward-only cursor by using ADO .NET:
cmd.CommandText = "Select * from tablename";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Updatable);
Forward-only/Read-only Cursors
Forward-only/read-only cursors, referred to as forward-only cursors in earlier versions of SQL Server Compact Edition, are the fastest cursors, but cannot be updated.
The following is an example of how to obtain a forward-only/read-only cursor by using ADO.NET:
cmd.CommandText = "Select * from tablename";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.None);
Note You cannot create read-only cursors on query that returns only read only columns because internally all SQL Server Compact Edition cursors are updatable. SQL Server Compact Edition cannot update read-only columns returned in the SqlCeResultSet. Therefore, it would fail with the error "Cannot generate an updatable cursor for the query because there is no updatable column."
Keyset-driven Cursors
The keyset-driven cursor in SQL Server Compact Edition is a scrollable cursor that you can update. A keyset-driven cursor is controlled by a set of physical identifiers known as the keyset. The keyset is based on all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset is built in a temporary table when the cursor is opened. With a keyset-driven cursor, membership is determined at the time that the query is executed.
Keyset-driven cursors in SQL Server Compact Edition differ slightly from those in SQL Server. In SQL Server, the keyset-driven cursor uses a set of unique identifiers as the keys in the keyset. In SQL Server Compact Edition, the keys are bookmarks for where values are logically stored in a table. They are not unique identifiers.
Although sensitive to a number of changes, a keyset-driven cursor is not as sensitive as other cursors. For example, an insert outside the cursor will not be seen, although inserts inside the cursor will be seen at the end. In this case, we recommend that you close and reopen the cursor, or use one of the forward-only cursors.
Because SQL Server Compact Edition uses bookmarks to define a keyset, all changes to data values for rows that are included in the keyset are visible by using the cursor. This is the case for both changes that are made within the cursors and changes that are made outside the cursor.
Any deletes in a keyset cursor, whether within or outside the cursor, will cause the row to be reported as deleted if an attempt is made to fetch it.
The following is an example of how to obtain a keyset-driven cursor by using ADO .NET:
cmd.CommandText = "Select * from tablename";
SqlCeResultSet rs = cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);
Working with Joins
If the query you use to open a keyset-driven cursor includes joined columns, these columns are not updateable. The user can insert new values into these columns, but updates are not supported.
If the keyset is used to populate a user-updateable control, like a DataGrid object, users might try to update the values in the control, and the update will fail. If you are developing an application that uses a DataGrid to display joined column data, ensure that you set the joined columns in the DataGrid to Read-only.
See Also
Concepts
Cursors (SQL Server Compact Edition)
Choosing a Cursor Type
Cursors and Locking
Implicit Cursor Conversions
Updating Keyset-driven Cursors