How to: Seek Using the SqlCeDataReader Class

This topic describes how to seek when you use the SqlCeDataReader.

Seeking on an Index

By using the Seek method of the SqlCeDataReader object, you can quickly find rows in a result set (or cursor). When seeking, you can specify both the range of the index over which to seek and the way in which rows are selected. To seek, the IndexName property must be specified on the SqlCeCommand object.

Setting the Index Range

The SetRange method of the SqlCeCommand object specifies the range of the index on which to seek. Ranges are typically specified with index start and end values and range options. These are defined by the DbRangeOptions enumeration. If no range options are specified, by default the range includes both the start and end values. If a range option of DbRangeOptions.Match is set, the range include only rows in which index values match the startData value. If a range option of DbRangeOptions.Prefix is set, the range includes all rows in which the index values start with the startData value. When you use Match or Prefix, the end value must be set to NULL. For more information, see the "DbRangeOptions Enumeration" topic in the .NET Framework Class Library reference.

Seek Options

Seek options specify how to select rows on an index. Options, such as FirstEqual, LastEqual, BeforeEqual, and AfterEqual, that have Equal in their names, select rows that match the seek value. If no rows of the index match the seek value, a row either before, in the case of FirstEqual and BeforeEqual, or after, as in the case of AfterEqual and LastEqual, is selected. For more information, see the "DbSeekOptions Enumeration" topic in the .NET Framework Class Library reference.

Example

The following example shows the Seek method being executed on a three-column index in which the data types on the columns are integer, datetime, and money. The index range for the integer index is from 1 to 5, inclusive. The index ranges for the datetime and money columns start at 1/1/1996 and $10.00, respectively. This sample selects a row in which the integer column is 1, the datetime column is 1/1/1997, and the money column is $10.50. If an index that matches this criteria does not exist, the AfterEqual property causes the next row on the index to be selected.

// Example that seeks on a three-column index
public void CreateMySqlCeCommand(SqlCeConnection conn) 
{
    SqlCeCommand cmd = conn.CreateCommand();
    cmd.CommandType  = CommandType.TableDirect;

    // This is the name of the base table. 
    cmd.CommandText  = "Orders";

    //Assume: Index contains three columns [int, datetime, money]
    cmd.IndexName    = "SomeIndex"; 

    object[] start = new object[3];
    object[] end   = new object[1];

    start[0] = 1;
    start[1] = new SqlDateTime(1996, 1, 1);
    start[2] = new SqlMoney(10.00);

    end[0]   = 5;

    cmd.SetRange(DbRangeOptions.InclusiveStart |
        DbRangeOptions.InclusiveEnd, start, end); 

    SqlCeDataReader rdr = cmd.ExecuteReader();
    rdr.Seek(DbSeekOptions.AfterEqual, 1, new SqlDateTime(1997, 1,1), 
        new SqlMoney(10.50));
 
    while(rdr.Read()) 
    {
        // Read data in the usual way.    
    }
    rdr.Close();
}

See Also

Reference

System.Data.SqlServerCe Namespace Overview
System.Data.SqlServerCe Namespace (.NET Framework Reference Documentation)

Help and Information

Getting SQL Server Compact Edition Assistance