Listing 2 shows the Person class code. The m_PersonsLoaded variable holds a collection of all the Person objects that are currently loaded. This variable is shared so all Person objects use the same m_PersonsLoaded variable, and all have access to the same collection. The variable is private, so it is hidden from the rest of the application.
When a Person object is destroyed, the system calls its Finalize method. If the program uses more than one reference to the same object, this occurs only after the last reference to the object is deleted.
If the object's Index value is not 0, the Person object has been saved to the database and added to the m_PersonsLoaded collection. In that case, Finalize removes the object from the collection so future requests for this Person will go to the database.
The CombinedName property simply returns the Person object's first and last names concatenated together as in "Stephens, Rod". The database's People table contains a CombinedName field to make searching by the combined name faster.
SavePerson saves a Person's data into the database. The routine begins by using an XmlSerialization object to create the Person's serialization. Then it examines the Person's Index value. If Index = 0, the object has just been created and has not yet been saved into the database. In that case, the program executes an INSERT statement to create the new database record. It fetches the newly created Index value, saves it in the Person's Index variable, and adds the object to the m_PersonsLoaded collection.
If Index is not 0, the object already exists in the database. In that case, SavePerson updates its database entry using an UPDATE statement. It then searches the m_PersonsLoaded collection to find its own entry. It removes itself, and adds itself back into the collection. That updates the CombinedName value used as the collection's key in case the FirstName or LastName have changed.
The GetPerson function fetches a Person object using its combined name. GetPerson first checks the m_PersonsLoaded collection to see if the object is already loaded. If the object is in the collection, GetPerson returns a reference to the existing object.
If the object is not already in the m_PersonsLoaded collection, GetPerson fetches it from the database much as subroutine FetchBouncers does in program BouncingObjects.
Listing 2. The Person class uses this code to save and restore objects in the database.
Public Class Person ' Database identifier. Public Index As Long ' Public variables. Public LastName As String Public FirstName As String Public EmailAddress As String Public Salary As Single ' A list of people currently loaded. Private Shared m_PersonsLoaded As New Collection() ' Before the object is destroyed, remove it ' from the m_PersonsLoaded list. Protected Overrides Sub Finalize() If Index > 0 Then m_PersonsLoaded.Remove(CombinedName) End Sub ' Return the combined name as in "Stephens, Rod". Public ReadOnly Property CombinedName() As String Get CombinedName = LastName & ", " & FirstName End Get End Property ' Save the Person into the database. Public Sub SavePerson() Dim xml_serializer As XmlSerializer Dim string_writer As New StringWriter() Dim serialization As String Dim i As Integer ' Get the Person's serialization. xml_serializer = New XmlSerializer(GetType(Person)) xml_serializer.Serialize(string_writer, Me) serialization = string_writer.ToString ' Replace ' with '' for SQL. serialization = Replace(serialization, "'", "''") ' See if this is a new or existing Person. If Index = 0 Then ' This is a new Person. ' Add it to the database. ExecuteNonQuery( _ "INSERT INTO People " & _ "(Serialization, CombinedName) " & _ "VALUES ('" & _ serialization & "', '" & _ Replace(CombinedName, "'", "''") & "')") ' Get the new Index value. Dim data_set As DataSet data_set = ExecuteQuery("SELECT MAX(Index) FROM People") Index = data_set.Tables(0).Rows(0).Item(0) ' Add this new item to m_PersonsLoaded. m_PersonsLoaded.Add(Me, CombinedName) Else ' This is an existing Person. ' Update its database entry. ExecuteNonQuery( _ "UPDATE People SET " & _ "Serialization='" & serialization & "', " & _ "CombinedName='" & Replace(CombinedName, "'", "''") & "' " & _ "WHERE Index=" & Index) ' Find ourself in m_PersonsLoaded. For i = 1 To m_PersonsLoaded.Count If m_PersonsLoaded(i) Is Me Then ' This is us. Remove ourselves and readd ' with our new CombinedName. m_PersonsLoaded.Remove(i) m_PersonsLoaded.Add(Me, CombinedName) End If Next i End If End Sub ' ***************** ' Object DB methods ' ***************** ' Return the Person with this combined name. Public Shared Function GetPerson(ByVal combined_name As String) As Person Dim new_person As Person ' See if the Person is already loaded. Try new_person = m_PersonsLoaded.Item(combined_name) Catch arg_exc As ArgumentException ' The Person is not in the collection. Catch exc As Exception ' Unexpected error. MsgBox(exc.Message) End Try ' Return this Person. If Not (new_person Is Nothing) Then Return new_person ' Load the Person from the database. Dim data_set As DataSet combined_name = Replace(combined_name, "'", "''") data_set = ExecuteQuery( _ "SELECT Index, Serialization " & _ "FROM People " & _ "WHERE CombinedName='" & combined_name & "'") ' If we got nothing, do no more. If data_set.Tables(0).Rows.Count = 0 Then Return Nothing ' Deserialize the Person. Dim serialization As String Dim string_reader As StringReader Dim xml_serializer As New XmlSerializer(GetType(Person)) serialization = data_set.Tables(0).Rows(0).Item(1) string_reader = New StringReader(serialization) new_person = xml_serializer.Deserialize(string_reader) ' Set the Index. new_person.Index = data_set.Tables(0).Rows(0).Item(0) m_PersonsLoaded.Add(new_person, combined_name) Return new_person End Function End Class
Download the Personnel programs source code: