The DataReader class provides a means to efficiently retrieve a forward-only stream of data from a database. DataReaders are appropriate when the need is to simply display the result set, as only one record at a time is ever present in memory.

The DataReader is mainly used in scenarios wherein data need not be updateable nor should persist across multiple requests.

This document briefly describes the steps to retrieve information from a database into a DataReader.

NOTE: While this code walk-thru uses the SQL Server™ .NET Data Provider, the same concepts apply to the corresponding OLEDB classes as well.

1. First, set up the connection.

   SqlConnection myConn = new SqlConnection ( "server=(local)\\NetSDK; trusted_connection=yes; database=pubs" ); Dim myConn As New SqlConnection ( _ "server=(local)\NetSDK; trusted_connection=yes; database=pubs" )
   	C#	VB

2. Next, set up a command with the given query and associate this with the current connection.

   String query = "SELECT * FROM Customers"; SqlCommand myCmd = new SqlCommand ( query, myConn ); Dim query As String = "SELECT * FROM Customers" Dim myCmd As SqlCommand = new SqlCommand ( query, myConn )
   	C#	VB

3. Open the connection and set up a DataReader by invoking ExecuteReader on the command object. The ExecuteReader method executes a given query, retrieves the rows from the data source, and binds the result set into the given instance of the DataReader class.

   myConn.Open ( ); SqlDataReader myReader = myCmd.ExecuteReader ( ); myConn.Open ( ) Dim myReader As SqlDataReader = myCmd.ExecuteReader ( )
   	C#	VB

When the method successfully completes, the DataReader object will contain the results of the query.

To obtain each row from the result set, you step through the DataReader in a very simple format.

while ( myReader.Read ( ) ) {
   // ... do your thing with the current row here
}

While myReader.Read ( ) 
   ' ... do your thing with the current row here
End While

The default position of the DataReader is prior to the first record. Therefore you must call Read to begin accessing any data. Read advances the DataReader to the next record, and at the same time returns true if there are more rows, and false otherwise. This makes the Read method suitable in a while loop, which executes as long as a condition is true.

The following example shows how to iterate thru the contents of a DataReader and return the values from the first and second fields of each row.

NOTE: This example uses one of the overloaded versions of the ExecuteReader method. For other examples that may be available, see the individual overload topics.

String query = "SELECT CustomerID, CompanyName FROM Customers";
SqlCommand myCmd = new SqlCommand ( query, myConn );
myConn.Open ( );
SqlDataReader myReader = myCmd.ExecuteReader ( );

while ( myReader.Read ( ) ) {
   Response.Write ( myReader.GetString ( 0 ) + ", " + myReader.GetString ( 1 ) );
}

myReader.Close ( );
myConn.Close ( );

Dim query As String = "SELECT CustomerID, CompanyName FROM Customers"
Dim myCmd As New SqlCommand ( query, myConn ) 
myConn.Open ( ) 
Dim myReader As SqlDataReader = myCmd.ExecuteReader ( ) 

While myReader.Read ( ) 
   Response.Write ( myReader.GetString ( 0 ) + ", " + myReader.GetString ( 1 ) ) 
End While

myReader.Close ( ) 
myConn.Close ( ) 

 Show me 

NOTE: This example uses one of the overloaded versions of the ExecuteReader method. For other examples that may be available, see the individual overload topics.

String query = "SELECT ProductName, ProductDescription FROM Products";
OleDbCommand myCmd = new OleDbCommand ( query, myConn );
myConn.Open ( );
OleDbDataReader myReader = myCmd.ExecuteReader ( );

while ( myReader.Read ( ) ) {
   Response.Write ( myReader.GetString ( 0 ) + ", " + myReader.GetString ( 1 ) );
}

myReader.Close ( );
myConn.Close ( );

Dim query As String = "SELECT ProductName, ProductDescription FROM Products"
Dim myCmd As New OleDbCommand ( query, myConn ) 
myConn.Open ( ) 
Dim myReader As OleDbDataReader = myCmd.ExecuteReader ( ) 

While myReader.Read ( ) 
   Response.Write ( myReader.GetString ( 0 ) + ", " + myReader.GetString ( 1 ) ) 
End While

myReader.Close ( ) 
myConn.Close ( ) 

 Show me 

You can also access each column of the returned row by simply passing the name or ordinal reference of the column to the DataReader.

myReader [ "CustomerID" ];

myReader ( "CustomerID" ) 

For best performance, though, the DataReader provides a set of methods that allow you to access column values in their native data types ( GetString, GetInt32, GetDouble, GetDateTime, and so on ). Using the typed accessor methods when the underlying data type is known lessens the type conversion required when retrieving column values, hence, performs better.

myReader.GetString ( colIndex ) 

For a list of typed accessor methods, see the SqlDataReader class and the OleDBDataReader class in the namespace library.