Getting a Single Horse | Designing for Scalability with Microsoft Windows DNA (DV-MPS Designing)

[Previous] [Next]

We went through a procedure similar to the one we've just outlined with the methods responsible for getting and displaying a single horse. We're sure you don't need to see the facade class code or the HorseManager class code for getting a single horse. All you'll want to see is the following data access code. Just as the GetListAsRSForNamePattern method does, this one uses a recordset as its only ADO object:

Public Function GetById (lngId As Long) As Recordset Dim rs As ADODB.Recordset, strSQL As String Const strConn = _ "Provider=SQLOLEDB.1;Server=RACING_SERVER;" & _ "User ID=sa;Initial Catalog=MSPress_ADBRacing" strSQL = _ "SELECT HorseId, HorseName, BredIn, Sex, " & _ "Birthyear, Trainer " & vbCrLf & _ "FROM Horses " & vbCrLf & _ "WHERE HorseId = " & lngId Set rs = CreateObject("ADOR.Recordset") rs.ActiveConnection = strConn rs.Source = strSQL rs.LockType = adLockBatchOptimistic rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.Open Set rs.ActiveConnection = Nothing Set GetById = rs End Function

To avoid misunderstanding, we'd just like to point out that the preceding code resides in the HorseFetcher class in the RaceDataAccess Visual Basic project.

Getting Trainers and Countries

Getting trainers and countries is just as easy. Here's the code, which lives in the TrainerFetcher and CountryFetcher classes respectively:

TrainerFetcher.GetList

Public Function GetList _ (Optional ByVal strNamePattern As String) As Recordset Dim rs As ADODB.Recordset, strSQL As String Const strConn = _ "Provider=SQLOLEDB.1;Server=RACING_SERVER;" & _ "User ID=sa;Initial Catalog=MSPress_ADBRacing" If Len(strNamePattern) = 0 Then strNamePattern = "%" strSQL = "SELECT TrainerId, " & _ "rtrim(Lastname) + ', ' + rtrim(firstname) " & _ "As TrainerName " & vbCrLf & _ "FROM Trainers " & vbCrLf & _ "WHERE Lastname LIKE '" & strNamePattern & "' " & vbCrLf & _ "ORDER BY TrainerName" Set rs = CreateObject("ADOR.Recordset") rs.ActiveConnection = strConn rs.Source = strSQL rs.LockType = adLockReadOnly rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.Open Set rs.ActiveConnection = Nothing Set GetList = rs End Function

CountryFetcher.GetList

Public Function GetList(Optional ByVal strNamePattern _ As String) As Recordset Dim rs As ADODB.Recordset, strSQL As String Const strConn = _ "Provider=SQLOLEDB.1;Server=RACING_SERVER;" _ "User ID=sa;Initial Catalog=MSPress_ADBRacing" If Len(strNamePattern) = 0 Then strNamePattern = "%" strSQL = "SELECT CountryCode, Country " & vbCrLf & _ "FROM Countries " & vbCrLf & _ "WHERE Country LIKE '" & strNamePattern & "' " & vbCrLf & _ "ORDER BY Country" Set rs = CreateObject("ADOR.Recordset") rs.ActiveConnection = strConn rs.Source = strSQL rs.LockType = adLockReadOnly rs.CursorLocation = adUseClient rs.CursorType = adOpenStatic rs.Open Set rs.ActiveConnection = Nothing Set GetList = rs End Function

Testing Again

Now we have at least a first version of every fetching operation of our small project. When we tested all this code, using the same test form as before, it worked fine again. We're ready to move on to the transactional services in the modifier class.