Sometimes an application is only required to read data and not to update or write the data. Especially if such applications need large amount of data, then it is wiser to do away with the DataSet object because of its memory overheads. The DataReader requires very little memory because it just reads the data as its name suggests. It reads only one record at a time, and for applications using huge read only data, the DataReader is an excellent alternative because it is a read only and forward only stream that cuts down significantly on memory requirements.
After creating an instance of the Command object, a DataReader can be created by calling Command.ExecuteReader to retrieve rows from a data source. Assuming a connection as in the previous example is set up, the following code illustrates how to loop through a DataReader:
Dim dReader As OleDbDataReader Set dReader = Nothing dReader = cmd.ExecuteReader( Do While dReader.Read MsgBox(myReader.GetString(1)) End While |
The code above loops through the publishers table in the pubs database and displays the pub_id value for all of the publishers.
The Read method of the DataReader object is used to obtain a row from the results of the query. Columns can be accessed by passing the name or ordinal reference of the column to the DataReader. However, for best performance, the DataReader provides a series of methods that allow you to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on).