Microsoft KB Archive/325815

= Using SqlCeDataReader with SQL Server CE 2.0 or SQL Server 2005 Compact Edition in Visual Basic .NET =

Article ID: 325815

Article Last Modified on 12/6/2006

-

APPLIES TO


 * Microsoft SQL Server 2000 Windows CE Edition 2.0
 * Microsoft SQL Server 2005 Compact Edition

-



This article was previously published under Q325815



SUMMARY
In Microsoft SQL Server 2000 Windows CE Edition 2.0 or in SQL Server 2005 Compact 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(&quot;provider=microsoft.sqlserver.oledb.ce.2.0;data source=\my documents\northwind.sdf&quot;) con.Open Dim cmd As SqlServerCe.SqlCeCommand cmd = New SqlServerCe.SqlCeCommand(&quot;Select ProductID from products&quot;, 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 &quot;Advanced Programming Using System.Data.SqlServerCe&quot; topic in SQL Server CE Books Online. The following sample code illustrates the Seek method syntax: cmd.commandtype = commandtype.tabledirect cmd.commandtext = &quot;products&quot; cmd.indexname = &quot;pid&quot; 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

