Programming Visual Basic .NET

Reading Data Using a DataReader

As you have seen, the DataSet class provides a flexible way to read and write data in any data source. There are times, however, when such flexibility is not needed and when it might be better to optimize data-access speed as much as possible. For example, an application might store the text for all of its drop-down lists in a database table and read them out when the application is started. Clearly, all that is needed here is to read once through a result set as fast as possible. For needs such as this, ADO.NET has DataReader classes.

Unlike the DataSet class, DataReader classes are connected to their data sources. Consequently, there is no generic DataReader class. Rather, each managed provider exposes its own DataReader class, which implements the System.Data.IDataReader interface. The SQL Server managed provider exposes the SqlDataReader class (in the System.Data.SqlClient namespace). DataReader classes provide sequential, forward-only, read-only access to data. Because they are optimized for this task, they are faster than the DataSet class.

Example 8-12 shows how to read through a result set using an SqlDataReader object.

Example 8-12. Using a SqlDataReader object

' Open a database connection. Dim strConnection As String = _ "Data Source=localhost;Initial Catalog=Northwind;" _ & "Integrated Security=True" Dim cn As SqlConnection = New SqlConnection(strConnection) cn.Open( ) ' Set up a command object. Dim strSql As String = "SELECT * FROM ...