Anyone help to explain the Fetch method?

yipchunyu posted on Tuesday, October 10, 2006

I read the book but can't understand fully. How should I make use of the data fetch with criteria.
For example, followings are the codes inside part of the ProjectList object. How should I e.g
1. Add some parameters?
2. Multiple set of criteria e.g. search by Name and Author / Just search by ID

Any help is very much welcome. Thx in advance.

#Region " Data Access "

<Serializable()> _
Private Class Criteria
    ' no criteria - retrieve all projects
End Class

<Serializable()> _
Private Class FilteredCriteria
    Private mName As String
    Public ReadOnly Property Name() As String
      Get
        Return mName
      End Get
    End Property

    Public Sub New(ByVal name As String)
      mName = name
    End Sub
End Class

Private Overloads Sub DataPortal_Fetch(ByVal criteria As Criteria)

    ' fetch with no filter
    Fetch("")

End Sub

Private Overloads Sub DataPortal_Fetch(ByVal criteria As FilteredCriteria)

    Fetch(criteria.Name)

End Sub

Private Sub Fetch(ByVal nameFilter As String)

    RaiseListChangedEvents = False
    Using cn As New SqlConnection(Database.PTrackerConnection)
      cn.Open()
      Using cm As SqlCommand = cn.CreateCommand
        With cm
          .CommandType = CommandType.StoredProcedure
          .CommandText = "getProjects"
          Using dr As New SafeDataReader(.ExecuteReader)
            IsReadOnly = False
            While dr.Read()
              Dim info As New ProjectInfo(dr.GetGuid(0), dr.GetString(1))
              ' apply filter if necessary
              If Len(nameFilter) = 0 OrElse info.Name.IndexOf(nameFilter) = 0 Then
                Me.Add(info)
              End If
            End While
            IsReadOnly = True
          End Using
        End With
      End Using
    End Using
    RaiseListChangedEvents = True

End Sub

#End Region

RockfordLhotka replied on Tuesday, October 10, 2006

yipchunyu:
I read the book but can't understand fully. How should I make use of the data fetch with criteria.
For example, followings are the codes inside part of the ProjectList object. How should I e.g
1. Add some parameters?
2. Multiple set of criteria e.g. search by Name and Author / Just search by ID

The Criteria class defines your parameters. In ProjectList there are actually two different Criteria classes, because you can get the complete list (the Criteria with no fields) or you can get a filtered list (the Criteria with the name field for filtering).

If your object has different parameters, then you would create a Criteria class that contains those parameters.

Typically you will have one Criteria class for each different factory method, so you can clearly indicate which overload of DataPortal_Fetch() should be called by that factory method.

If you need optional parameters, then you need to make your Criteria class a little more complex. There are many ways to do this, but I prefer to keep a Boolean flag along with each criteria field, using that flag to indicate whether the field has been set.

(though you could probably use Nullable<T> to track whether each field was set - that'd be interesting to try)

This way, in your DataPortal_Fetch() method, you can easily determine which criteria fields were set, and so you can create your WHERE clause or call the right stored procedure or whatever.

JoeFallon1 replied on Tuesday, October 10, 2006

In addition to what Rocky said, I would pass the filter to the SP or SQL Statement so that the Database returns a smaller rowset over the wire to the BO. Rather than retrieve the entire DB and then filter out those records I don't want. Rocky's sample app is just showing one way to do it - it isn't necessarily the best way to do it. <g>

Joe