The DataReader Object

The DataReader Object

The DataReader provides a forward-only, read-only, nonbuffered stream over the rows created by the ExecuteReader method of the Command object. The DataReader is basically equivalent to a forward-only, read-onlyrecordset in ADO 2.X. It doesn't support scrolling or updating, and is the fastest way to access data from a data source. Because the data isn't buffered or stored in any cache, this method is a particularly good choice for retrieving large amounts of data. Calling the Read method advances the DataReader to the next record.

The fields of each row of data can be accessed by strongly typed accessors, in addition to the Fields collection. Accessing field data via the fields collection by field name and without regard to type is done similarly to accessing fields in an ADO 2.X record set, as in:

 X = MyReader("Myfield")  

Note

The DataReader doesn't have an explicit constructor you can't create a new object instance by using New(). You must call the ExecuteReader method of the Command object to instantiate a new object.

Alternatively, when you know the data type of each field, you can access the data by using type-specific methods. These methods fetch the column that indicated by a zero-based index to for example, as with

 X = MyReader.GetIn16(1)  

or

 Str = MyReader.GetString(2)  

The first approach, with its simple name access, provides for improved readability, ease of use, and compatibility with older programs. The second approach, although requiring more effort, provides greater performance because it minimizes the number of type conversions performed.

Now make one last addition to Form1 of the DataProviderObjects project:

1. Add a button immediately below the cmdExecuteScalar button.

2. In the Properties window, set the Name property of the button to cmdExecuteReader and set the Text property to ExecuteReader.

3. Add the code for this new button, as shown in Listing 4.8.

Note

In addition to showing how to program the DataReader, this example also demonstrates some other features. For instance, there is a third value in the CommandType enumeration for the Command object's CommandType property. In addition to Text and StoredProcedure, there is also TableDirect. This option indicates that the CommandText property specifies the name of a table where all the columns are returned by the command. Only the Oledb Data Provider supports this option.

Also, database views are normally dealt with as if they were tables. Therefore you can specify the name of a view, rather than a table name, when the CommandType is TableDirect.

Listing 4.8 Code to create a DataReader and retrieve field values, using a database view and the TableDirect command type

 Private Sub btnExecuteReader_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecuteReader.Click      ' Create an instance of an Connection object      Dim cnn As OleDbConnection = New OleDbConnection( _        "provider=SQLOLEDB;server=localhost;uid=sa;database=pubs")        ' Create instance of Command object      Dim cmd As OleDbCommand = New OleDbCommand()      txtResults.Clear()      ' Set command's connection and command text      cmd.Connection = cnn      cmd.CommandType = CommandType.TableDirect      cmd.CommandText = "EmployeeJobs_View"      ' Must open the Connection before calling ExecuteReader()      cnn.Open()      Dim reader As OleDbDataReader      reader = cmd.ExecuteReader()      While reader.Read()        txtResults.Text = txtResults.Text & reader("fname") & _          ControlChars.Tab & reader("lname") & _          ControlChars.Tab & ControlChars.Tab & _          reader("job_desc") & ControlChars.CrLf      End While      reader.Close()      cnn.Close() End Sub 

Note

Always remember to call Read () before trying to access data from the DataReader. Unlike a recordset in ADO 2.X, which is automatically positioned on the first row immediately after being loaded with data, the ADO.NET DataReader must be explicitly positioned to the first row by an initial call to the Read method.

You could also write the While loop by using the more efficient, strongly typed, field accessors:

 While reader.Read()    txtResults.Text = txtResults.Text & reader.GetString(1) & _     ControlChars.Tab & reader.GetString(2) & _   ControlChars.Tab & ControlChars.Tab & _        reader.GetString(0) & ControlChars.CrLf End While 

Another change that you might want to make, depending on your taste and style, is to combine the declaration of the DataReader and the execution of the ExecuteReader method into a single line. You can replace

 Dim reader As OleDbDataReader  reader = cmd.ExecuteReader() 

with

 Dim reader As OleDbDataReader = cmd.ExecuteReader()  

When you run the DataProviderObjects project and click on the ExecuteReader button, the textbox should display the data from the EmployeeJobs_view, as shown in Figure 4.4.

Figure 4.4. Results of successfully running the ExecuteReader command of Listing 4.8

Note

Always call the Close method when you have finished using the DataReader object. If you have output or return value parameters defined for the Command object that you're using, they aren't available until the DataReader is closed. Also, the DataReader's connection is kept open until you close either the DataReader or the connection.

The DataReader also provides an easy and efficient way of building a data-driven Web page, by binding it to a WebForms DataGrid. We show how to do that in Chapter 11.