Walkthrough: Using a DbDataReader to Retrieve Multiple Rows

This walkthrough demonstrates how to use a DbDataReader to retrieve multiple rows of data.

To reproduce the demonstration

1. Configure the database. For the necessary steps, see "QuickStart Configuration" in Data Access QuickStarts.

2. Create the database by adding the following code. The factory creates a Database object that has the default configuration.

   db = DatabaseFactory.CreateDatabase();
   

   'Usage
   db = DatabaseFactory.CreateDatabase()
   

3. Create the command by adding the following code. It creates a DbCommand used with a SQL String.

   string sqlCommand = "Select CustomerID, Name, Address, City, Country, PostalCode " +
   "From Customers";
   DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
   

   'Usage
   Dim sqlCommand As String = "Select CustomerID, Name, Address, City, Country, PostalCode " & _
               "From Customers"
   Dim dbCommand As DbCommand = db.GetSqlStringCommand(sqlCommand)
   

4. Call ExecuteReader by adding the following code, which passes the DbCommand for the SQL command text used to populate the DbDataReader.

   using (IDataReader dataReader = db.ExecuteReader(dbCommand))
   {
     // Processing code 
   }
   

   'Usage
   Using dataReader As IDataReader = db.ExecuteReader(dbCommand)
     ' Processing code
   End Using
   

5. Process the results by adding the following code inside the using statement, which loops through the DbDataReader and places the results into a string.

   StringBuilder readerData = new StringBuilder();
   
   while (dataReader.Read())
   {
     // Get the value of the Name column in the DbDataReader.
     readerData.Append(dataReader["Name"]);
     readerData.Append(Environment.NewLine);
   }
   

   'Usage
   Dim readerData As StringBuilder = New StringBuilder()
   
   While (dataReader.Read())
     ' Get the value of the Name column in the DbDataReader.
     readerData.Append(dataReader("Name"))
     readerData.Append(Environment.NewLine)
   End While
   

6. If the DbDataReader was not scoped with a using statement, add the following code to explicitly close the DbDataReader.

   // Close the reader, which will cause the connection to be closed as well.
   if (dataReader != null)
        dataReader.Close();
   

   'Usage
   ' Close the reader, which will cause the connection to be closed as well.
   If (Not dataReader Is Nothing) Then
        dataReader.Close()
   End If