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



Closes the DataReader.


When reading the results of a batch SQL statement, advances to the next result set (set of records).


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(
 connection.Open( );
 // create the dataset, set property
 using(dataSet = new System.Data.DataSet( ))
 // get records from the Bugs table
 string commandString = 
 "Select BugID, Description from Bugs";
 command = new System.Data.SqlClient.SqlCommand( );
 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( )
 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
 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.

Programming. NET Windows Applications
Programming .Net Windows Applications
ISBN: 0596003218
EAN: 2147483647
Year: 2003
Pages: 148
Simiral book on Amazon © 2008-2017.
If you may any questions please contact us: