The DataReader: Walking through the Data

As discussed earlier in this chapter, there are two common ways to fetch data from a data source: using a DataSet and using a DataReader. A DataReader 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 DataReader is also called a firehose cursor or forward, read-only cursor because it moves forward through the data. The DataReader not only allows you to move forward through each record of a database, but it also enables you to parse the data from each column.

Similar to other data components, each data provider has a DataReader class. For example, OleDbDataReader is the DataReader class for OleDb data providers. Similarly, SqlDataReader and ODBC DataReader are DataReader classes for the Sql and ODBC data providers, respectively.

Initializing a DataReader

As you saw previously, you call the ExecuteReader method of the Command object, which returns an instance of the DataReader. For example, you use the following code:

 Dim cmd As SqlCommand = New SqlCommand(SQL, conn) ' Call ExecuteReader to return a DataReader Dim reader As SqlDataReader = cmd.ExecuteReader() 

Once you're done with a DataReader, call the Close method to close a DataReader:

 reader.Close() 

Understanding DataReader Properties and Methods

Table 4-9 describes the DataReader properties, and Table 4-10 describes the DataReader methods.

Table 4-9: The DataReader Properties

PROPERTY	DESCRIPTION
Depth	Indicates the depth of nesting for a row
FieldCount	Returns number of columns in a row
IsClosed	Indicates whether a DataReader is closed
Item	Gets the value of a column in native format
RecordsAffected	Number of rows affected after a transaction

Table 4-10: The DataReader Methods

METHOD	DESCRIPTION
Close	Closes a DataReader object.
IsDBNull	Represents whether a column contains null values.
Read	Reads the next record in the DataReader.
NextResult	Advances the DataReader 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 a DataReader is initialized, 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. Listing 4-17 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, you use similar steps as in previous examples. Initially, you create a Connection object, create a Command object, call the ExecuteReader method, call the DataReader's Read method until the end of the data, and then display the data. At the end, you release the DataReader and Connection objects.

Figure 4-2 shows the output of Listing 4-17.

Figure 4-2: Output of the Customers table from the DataReader

SqlDataReader provides many Read methods, which allow you to get the value of a column as a specific type. For instance, you could rewrite this line from the previous example:

 Dim str As String = reader("CustomerID").ToString() 

as this:

 Dim str As String = reader.GetString(0) 

With the GetString method of CustomerID, you don't need to do any conversion, but you do have to know the zero-based column number of CustomerID (which, in this case, is zero).

Interpreting Batches of Queries

The DataReader also has methods that enable you to read data from a batch of SQL queries. The following is an example of a batch transaction on the Customers and Orders tables. The NextResult method allows you to obtain each query result from the batch of queries performed on both tables. 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:

 Dim cmd as SqlCommand = new SqlCommand(   "SELECT * FROM Customers;SELECT * FROM Orders",   conn) 

Now you can create the Reader through the Command object. You then use a result flag as an indicator to check if you've gone through all the results. Then you 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 4-18).

Listing 4-18: Executing Batches Using DataReader

 ' Create a Connection Object Dim ConnectionString As String = "Integrated Security=SSPI;" & _      "Initial Catalog=Northwind;Data Source=MCB;" Dim conn As SqlConnection = New SqlConnection(ConnectionString) Dim SQL As String = "SELECT * FROM Customers; SELECT * FROM Orders" ' open a connection conn.Open() Dim cmd As SqlCommand = New SqlCommand(SQL, conn) ' Call ExecuteNonQuery on the Command Object to execute insert Dim res As Integer ' Call ExecuteReader to return a DataReader Dim reader As SqlDataReader = cmd.ExecuteReader() Dim str As String = "" Dim counter As Integer Dim bNextResult As Boolean = True While bNextResult = True   While reader.Read()    str += reader.GetValue(0).ToString() + " ,"    counter = counter + 1   If counter = 10 Then     Exit While   End If End While  MessageBox.Show(str) bNextResult = reader.NextResult() End While ' release objects conn.Close() conn.Dispose() 

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. The code displays data from both tables.

Tip

One of the commonly asked questions on ADO.NET-related discussion forums and newsgroups is about when to use DataReader vs. when to use a DataSet. Here is a simple answer: You use a DataReader instead of a DataSet when you need read-only data and you don't need to alter the data. See the "Using a DataSet vs. Using a DataReader" section for a more in-depth answer.

Understanding Command Behaviors

The ExecuteReader method of a Command object such as SqlCommand has two over-loaded forms, as shown:

 Overloads Public Function ExecuteReader() As SqlDataReader Overloads Public Function ExecuteReader(CommandBehavior) As SqlDataReader 

The first form doesn't take any arguments, but the second form takes an argument of type CommandBehavior. The Command behavior can control the action and return data as a result of executing a Command.

The default value passed in the ExecuteReader is CommandBehavior.Default, which returned the same results as ExecuteReader with no arguments.

When CommandBehavior.CloseConnection is passed in ExecuteReader, the associated Connection object is closed when the DataReader is closed. In this case, you don't have to call SqlConnection.Close explicitly.

You can fetch only a single row from a database by executing a SqlCommand. The CommandBehavior.SingleRow value passed in ExecuteReader returns the single row from a table instead of the result set.

CommandBehavior.SequentialAccess provides a way for the DataReader to handle rows that contain large binary values (BLOB data) such as images or memo fields. SequentialAccess values enable DataReader to load data as a stream of bytes or characters.

By setting the CommandBehavior.KeyInfo value, the DataReader doesn't return data. It returns the table column and primary key information instead. This is useful when you're collecting metadata.

The CommandBehavior.SchemaOnly value returns column information only.