Using SqlCeDataReader with SQL Server CE 2.0 or SQL Server 2005 Mobile Edition in Visual Basic .NET (325815)

MORE INFORMATION

Create a SqlCeDataReader

To create a stream of data rows by using the SqlCeDataReader class, you must call the ExecuteReader method of the SqlCeCommand object to create a SqlCeDataReader class. The following sample code illustrates how to create a SqlCeDataReader class:

        Dim con As SqlServerCe.SqlCeConnection = New SqlServerCe.SqlCeConnection("provider=microsoft.sqlserver.oledb.ce.2.0;data source=\my documents\northwind.sdf")
        con.Open()
        Dim cmd As SqlServerCe.SqlCeCommand
        cmd = New SqlServerCe.SqlCeCommand("Select ProductID from products", con)
        Dim rdr As SqlServerCe.SqlCeDataReader
        rdr = cmd.ExecuteReader()
				

Note This sample code assumes that the My Documents\Northwind.sdf database file already exists on the SQL Server CE device.

Read the Data

After you create the SqlCeDataReader class, you can use it to read the data. To read the stream of data rows by using the SqlCeDataReader class, you must use the Read method of the SqlCeDataReader class. The Read method moves the SqlCeDataReader class to the next record. The Read method also has a Boolean return value. If the return value is TRUE, there are additional rows; if the return value is FALSE, there are no additional rows. For example, you can use the following sample code to read the data:

        While rdr.Read
            ListBox1.Items.Add(rdr(0))
        End While
				

Use the Seek Method

The SqlCeDataReader class has a Seek method that you can use to quickly find rows in a result set (or cursor). When you use the Seek method, you can specify the index and the way that the rows are selected. You must specify the IndexName parameter on the SqlCeCommand object.

By using the SetRange method of the SqlCeCommand object, you can specify the range of the index where you want to search. For additional information about how to set the index range, see the "Advanced Programming Using System.Data.SqlServerCe" topic in SQL Server CE Books Online. The following sample code illustrates the Seek method syntax:

     cmd.commandtype = commandtype.tabledirect
     cmd.commandtext = "products"
     cmd.indexname = "pid"
     rdr = cmd.executereader
     rdr.seek(data.sqlserverce.dbseekoption.before, 5)
				

Note The code assumes that there is a Product ID (PID) index on the ProductID column of the products table.

Close the SqlCeDataReader

After you read the data, you can close the SqlCeDataReader class. Remember that if the SqlCeDataReader class is in use, you cannot perform another operation on the SqlCeConnection class until the SqlCeDataReader class is closed. To check to see if the SqlCeDataReader class is open or closed, call the IsClosed property. The property returns as TRUE if the SqlCeDataReader class is closed and returns as FALSE if it is open. The following code is an example of the IsClosed property:

        If rdr.IsClosed = False Then
             rdr.Close()
        End If