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 would contain the company name .