DataReader in ADO.NET

An Easy Walk through the Data

There are two common objects in ADO.NET to read data, DataSet and DataReader. C# DataSet and C# DataReader classes represent these objects. In this article, we'll learn about ADO.NET DataReader and how to use a DataReader in C#.
A data reader provides an easy way for the programmer to read data from a database as if it were coming from a stream. The DataReader is the solution for forward streaming data through ADO.NET. The data reader is also called a firehose cursor or forward read-only cursor because it moves forward through the data. The data reader not only allows you to move forward through each record of database, but it also enables you to parse the data from each column. The DataReader class represents a data reader in ADO.NET.
Similar to other ADO.NET objects, each data provider has a data reader class for example; OleDbDataReader is the data reader class for OleDb data providers. Similarly, SqlDataReader and ODBC DataReader are data reader classes for SQL and ODBC data providers, respectively.
The IDataReader interface defines the functionally of a data reader and works as the base class for all data provider-specific data reader classes such as OleDataReader. SqlDataReader, and OdbcDataReader. Figure 5-36 shows some of the classes that implement IDbDataReader.
Figure 5-36. Data Provider-specific classes implementing IdbDataReader

Initializing DataReader

As you've seen in the previous examples, you call the ExecuteReader method of the Command object, which returns an instance of the DataReader. For example, use the following line of code:
  1. SqlCommand cmd = new SqlCommand(SQL, conn);  
  2. // Call ExecuteReader to return a DataReader  
  3. SqlDataReader reader = cmd.ExecuteReader();  
Once you're done with the data reader, call the Close method to close a data reader:
  1. reader.Close();  

DataReader Properties and Methods

Table 5-26 describes DataReader properties, and Table 5-27 describes DataReader methods.
Table 5-26. The DataReader properties
Depth Indicates the depth of nesting for row
FieldCount Returns number of columns in a row
IsClosed Indicates whether a data reader is closed
Item Gets the value of a column in native format
RecordsAffected Number of row affected after a transaction

Table 5-27. The DataReader methods

Close Closes a DataRaeder object.
Read Reads next record in the data reader.
NextResult Advances the data reader to the next result during batch transactions.
Getxxx There are dozens of Getxxx methods. These methods read a specific data type value from a column. For example. GetChar will return a column value as a character and GetString as a string.

Reading with the DataReader

Once the OleDbDataReader is initialize, you can utilize its various methods to read your data records. Foremost, you can use the Read method, which, when called repeatedly, continues to read each row of data into the DataReader object. The DataReader also provides a simple indexer that enables you to pull each column of data from the row. Below is an example of using the DataReader in the Northwind database for the Customers table and displaying data on the console.
As you can see from listing 5-39, I've used similar steps as I've been using in previous examples. I created a connection object, created a command object, called the ExecuteReader method, called the DataReader's Read method until the end of the data, and then displayed the data. At the end, I released the data reader and connection objects.
Listing 5-39. DataReader reads data from a SQL server database
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Text;  
  4. using System.Data.SqlClient;  
  6. namespace CommandTypeEnumeration  
  7. {  
  8.     class Program  
  9.     {  
  10.         static void Main(string[] args)  
  11.         {  
  13.             // Create a connection string  
  14.             string ConnectionString = "Integrated Security = SSPI; " +  
  15.             "Initial Catalog= Northwind; " + " Data source = localhost; ";  
  16.             string SQL = "SELECT * FROM Customers";  
  18.             // create a connection object  
  19.             SqlConnection conn = new SqlConnection(ConnectionString);  
  21.             // Create a command object  
  22.             SqlCommand cmd = new SqlCommand(SQL, conn);  
  23.             conn.Open();  
  25.             // Call ExecuteReader to return a DataReader  
  26.             SqlDataReader reader = cmd.ExecuteReader();  
  27.             Console.WriteLine("customer ID, Contact Name, " + "Contact Title, Address ");  
  28.             Console.WriteLine("=============================");  
  30.             while (reader.Read())  
  31.             {  
  32.                 Console.Write(reader["CustomerID"].ToString() + ", ");  
  33.                 Console.Write(reader["ContactName"].ToString() + ", ");  
  34.                 Console.Write(reader["ContactTitle"].ToString() + ", ");  
  35.                 Console.WriteLine(reader["Address"].ToString() + ", ");  
  36.             }  
  38.             //Release resources  
  39.             reader.Close();  
  40.             conn.Close();  
  41.         }  
  42.     }  
  43. }  
Figure 5-37 shows the output of Listing 5-39.
Figure 5-37. Output of the Customers table from the DataReader
Other methods in the Reader allow you to get the value of a column as a specific type. For instance, this line from the previous example:
  1. string str = reader["CustomerID"].ToString();  
Could be rewritten as this:
  1. string str = reader.GetString(0);  
With the GetString method of the CustomerID, you don't need to do any conversion, but you do have known the zero-based column number of the CustomerID (Which, in this case, is zero).
Interpreting Batched of Queries
DataReader also has methods that enable you read data from a batch of SQL queries. Below is an example of a batch transaction on the Customers and Orders table. The NextResult method allows you to obtain each query result from the batch of queries performed on both table. In this example, after creating a connection object, you set up your Command object to do a batch query on the Customers and the Orders tables:
  1. SqlCommand cmd = new SqlCommand("SELECT * FROM Customers; SELECT * FROM Orders", conn);  
Now you can create the Reader through the Command object. You'll then use a result flag as an indicator to check if you've gone through all the results. Then you'll loop through each stream of results and read the data into a string until it reads 10 records. After that, you show results in a message box (see listing 5-40).
After that you call the NextResult method, which gets the next query result in the batch. The result is processed again in the Read method loop.
Listing 5-40. Executing batches using DataReader
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Text;  
  4. using System.Data.SqlClient;  
  5. using System.Windows.Forms;  
  7. namespace CommandTypeEnumeration  
  8. {  
  10.     class Program  
  11.     {  
  12.         static void Main(string[] args)  
  13.         {  
  14.             // Create a connection string  
  15.             string ConnectionString = "Integrated Security = SSPI; " +  
  16.             "Initial Catalog= Northwind; " + "Data Source = localhost; ";  
  17.             string SQL = " SELECT * FROM Customers; SELECT * FROM Orders";  
  19.             // Create a Conenction object  
  20.             SqlConnection conn = new SqlConnection(ConnectionString);  
  22.             // Create a command object  
  23.             SqlCommand cmd = new SqlCommand(SQL, conn);  
  24.             conn.Open();  
  26.             // Call ExecuteReader to return a DataReader  
  27.             SqlDataReader reader = cmd.ExecuteReader();  
  28.             int counter = 0;  
  29.             string str = " ";  
  30.             bool bNextResult = true;  
  32.             while (bNextResult == true)  
  33.             {  
  34.                 while (reader.Read())  
  35.                 {  
  36.                     str += reader.GetValue(0).ToString() + "\n";  
  37.                     counter++;  
  38.                     if (counter == 10)  
  39.                         break;  
  40.                 }  
  42.                 MessageBox.Show(str);  
  43.                 bNextResult = reader.NextResult();  
  44.             }  
  46.             // Release resources  
  47.             reader.Close();  
  48.             conn.Close();  
  49.         }  
  50.     }  
  51. }  
Figure 5-38 shows the two Message Boxes produced from this routine.
Figure 5-38. Output for the batch read of the Customers and Orders table


Hope this article would have helped you in understanding DataReader in ADO.NET. See my other articles on the website on ADO.NET.