Using a Data Reader

As discussed in the In Depth section of this chapter, you use data readers to get low-level access to the data in a database. Data readers let you read record after record (going forward in the database only) and retrieve individual values in each record. To see how to use data readers, take a look at the DataReader example on the CD-ROM. When the user clicks the button in that example, the program uses a data reader to read the data in the authors table in the pubs database, as you see in Figure 22.5.

Figure 22.5: Using a data reader to read from a database.

There are no data reader controls in the Visual Basic toolbox—you have to create them in code. To create a data reader, you can use the ExecuteReader method of a command object. Here's how I create a data reader in the DataReader example, and load the authors table into it:

Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
    Dim Connection1String As New String(_
        "Provider=SQLOLEDB;Data Source=;User ID=sa;Initial Catalog=pubs;")
    Dim Connection1 As New OleDbConnection(Connection1String)

    Dim Command1 As New OleDbCommand("SELECT * FROM authors", Connection1)

    Connection1.Open()

    Dim Reader1 As OleDbDataReader = _
        Command1.ExecuteReader(CommandBehavior.CloseConnection)
        ⋮

Note in Figure 22.5 that the program gives the names of each field in headers at the top of the display. To get the names of the columns in this table, I can get the XML schema of the table using the data reader GetSchemaTable method, and I retrieve the names of the columns, like this:

Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
    Dim LoopIndex As Integer
    Dim Connection1String As New String(_
        "Provider=SQLOLEDB;Data Source=;User ID=sa;Initial Catalog=pubs;")
        ⋮
    Dim schemaTable As DataTable = Reader1.GetSchemaTable()

    For LoopIndex = 0 To schemaTable.Rows.Count - 1
        TextBox1.Text &= schemaTable.Rows(LoopIndex).Item(0).ToString() & _
            ControlChars.Tab & ControlChars.Tab
        If LoopIndex = 0 Then 'Handle wide au_id field
            TextBox1.Text &= ControlChars.Tab
        End If
    Next

    TextBox1.Text &= ControlChars.CrLf

    For LoopIndex = 0 To (schemaTable.Rows.Count - 1)
        TextBox1.Text &= "-------" & ControlChars.Tab & _
            ControlChars.Tab
        If LoopIndex = 0 Then 'Handle wide au_id field
            TextBox1.Text &= ControlChars.Tab
        End If
    Next LoopIndex

    TextBox1.Text &= ControlChars.CrLf
        ⋮

To actually read a row of data from a data reader, you use the Read method. After the reader has read a row, you use Get methods to read the data in the fields in the row, one after the other (see Table 22.27 for these methods). For example, if you know the field holds string data, you use the GetString method. If you know it holds a Double value, use GetDouble. You can determine the data type of a field with the table's XML schema, as I do in this example, or with the data reader GetFieldType. Here's how I read in the data in the authors table, row by row; mostly, the fields in that table are of type String, but there is one field (the contract field) that is of type Boolean:

Private Sub Button1_Click(ByVal sender As System.Object, _
        ByVal e As System.EventArgs) Handles Button1.Click
    Dim LoopIndex As Integer
    Dim Connection1String As New String(_
        "Provider=SQLOLEDB;Data Source=;User ID=sa;Initial Catalog=pubs;")
    Dim Connection1 As New OleDbConnection(Connection1String)
        ⋮
    TextBox1.Text &= ControlChars.CrLf

    While Reader1.Read()
        For LoopIndex = 0 To schemaTable.Rows.Count - 1
            If schemaTable.Rows(LoopIndex).Item(5).ToString() = _
                "System.String" Then
                TextBox1.Text &= Reader1.GetString(LoopIndex) & _
                ControlChars.Tab & ControlChars.Tab
            End If
            If schemaTable.Rows(LoopIndex).Item(5).ToString() = _
                "System.Boolean" Then
                TextBox1.Text &= Reader1.GetBoolean(LoopIndex).ToString() _
                    & ControlChars.Tab & ControlChars.Tab
            End If
        Next LoopIndex
        TextBox1.Text &= ControlChars.CrLf
    End While

    Reader1.Close()
    Connection1.Close()
End Sub

And that's it—now we've used a data reader to read data.