Public Class Books
  Implements IDisposable

  'Private members for internal usage
  Private SqlCommand As SqlClient.SqlDataAdapter
  Private QueryData As BookData
  Private CurrentRow As Int32

  'Public New() interface for constructing the object
  Public Sub New()
    strTitle = Nothing
    strAuthor = Nothing
    dPrice = 0
    CurrentRow = -1

    SqlCommand = New SqlClient.SqlDataAdapter()
    SqlCommand.SelectCommand = New SqlClient.SqlCommand()
    SqlCommand.TableMappings.Add("Table", BookData.BOOK_TABLE)
  End Sub

  'Public dispose interface
  Public Overridable Sub Dispose() Implements IDisposable.Dispose
    Dispose(True)
    GC.SuppressFinalize(True)
  End Sub

  'Public Query method provides the user of the object a method to
  'start the query of the books in the database
  Public Sub Query()
    If Not QueryData Is Nothing Then
      QueryData.Dispose()
    End If

    CurrentRow = 0
    QueryData = New BookData()

    With SqlCommand
      Try
        With .SelectCommand
          .CommandType = CommandType.Text
          .CommandText = "SELECT title, au_lname, price FROM " & _
                  BookData.BOOK_TABLE
          .Connection = New System.Data.SqlClient.SqlConnection( _
                  "data source=(local)\NetSDK;initial catalog=pubs;" & _
                  "persist security info=False;user id=sa;")
        End With
        .Fill(QueryData)
      Finally
        If Not .SelectCommand Is Nothing Then
          If Not .SelectCommand.Connection Is Nothing Then
            .SelectCommand.Connection.Dispose()
          End If
          .SelectCommand.Dispose()
        End If
        .Dispose()
      End Try
    End With
  End Sub

  'The public Fetch method provides the object user a method
  'of fetching each record that was returned from the query
  'The results are loaded into members for access via public properties
  Public Function Fetch() As Boolean
    Fetch = False

    If CurrentRow < 0 Then
      Exit Function
    End If

    With QueryData.Tables(BookData.BOOK_TABLE).Rows

      'If there are no more records, exit
      If CurrentRow >= .Count() Then
        CurrentRow = -1
        Exit Function
      End If

      Try
        strTitle = CType(.Item(CurrentRow).Item(BookData.TITLE_COLUMN), _
            String)
        strAuthor = CType(.Item(CurrentRow).Item(BookData.AUTHOR_COLUMN), _
            String)
        dPrice = CType(.Item(CurrentRow).Item(BookData.PRICE_COLUMN), _
            Double)
      Catch
        'Do nothing...
      Finally
        Fetch = True
        CurrentRow += 1
      End Try

    End With
  End Function

#Region " Property Definitions "

  Private strTitle As String
  Private strAuthor As String
  Private dPrice As Double

  'The public properties provide read only access to the
  'data returned from the query after each call to Fetch()
  Public ReadOnly Property Title() As String
    Get
      Title = strTitle
    End Get
  End Property

  Public ReadOnly Property Author() As String
    Get
      Author = strAuthor
    End Get
  End Property

  Public ReadOnly Property Price() As String
    Get
      Price = dPrice.ToString("c")
    End Get
  End Property

#End Region

End Class