employee.cls

完整的三层数据库应用程序

    Else	
SaveRecord:
      adoRS("Address") = IIF(m_Address= vbNullString, vbNullString, m_Address)
      adoRS("BillingRate") = m_BillingRate
      adoRS("City") = IIF(m_City= vbNullString, vbNullString, m_City)
      adoRS("Country") = IIF(m_Country= vbNullString, vbNullString, m_Country)
      adoRS("Extension") = IIF(m_Extension= vbNullString, vbNullString, m_Extension)
      adoRS("FirstName") = IIF(m_FirstName= vbNullString, vbNullString, m_FirstName)
      adoRS("LastName") = IIF(m_LastName= vbNullString, vbNullString, m_LastName)
      adoRS("PostalCode") = IIF(m_PostalCode= vbNullString, vbNullString, m_PostalCode)
      adoRS("StateOrProvince") = IIF(m_StateOrProvince= vbNullString, vbNullString, m_StateOrProvince)
      adoRS("Title") = IIF(m_Title= vbNullString, vbNullString, m_Title)
      adoRS("WorkPhone") = IIF(m_WorkPhone= vbNullString, vbNullString, m_WorkPhone)

      .Update
      m_EmployeeID = adoRS("EmployeeID")
      m_OldEmployeeID = m_EmployeeID
    End If
  Else
    If Not m_IsDeleted Then
      .AddNew
      GoTo SaveRecord
    End If
  End If
  .Close
  End With

Skip_Save:

  Dim uTimeCard As TimeCard  
  i = 1
  Do While i <= m_TimeCards.Count
    Set uTimeCard = m_TimeCards(i)
    If uTimeCard.IsDeleted Then
      If Not uTimeCard.Save(False) Then
        GoSub Rollback_Save
        Exit Function
      End If
      m_TimeCards.Remove i
    Else
      If uTimeCard.IsDirty Then
        uTimeCard.EmployeeID = m_EmployeeID
      End If
      If Not uTimeCard.Save(False) Then
        GoSub Rollback_Save
        Exit Function
      End If  
      i = i + 1
    End If
  Loop

  If bolInTran Then 
    Conn.CommitTrans
    bolInTran = False
  End If
  Save = True
  IsDirty = False     
  IsNew = False
  RaiseEvent OnRecordSaved(Me)
Done_Save:
  Exit Function

Err_Save:
  If bolStartTran Then GoSub Rollback_Save
  ErrNum = Err.Number
  ErrMsg = Err.Description
  Call ErrHandler(ErrNum, ErrMsg,"Employee","Save")
  GoTo Done_Save
Rollback_Save:
  If bolInTran Then Conn.RollbackTrans
  Return
End Function
'******************************************************************************
'*                                                                            *
'* Name:    Delete                                                            *
'*                                                                            *
'* Purpose: mark this object and it's children as to be deleted when save     *
'*          is callled. Note it doesn't do the deletion in the database.      *
'******************************************************************************
Public Sub Delete()
  m_TimeCards.Delete

  IsDirty = True
  IsDeleted = True  
End Sub    
'******************************************************************************
'*                                                                            *
'* Name:    DeleteList                                                        *
'*                                                                            *
'* Purpose: Delete record in database based on a where SQL clause.            *
'*          Note it doesn't delete children records.                          *   
'*                                                                            *
'******************************************************************************
Public Function DeleteList(ByVal strDeleteSQL As String) As Boolean
  On Error GoTo Err_DeleteList
  Dim strSQL As String

  strSQL = "DELETE * FROM Employees " & strDeleteSQL 
  Conn.Execute strSQL
  DeleteList = True
  
  Exit Function
Err_DeleteList:
  ErrNum = Err.Number
  ErrMsg = Err.Description
  Call ErrHandler(ErrNum, ErrMsg,"Employee","DeleteList")
End Function
    
'******************************************************************************
'*                                                                            *
'* Name:    Load                                                              *
'*                                                                            *
'* Purpose: Get the specified record. If found, fill this object with correct *
'*          record data. GetChildren is optional so if true get children      *
'*          record as well.                                                   *
'*                                                                            *
'* Returns: Boolean - True (record found); False (otherwise).                 *
'*                                                                            *
'******************************************************************************
Public Function Load(ByVal vEmployeeID As Long, Optional ByVal GetChildren As Boolean = True) As Boolean
  On Error GoTo Err_Load

  Dim adoRS As New ADODB.Recordset
  Dim strSQL As String

  Clear

  strSQL = "Select a.Address,a.BillingRate,a.City,a.Country,a.EmployeeID,a.Extension,a.FirstName,a.LastName,a.PostalCode,a.StateOrProvince,a.Title,a.WorkPhone From Employees a WHERE a.EmployeeID=" & vEmployeeID & ""

  adoRS.Open strSQL, Conn, adOpenForwardOnly, adLockReadOnly
    
  With adoRS
    If Not .EOF Then
      m_Address= IIF(IsNull(adoRS("Address")), "", adoRS("Address"))
      m_BillingRate= IIF(IsNull(adoRS("BillingRate")), 0, adoRS("BillingRate"))
      m_City= IIF(IsNull(adoRS("City")), "", adoRS("City"))
      m_Country= IIF(IsNull(adoRS("Country")), "", adoRS("Country"))
      m_EmployeeID= IIF(IsNull(adoRS("EmployeeID")), 0, adoRS("EmployeeID"))
      m_Extension= IIF(IsNull(adoRS("Extension")), "", adoRS("Extension"))
      m_FirstName= IIF(IsNull(adoRS("FirstName")), "", adoRS("FirstName"))
      m_LastName= IIF(IsNull(adoRS("LastName")), "", adoRS("LastName"))
      m_PostalCode= IIF(IsNull(adoRS("PostalCode")), "", adoRS("PostalCode"))
      m_StateOrProvince= IIF(IsNull(adoRS("StateOrProvince")), "", adoRS("StateOrProvince"))
      m_Title= IIF(IsNull(adoRS("Title")), "", adoRS("Title"))
      m_WorkPhone= IIF(IsNull(adoRS("WorkPhone")), "", adoRS("WorkPhone"))
      m_OldEmployeeID = m_EmployeeID

	  ReSetBrokenRule False 
    Else
      Load = False
      .Close
      Exit Function
    End If
    .Close
  End With

  If GetChildren Then m_TimeCards.LoadRelated m_EmployeeID

  Load = True
  IsDirty = False  
  IsNew = False
  RaiseEvent OnRecordLoad(Me)
  Exit Function

Err_Load:
  ErrNum = Err.Number
  ErrMsg = Err.Description
  Call ErrHandler(ErrNum, ErrMsg,"Employee","Load")
End Function


'******************************************************************************
'*                                                                            *
'* Name:    LoadList                                                          *
'*                                                                            *
'* Purpose: Load records based on a where SQL clause.                         *
'*          You can include orderby clause in SQLWhereClause to sort data.    *  
'*                                                                            *
'* Return: a Employees object                  *
'******************************************************************************
Function LoadList(Optional ByVal SQLWhereClause As String = vbNullString , Optional GetChildren As Boolean = False) As Employees
  On Error GoTo Err_LoadList
  Dim adoRS As New ADODB.Recordset
  Dim strSQL As String
  Dim uEmployee As Employee
  Dim uEmployees As New Employees

  strSQL = "Select a.Address,a.BillingRate,a.City,a.Country,a.EmployeeID,a.Extension,a.FirstName,a.LastName,a.PostalCode,a.StateOrProvince,a.Title,a.WorkPhone From Employees a" & SQLWhereClause

  adoRS.Open strSQL, Conn, adOpenForwardOnly, adLockReadOnly
    
  With adoRS
    Do While Not .EOF
      Set uEmployee = Nothing
      Set uEmployee = New Employee
      uEmployee.Address= IIF(IsNull(adoRS("Address")), "", adoRS("Address"))
      uEmployee.BillingRate= IIF(IsNull(adoRS("BillingRate")), 0, adoRS("BillingRate"))
      uEmployee.City= IIF(IsNull(adoRS("City")), "", adoRS("City"))
      uEmployee.Country= IIF(IsNull(adoRS("Country")), "", adoRS("Country"))
      uEmployee.EmployeeID= IIF(IsNull(adoRS("EmployeeID")), 0, adoRS("EmployeeID"))
      uEmployee.Extension= IIF(IsNull(adoRS("Extension")), "", adoRS("Extension"))
      uEmployee.FirstName= IIF(IsNull(adoRS("FirstName")), "", adoRS("FirstName"))
      uEmployee.LastName= IIF(IsNull(adoRS("LastName")), "", adoRS("LastName"))
      uEmployee.PostalCode= IIF(IsNull(adoRS("PostalCode")), "", adoRS("PostalCode"))
      uEmployee.StateOrProvince= IIF(IsNull(adoRS("StateOrProvince")), "", adoRS("StateOrProvince"))
      uEmployee.Title= IIF(IsNull(adoRS("Title")), "", adoRS("Title"))
      uEmployee.WorkPhone= IIF(IsNull(adoRS("WorkPhone")), "", adoRS("WorkPhone"))
      uEmployee.OldEmployeeID = uEmployee.EmployeeID
	
      uEmployee.IsDirty = False    
      uEmployee.IsNew = False 
      uEmployee.ReSetBrokenRule False
      uEmployees.AddExisting uEmployee, ":" & uEmployee.EmployeeID 
      If GetChildren Then uEmployee.TimeCards.LoadRelated uEmployee.EmployeeID

      .MoveNext
    Loop
    .Close
  End With

  Set LoadList = uEmployees
  Exit Function

Err_LoadList:
  ErrNum = Err.Number
  ErrMsg = Err.Description
  Call ErrHandler(ErrNum, ErrMsg,"Employee","LoadList")
End Function
 
'******************************************************************************
'*                                                                            *
'* Name:    LoadByFirstName                                   *
'*                                                                            *
'* Purpose: Load records based on FirstName                         *
'*          Optionally you can include orderby clause to sort data.           *  
'*                                                                            *
'* Return: a Employees object                  *
'******************************************************************************
Function LoadByFirstName(ByVal vFirstName As String , Optional ByVal OrderByClause As String = vbNullString , Optional GetChildren As Boolean = False) As Employees
  On Error GoTo Err_LoadByFirstName
  Dim adoRS As New ADODB.Recordset
  Dim strSQL As String
  Dim uEmployee As Employee
  Dim uEmployees As New Employees

  strSQL = "Select a.Address,a.BillingRate,a.City,a.Country,a.EmployeeID,a.Extension,a.FirstName,a.LastName,a.PostalCode,a.StateOrProvince,a.Title,a.WorkPhone From Employees a Where a.FirstName Like '" & vFirstName & "'" & OrderByClause

  adoRS.Open strSQL, Conn, adOpenForwardOnly, adLockReadOnly
    
  With adoRS
    Do While Not .EOF
      Set uEmployee = Nothing
      Set uEmployee = New Employee
      uEmployee.Address= IIF(IsNull(adoRS("Address")), "", adoRS("Address"))
      uEmployee.BillingRate= IIF(IsNull(adoRS("BillingRate")), 0, adoRS("BillingRate"))
      uEmployee.City= IIF(IsNull(adoRS("City")), "", adoRS("City"))
      uEmployee.Country= IIF(IsNull(adoRS("Country")), "", adoRS("Country"))
      uEmployee.EmployeeID= IIF(IsNull(adoRS("EmployeeID")), 0, adoRS("EmployeeID"))
      uEmployee.Extension= IIF(IsNull(adoRS("Extension")), "", adoRS("Extension"))
      uEmployee.FirstName= IIF(IsNull(adoRS("FirstName")), "", adoRS("FirstName"))
      uEmployee.LastName= IIF(IsNull(adoRS("LastName")), "", adoRS("LastName"))
      uEmployee.PostalCode= IIF(IsNull(adoRS("PostalCode")), "", adoRS("PostalCode"))
      uEmployee.StateOrProvince= IIF(IsNull(adoRS("StateOrProvince")), "", adoRS("StateOrProvince"))
      uEmployee.Title= IIF(IsNull(adoRS("Title")), "", adoRS("Title"))
      uEmployee.WorkPhone= IIF(IsNull(adoRS("WorkPhone")), "", adoRS("WorkPhone"))
      uEmployee.OldEmployeeID = uEmployee.EmployeeID
	
      uEmployee.IsDirty = False 
      uEmployee.IsNew = False 
      uEmployee.ReSetBrokenRule False
      uEmployees.AddExisting uEmployee, ":" & uEmployee.EmployeeID 
      If GetChildren Then uEmployee.TimeCards.LoadRelated uEmployee.EmployeeID

      .MoveNext
    Loop
    .Close
  End With

  Set LoadByFirstName = uEmployees
  Exit Function

Err_LoadByFirstName:
  ErrNum = Err.Number
  ErrMsg = Err.Description
  Call ErrHandler(ErrNum, ErrMsg,"Employee","LoadByFirstName")
End Function
	 
'******************************************************************************
'*                                                                            *
'* Name:    LoadByLastName                                   *
'*                                                                            *
'* Purpose: Load records based on LastName                         *
'*          Optionally you can include orderby clause to sort data.           *  
'*                                                                            *
'* Return: a Employees object                  *
'******************************************************************************
Function LoadByLastName(ByVal vLastName As String , Optional ByVal OrderByClause As String = vbNullString , Optional GetChildren As Boolean = False) As Employees
  On Error GoTo Err_LoadByLastName
  Dim adoRS As New ADODB.Recordset
  Dim strSQL As String
  Dim uEmployee As Employee
  Dim uEmployees As New Employees

  strSQL = "Select a.Address,a.BillingRate,a.City,a.Country,a.EmployeeID,a.Extension,a.FirstName,a.LastName,a.PostalCode,a.StateOrProvince,a.Title,a.WorkPhone From Employees a Where a.LastName Like '" & vLastName & "'" & OrderByClause

  adoRS.Open strSQL, Conn, adOpenForwardOnly, adLockReadOnly