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

View products that this article applies to.

This article was previously published under Q325815

SUMMARY

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.

Back to the top

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.

Back to the top

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

Back to the top

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.

Back to the top

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

Back to the top

REFERENCES

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.

Back to the top