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.
|
Table 19-5 shows the most important methods and properties of the DataReader class.
|
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.
|
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))
|