SqlCeCommand.SetRange Method

  • Article

Restricts the set of rows that will be read by the SqlCeDataReader.

Namespace:  System.Data.SqlServerCe
Assembly:  System.Data.SqlServerCe (in System.Data.SqlServerCe.dll)

Syntax

'Declaration
Public Sub SetRange ( _
    dbRangeOptions As DbRangeOptions, _
    startData As Object(), _
    endData As Object() _
)
'Usage
Dim instance As SqlCeCommand
Dim dbRangeOptions As DbRangeOptions
Dim startData As Object()
Dim endData As Object()

instance.SetRange(dbRangeOptions, startData, _
    endData)

public void SetRange(
    DbRangeOptions dbRangeOptions,
    Object[] startData,
    Object[] endData
)

public:
void SetRange(
    DbRangeOptions dbRangeOptions, 
    array<Object^>^ startData, 
    array<Object^>^ endData
)

member SetRange : 
        dbRangeOptions:DbRangeOptions * 
        startData:Object[] * 
        endData:Object[] -> unit 

public function SetRange(
    dbRangeOptions : DbRangeOptions, 
    startData : Object[], 
    endData : Object[]
)

Parameters

  • startData
    Type: array<System.Object[]
    The starting key values for the range.
  • endData
    Type: array<System.Object[]
    The ending key values for the range.

Exceptions

Exception Condition
InvalidOperationException

IndexName property has not been set.

Remarks

This method is intended to be a faster alternative to a SELECT statement for retrieving a set of rows from a base table. Instead of a WHERE clause in a SELECT statement, SetRange can be used to quickly retrieve a set of rows based on their index values. For example, to retrieve a set of employees with an employee ID between 1 and 5, you could execute a SELECT statement, but setting a range of 1 to 5 on the employee ID index will greatly improve performance.

This method can only be used when CommandType is set to TableDirect, CommandText is set to a valid base table name, and IndexName is set to a valid index name on the specified base table. When SetRange is used, the SqlCeDataReader returned from ExecuteReader will only return rows whose key values in the specified index match the range.

When Seek is used on a SqlCeDataReader that has a range, Seek will only position on rows in the specified range. For detailed information about SetRange, see the "IRowsetIndex::SetRange" topic in the OLE DB documentation.

Examples

This example retrieves data from the Orders table using an index into a SqlCeDataReader. The range on the index on which to Seek is specified by the SetRange method.

        Try
            Dim conn As New SqlCeConnection("Data Source = MyDatabase.sdf")
            conn.Open()

            Dim cmd As SqlCeCommand = conn.CreateCommand()
            cmd.CommandType = CommandType.TableDirect
            cmd.IndexName = "Orders_PK"
            cmd.CommandText = "Orders"

            ' We are interested in orders that match Order ID = 10020
            '
            cmd.SetRange(DbRangeOptions.Match, New Object() {10020}, Nothing)

            Dim reader As SqlCeDataReader = cmd.ExecuteReader(CommandBehavior.Default)

            While reader.Read()
                MessageBox.Show(String.Format("{0} ; {1}", reader("Order ID"), reader("Order Date")))
            End While

            ' Now we are interested in orders with Order ID between (10020, 10050)
            '
            cmd.SetRange(DbRangeOptions.InclusiveStart Or DbRangeOptions.InclusiveEnd, New Object() {10020}, New Object() {10050})

            reader = cmd.ExecuteReader(CommandBehavior.Default)

            ' Now seek to Order ID = 10045
            '
            Dim onRow As Boolean =  reader.Seek(DbSeekOptions.FirstEqual, New Object() {10045})

            ' Now ,the reader will return rows with Order ID >= 10045 <= 10050
            ' because the range was set to (10020, 10050)
            '
            If onRow Then
                While reader.Read()
                    MessageBox.Show(String.Format("{0} ; {1}", reader("Order ID"), reader("Order Date")))
                End While
            End If
        Catch e As Exception
            MessageBox.Show(e.Message)
        End Try

        try
        {
            SqlCeConnection conn = new SqlCeConnection("Data Source = MyDatabase.sdf");
            conn.Open();

            SqlCeCommand cmd = conn.CreateCommand();
            cmd.CommandType = CommandType.TableDirect;
            cmd.IndexName = "Orders_PK";
            cmd.CommandText = "Orders";

            // We are interested in orders that match Order ID = 10020
            //
            cmd.SetRange(DbRangeOptions.Match, new object[] { 10020 }, null);

            SqlCeDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);

            for (int i = 1; reader.Read(); i++)
            {
                MessageBox.Show(String.Format("{0} ; {1}", reader["Order ID"], reader["Order Date"]));
            }

            // Now we are interested in orders with Order ID between (10020, 10050)
            //
            cmd.SetRange(DbRangeOptions.InclusiveStart | DbRangeOptions.InclusiveEnd,
                new object[] { 10020 }, new object[] { 10050 });

            reader = cmd.ExecuteReader(CommandBehavior.Default);

            // Now seek to Order ID = 10045
            //
            bool onRow = reader.Seek(DbSeekOptions.FirstEqual, new object[] { 10045 });

            // Now ,the reader will return rows with Order ID >= 10045 <= 10050
            // because the range was set to (10020, 10050)
            //
            if (onRow)
            {
                for (int i = 1; reader.Read(); i++)
                {
                    MessageBox.Show(String.Format("{0} ; {1}", reader["Order ID"], reader["Order Date"]));
                }
            }
        }
        catch (Exception e)
        {
            MessageBox.Show(e.Message);
        }

See Also

Reference

SqlCeCommand Class

System.Data.SqlServerCe Namespace