Data Reader

An alternative to creating a DataSet is to create an instance of DataReader. The DataReader provides connected, forward-only, read-only access to a collection of tables, by, for example, executing a SQL statement or a stored procedure. DataReaders are lightweight objects ideally suited for filling a control or a form with data and then breaking the connection to the backend database.

Like DataAdapter, the DataReader class comes in two flavors: SqlDataReader for use with SQL Server and OleDbDataReader for use with other databases.

Table 19-5 shows the most important methods and properties of the DataReader class.

Table 19-5. The most important DataReader methods

Class member	Description
Close	Closes the DataReader.
NextResult	When reading the results of a batch SQL statement, advances to the next result set (set of records).
Read	Read a record and advance the iterator. Returns true if there are more records to read, otherwise false.

The DataReader is a very powerful object, but you won't use many of its methods or properties often. Most of the time, you'll simply use the DataReader to retrieve and iterate through the records that represent the result of your query.

Note to ADO programmers: you do not issue a MoveNext command to the DataReader. By reading a record, you automatically move to the next record. This eliminates one of the most common bugs with recordsets forgetting to move to the next record.

Do not try to create a DataReader by instantiating it with the keyword new. The constructors for the DataReader class are public internal; they are created only by the helper methods of related objects. As a client of the ADO.NET framework, you create a DataReader by calling ExecuteReader on your command object.

The next example modifies Example 19-4 (in C#) and Example 19-5 (VB.NET) to use a DataReader rather than a DataSet. The source code for C# is provided in Example 19-10, and the source for VB.NET is provided in Example 19-11, followed by a detailed analysis.

Example 19-10. Data reader in C#

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
 
namespace DataReaderCS
{
 public class Form1 : System.Windows.Forms.Form
 {
 private System.Data.SqlClient.SqlConnection connection;
 private System.Data.DataSet dataSet;
 private System.Data.SqlClient.SqlCommand command;
 private System.Data.SqlClient.SqlDataAdapter dataAdapter;
 private System.Windows.Forms.ListBox lbBugs;
 private System.ComponentModel.Container components = null;
 
 public Form1( )
 {
 InitializeComponent( );
 string connectionString = 
 "server=YourServer; uid=sa; pwd=YourPwd; database=WindForm_Bugs";
 
 // create and open the connection object
 using (connection = new System.Data.SqlClient.SqlConnection(
 connectionString))
 {
 
 connection.Open( );
 
 // create the dataset, set property
 using(dataSet = new System.Data.DataSet( ))
 {
 dataSet.CaseSensitive=true;
 
 
 // get records from the Bugs table
 string commandString = 
 "Select BugID, Description from Bugs";
 
 
 command = new System.Data.SqlClient.SqlCommand( );
 command.Connection=connection;
 command.CommandText= commandString;
 
 using (SqlDataReader dataReader = command.ExecuteReader( ))
 {
 while (dataReader.Read( ))
 {
 object bugID = dataReader["bugID"];
 object description = dataReader["description"];
 
 lbBugs.Items.Add(bugID.ToString( ) + ": " 
 + description.ToString( ));
 }
 } // end using datareader
 } // end using dataset
 } // end using connection
 }
 
 /// 

/// Clean up any resources being used. ///

protected override void Dispose( bool disposing ) { if( disposing ) { if (components != null) { components.Dispose( ); } } base.Dispose( disposing ); } #region Windows Form Designer generated code #endregion ///

/// The main entry point for the application. ///

[STAThread] static void Main( ) { Application.Run(new Form1( )); } } }

Example 19-11. Data reader in VB.NET

Public Sub New( )
 MyBase.New( )
 
 'This call is required by the Windows Form Designer.
 InitializeComponent( )
 
 Try
 Dim connectionString As String
 connectionString = _
 "Server=YourServer; uid=sa; pwd=YourPW; database=WindForm_Bugs"
 
 myConnection = _
 New System.Data.SqlClient.SqlConnection(connectionString)
 myConnection.Open( )
 
 myDataSet = New System.Data.DataSet( )
 myDataSet.CaseSensitive = True
 
 
 Dim commandString As String
 commandString = "Select BugID, Description from Bugs "
 
 myCommand = New System.Data.SqlClient.SqlCommand( )
 myCommand.Connection = myConnection
 myCommand.CommandText = commandString
 
 myDataReader = myCommand.ExecuteReader( )
 
 While myDataReader.Read
 lbBugs.Items.Add(myDataReader("bugID") & _
 ": " & myDataReader("Description"))
 End While
 Finally
 myConnection.Dispose( )
 myCommand.Dispose( )
 myDataReader.Close( )
 End Try
End Sub

Create the command object as you did in Example 19-4 and Example 19-5, but this time you do not create a DataAdapter or DataSet. Instead, you invoke ExecuteReader( ) on the command object:

myDataReader = myCommand.ExecuteReader( )

Iterate through the recordset in the DataReader within a while loop. Each time you call Read( ), a new record is provided. Access that record in a number of ways. Assign interim objects as shown in Example 19-10:

object bugID = dataReader["bugID"];
object description = dataReader["description"];
 
lbBugs.Items.Add(bugID.ToString( ) + ": " + description.ToString( ));

Alternatively, you can use unnamed temporary variables, as shown in Example 19-11:

lbBugs.Items.Add(dataReader("bugID") & ": " & dataReader("Description"))

You can also access each column by using the zero-based ordinal value of the column:

lbBugs.Items.Add(dataReader(0) & ": " & dataReader(1))

It is somewhat more efficient to use the accessors based on the native type of the underlying data (GetDateTime, GetDouble, GetInt32, and GetString):

lbBugs.Items.Add(dataReader.GetInt32(0).ToString( ) & ": " _
 & dataReader.GetString(1))

In C#, you use the using statement, and in VB.NET, you use a finally block to ensure that the DataReader is closed (and that the connection and command objects are disposed). For large-scale projects, this is vital, though to keep the code simple we may eschew this practice in some of the sample code.