DbRangeOptions Enumeration

  • Article

Specifies the options used by the SetRange method when specifying the index range over which to seek.

This enumeration has a FlagsAttribute attribute that allows a bitwise combination of its member values.

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

Syntax

'Declaration
<FlagsAttribute> _
Public Enumeration DbRangeOptions
'Usage
Dim instance As DbRangeOptions

[FlagsAttribute]
public enum DbRangeOptions

[FlagsAttribute]
public enum class DbRangeOptions

[<FlagsAttribute>]
type DbRangeOptions

public enum DbRangeOptions

Members

Member name Description
InclusiveStart Includes the startData value in the range.
InclusiveEnd Includes the endData value in the range.
ExclusiveStart Excludes the startData value from the range.
ExclusiveEnd Excludes the endData value from the range.
ExcludeNulls Excludes nulla null reference (Nothing in Visual Basic) values from the range.
Prefix Specifies a range in which index values begin with the value of startData. When using the Prefix option, endData must be set to nulla null reference (Nothing in Visual Basic).
Match Specifies a range in which index values match the value of startData. When using the Match option, endData must be set to nulla null reference (Nothing in Visual Basic).
Default Equivalent to setting both the InclusiveStart and InclusiveEnd flags.

Remarks

If you specify the Match or Prefix option, the endData value must be nulla null reference (Nothing in Visual Basic).

You cannot combine the Match and ExcludeNulls options.

Examples

In this example, the range options for the Seek operation on the index are specified as InclusiveStart or InclusiveEnd when calling 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

System.Data.SqlServerCe Namespace