warning.vb

<Visual Basic 数据库开发实例精粹（第二版）>一书首先介绍了Visual Basic(简称VB)开发的技巧和重点技术

Public Class Warning

#Region "字段常量字义"
  Protected Const IDField As String = "WarnId" '提醒信息ID
  Protected Const TypeIdField As String = "TypeId" '提醒类型ID
  Protected Const TypeNameField As String = "TypeName" '提醒类型名称
  Protected Const ClientIdField As String = "ClientId" '提醒客户ID
  Protected Const ClientNameField As String = "ClientName" '提醒客户名称
  Protected Const MsgField As String = "Msg" '提醒显示消息
  Protected Const ShowDateField As String = "ShowDate" '提醒显示时间
#End Region

  '''''''''''''''''''''''''''''''''''''''''''''''''''''''
  '以下为方法

  '新增提醒信息，并返回操作的结果
  Public Function AddNew(ByRef dmWarn As WarningDataModel) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView

    '构造SQL语句，注意需调用SafeDbString去除字符串中的单引号
    strSQL = "EXEC AddWarning " & dmWarn.TypeID & "," _
              & dmWarn.ClientID & "," _
              & "'" & dbObj.SafeDbString(dmWarn.ClientName) & "'," _
              & "'" & dbObj.SafeDbString(dmWarn.Msg) & "'," _
              & "'" & dmWarn.ShowDate & "'"

    ErrMsg = dbObj.GetDataView(strSQL, dv)
    If dv.Count = 0 Then
      Return ErrMsg
    End If

    dmWarn.ID = dv(0)(Me.IDField)

    Return ErrMsg

  End Function

  '修改提醒信息，返回操作结果
  Public Function Update(ByVal dmWarn As WarningDataModel) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String

    '通过ID判断是否存在该记录，即该记录是否被其它客户端删除
    '如果不存在该记录，则返回相应的操作结果给调用者
    If Not Me.IsExist(dmWarn.ID) Then
      ErrMsg = "对象不存在"
      Return ErrMsg
    End If

    '构造SQL语句，注意需调用SafeDbString去除字符串中的单引号
    strSQL = "EXEC UpdateWarning " & dmWarn.ID & "," _
              & dmWarn.TypeID & "," _
              & dmWarn.ClientID & "," _
              & "'" & dbObj.SafeDbString(dmWarn.ClientName) & "'," _
              & "'" & dbObj.SafeDbString(dmWarn.Msg) & "'," _
              & "'" & dmWarn.ShowDate & "'"

    ErrMsg = dbObj.RunSql(strSQL)

    Return ErrMsg

  End Function

  '修改提醒信息，返回操作结果
  Public Function UpdateBrithdayWarn(ByVal dmClient As ClientDataModel, _
                               Optional ByVal bRemove As Boolean = False) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim bExist As Boolean = False

    If bRemove Then
      ErrMsg = Me.DeleteBirthdayWarning(dmClient.ID)
    Else
      '判断是否已经有生日提醒记录
      bExist = Me.IsBirthdayWarningExist(dmClient.ID)
      Dim dmWarn As New WarningDataModel

      '此步是为了获取WarnId
      If bExist Then
        ErrMsg = Me.GetBrithdayWarningInfo(dmWarn, dmClient.ID)
      End If
      '初始化WarnDataModel
      dmWarn.ClientID = dmClient.ID
      dmWarn.ClientName = dmClient.Name
      dmWarn.ShowDate = dmClient.Birthday.Date
      dmWarn.TypeID = dmWarnType.BirthdayWarn
      dmWarn.Msg = "今天是" & dmClient.Name & "的生日，记得向他问候啊"
      If bExist Then
        ErrMsg = Me.Update(dmWarn)
      Else
        ErrMsg = Me.AddNew(dmWarn)
      End If

    End If

    Return ErrMsg

  End Function


  '删除一条提醒信息
  Public Function Delete(Optional ByVal lngID As Integer = 0) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String

    '执行删除操作并返回操作结果
    strSQL = "Exec DeleteWarning " & lngID
    ErrMsg = dbObj.RunSql(strSQL)

    Return ErrMsg

  End Function

  '删除一条生日提醒信息
  Public Function DeleteBirthdayWarning(ByVal iClientId As Integer) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String

    '执行删除操作并返回操作结果
    strSQL = "Exec DeleteBirthdayWarning " & iClientId & "," & dmWarnType.BirthdayWarn
    ErrMsg = dbObj.RunSql(strSQL)

    Return ErrMsg

  End Function

  Public Function IsExist(ByVal iWarnId As Integer) As Boolean
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView

    strSQL = "Exec GetWarningById " & iWarnId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    Return (dv.Count > 0)

  End Function


  Public Function IsBirthdayWarningExist(ByVal iClientId As Integer) As Boolean
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView

    strSQL = "Exec GetBrithdayWarningInfo " & iClientId & "," & dmWarnType.BirthdayWarn
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    Return (dv.Count > 0)

  End Function

  '根据客户ID获取生日提醒信息
  Public Function GetBrithdayWarningInfo(ByRef dmWarn As WarningDataModel, _
                   ByVal iClientId As Integer) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView


    strSQL = "Exec GetBrithdayWarningInfo " & iClientId & "," & dmWarnType.BirthdayWarn
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    If dv.Count < 1 Then
      Return ErrMsg
    End If

    '此后对Ary(0)操作是安全的
    Dim Ary As New ArrayList
    ErrMsg = FillWarningtDataModel(Ary, dv)
    If dmWarn Is Nothing Then
      dmWarn = New WarningDataModel
    End If
    dmWarn = Ary(0)

    Return ErrMsg

  End Function

  '根据提醒信息ID获取提醒信息
  Public Function GetWarningInfo(ByRef dmWarn As WarningDataModel, _
                ByVal iWarnId As Integer) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView


    strSQL = "Exec GetWarningById " & iWarnId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    If dv.Count < 1 Then
      Return ErrMsg
    End If

    '此后对Ary(0)操作是安全的
    Dim Ary As New ArrayList
    ErrMsg = FillWarningtDataModel(Ary, dv)
    If dmWarn Is Nothing Then
      dmWarn = New WarningDataModel
    End If
    dmWarn = Ary(0)

    Return ErrMsg

  End Function

  Public Function SearchWarningsByDate(ByRef Ary As ArrayList, _
                ByVal dtDate As Date, _
                Optional ByVal bIncludeBrithdayWarn As Boolean = True) As String

    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView

    Dim iShowBirthday As Integer
    iShowBirthday = IIf(bIncludeBrithdayWarn, 1, 0)

    strSQL = "Exec GetWarningByDate '" & dtDate & "', " & iShowBirthday
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    If dv.Count < 1 Then
      Return ErrMsg
    End If

    '此后对Ary(0)操作是安全的
    If ary Is Nothing Then
      ary = New ArrayList
    End If
    ErrMsg = FillWarningtDataModel(Ary, dv)

    Return ErrMsg
  End Function

  Public Function SearchAllWarnings(ByRef Ary As ArrayList) As String

    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView

    strSQL = "Exec GetAllWarnings "
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    If dv.Count < 1 Then
      Return ErrMsg
    End If

    '此后对Ary(0)操作是安全的
    If Ary Is Nothing Then
      Ary = New ArrayList
    End If
    ErrMsg = FillWarningtDataModel(Ary, dv)

    Return ErrMsg
  End Function

  Private Function FillWarningtDataModel(ByRef Ary As ArrayList, ByVal dv As DataView) As String
    Dim ErrMsg As String = ""

    If Ary Is Nothing Then
      Ary = New ArrayList
    End If

    Dim i As Integer
    Ary.Capacity = dv.Count
    Dim dmWarn As WarningDataModel
    Try
      For i = 0 To dv.Count - 1
        dmWarn = New WarningDataModel
        With dmWarn
          '设置ClientId会自动设置ClientName的值
          .ClientID = dv(i)(Me.ClientIdField)
          .ClientName = dv(i)(Me.ClientNameField)
          .ID = dv(i)(Me.IDField)
          .Msg = dv(i)(Me.MsgField)
          .ShowDate = dv(i)(Me.ShowDateField)
          .TypeID = dv(i)(Me.TypeIdField)
          .TypeName = dv(i)(Me.TypeNameField)
        End With
        Ary.Add(dmWarn)
      Next
    Catch ex As Exception
      ErrMsg = ex.Message
    End Try

    Return ErrMsg

  End Function
  '方法结束
  '''''''''''''''''''''''''''''''''''''''''''''''''''''''
End Class