Using the DataReader Class

The DataSet class is a central figure in ADO.NET. However, you have other options for reading data. Suppose, for example, you only want to iterate through a set of data, perhaps browsing. You don't need to scroll back and forth, and you don't need to update the data. You have the option of using a DataReader object for read-only and forward-only data. The DataReader class yields better performance heuristics than the DataSet class for forward-only browsing. The technique for using a DataReader is similar to that for initializing a DataSet.

The DataReader is requested from a command object. We still need a connection, but we don't need an adapter or a DataSet. Listing 11.4 demonstrates how to initialize a command and DataReader object, using the reader to iterate over every row represented by the SQL SELECT statement.

Listing 11.4. Using a Connection and Command to Initialize a DataReader Object

Private Sub InitializeDropDownList()
  Dim Connection As OleDbConnection = _
    New OleDbConnection(Database.ConnectionString)

  Dim Command As OleDbCommand = _
    New OleDbCommand( _
    "SELECT CompanyName FROM CUSTOMERS", Connection)

  Dim Reader As OleDbDataReader
  Connection.Open()
  Try
    Reader = Command.ExecuteReader()
    While (Reader.Read())
      DropDownList1.Items.Add(Reader.GetString(0))
    End While
  Finally
    Reader.Close
    Connection.Close()
  End Try

End Sub

There are a couple of variations on Listing 11.4. For example, you can pass a CommandBehavior argument to Command.ExecuteReader, like CommandBehavior.CloseConnection, which instructs the reader to close the connection after the reader is obtained. In our example we used a verbose version that requires that we close the reader and then the connection explicitly. Readers and connections represent resources; hence it is helpful to use a Try . . . Finally block (referred to as a resource protection block) to ensure that the Close methods are called.

The basic behavior of the reader after it is obtained is to read all rows in a loop and process each row with a getter. To demonstrate, our example uses a While loop and the GetString method. The argument passed to GetString represents the ordinal column index. Because I requested only CompanyName, I knew that column 0 would contain the company name.