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.
Tip
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.
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.
Tip
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
}
/// <summary>
/// Clean up any resources being used.
/// </summary>
protected override void Dispose( bool disposing )
{
if( disposing )
{
if (components != null)
{
components.Dispose( );
}
}
base.Dispose( disposing );
}
#region Windows Form Designer generated code
#endregion
/// <summary>
/// The main entry point for the application.
/// </summary>
[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 SubCreate 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))
Tip
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.