In Microsoft SQL Server 2000 Windows CE Edition 2.0, in SQL Server 2005 Compact Edition, or in SQL Server 2005 Mobile Edition, you can use the
SqlCeDataReader class to read a forward-only stream of data rows from a data
source. To use the
SqlCeDataReader class, you must include a reference to the
System.Data.SqlServerCe namespace. The
SQlCeDataReader class is an alternative of the DataSet. The DataSet is an
in-memory cache of data. While the
SqlCeDataReader class is in use, the associated
SqlCeConnection class is busy serving the
SqlCeDataReader class. You cannot perform another operation on the
SqlCeConnection class until the
SqlCeDataReader class is closed.
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()
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)
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
For additional information about the
SqlCeDataReader class, see the "SqlCeDataReader Class" topic in SQL Server CE 2.0
Books Online, in SQL Server 2005 Compact Edition Books Online, or in SQL Server 2005 Mobile Edition Books Online.