This article explains how to use the SqlCeRowset class to insert, update and delete records on a SQL Compact Edition database without going through the query processor.

To insert a large number of rows in a table, do the following:

1. Get a reference to a SqlCeDatabase object using the SqlCeConnection.SqlCeDatabase property.
2. Through this object get a reference to the table you want either by creating a SqlCeTable object (using the SqlCeDatabase reference and the table name), or by calling SqlCeDatabase.LoadSchema (which populates the SqlCeDatabase.Tables collection) and then using the Tables collection to retrieve a reference to a SqlCeTable.
3. Call SqlCeTable.Open and store the resulting SqlCeRowset object.

Now, you have a base table cursor to the table and you can directly insert, update and delete rows without using any SQL commands.

To insert data with an open SqlCeRowset object, do the following:

1. Call SqlCeRowset.PrepareRow – this will prepare the implicit row for insertion. All columns are marked as missing (this is a required step).
2. Use the SqlCeRowset.Set* methods to set the individual column data. You can use the column name or the column ordinal. Note that when using a base table cursor, column zero is the row bookmark. Always start numbering data columns at one using the table definition order.
3. Call SqlCeRowset.Insert to insert the row.
4. Go to step 1. to insert more rows.

By default the SqlCeRowset object works in synchronous mode. This means that for every transaction you have a roundtrip to the device: the desktop sends a message to the device and the device acknowledges it synchronously. This has proven to be a performance issue when you are bulk-inserting data on a database. The SqlCeRowset.ReportError property controls this behavior. By changing the value to SqlCeReportError.Async, no replies will be received from the device unless there is an error. These errors are not automatically detected (no exceptions will be thrown) so in order to check for these asynchronous errors you must check the Pipe.ErrorCount property from time to time.

The Pipe object encapsulates the data transfer protocol and uses two message queues: one for regular data and synchronous errors (these are thrown as exceptions) and another for asynchronous errors. You get a reference to a Pipe object through the SqlCeDatabase.Pipe property (this will be changed to a function named GetPipe in the next version). The number of asynchronous error messages is stored in the Pipe.ErrorCount property. To retrieve an error message you must use the Pipe.GetError function and use the resulting PipeMessage object as the parameter to a SqlCeException constructor. There is no need to throw this object unless your application calls for it.

Updating and deleting data using a SqlCeRowset requires the ability to position on a particular table row, and this implies the use of an index when the table is opened through SqlCeTable.Open. The index is specified through its name. To position on a particular row, you must call SqlCeRowset.Seek using an array of objects that matches the columns in the index (number, order and type). If the function returns true, the row was found and the cursor points to it so you can immediately call SqlCeRowset.Delete to delete it. To update existing data, use the SqlCeRowset.Set* methods to set the row data and then use SqlCeRowset.Update to update it on the database.

Note: When you are done using the SqlCeRowset, you must call the Dispose method to force the remote reclaiming of the device resources. Never rely on the desktop garbage collector to do this work for you.