cooperate.vb

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

Public Class Cooperate

#Region "字段常量字义"
  Protected Const IDField As String = "CooperateId" '合作信息ID
  Protected Const ClientIdField As String = "ClientId" '客户ID
  Protected Const ClientNameField As String = "ClientName" '客户名称
  Protected Const CooperateDateField As String = "CooperateDate" '合作日期
  Protected Const SatisfactionField As String = "Satisfaction" '合作满意度
  Protected Const RemarkField As String = "Remark" '合作信息
#End Region

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

  '新增一条合作信息，并返回操作的结果
  Public Function AddNew(ByRef dmCoop As CooperateDataModel) 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 AddCoop " _
              & dmCoop.mvarClientId & "," _
              & "'" & dbObj.SafeDbString(dmCoop.mvarClientName) & "'," _
              & "'" & dmCoop.mvarCooperateDate & "'," _
              & dmCoop.mvarSatisfaction & "," _
              & "'" & dbObj.SafeDbString(dmCoop.mvarRemark) & "'"


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

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

    Return ErrMsg

  End Function

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

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

    '构造SQL语句，注意需调用SafeDbString去除字符串中的单引号
    strSQL = "EXEC UpdateCoop " & dmCoop.mvarID & "," _
          & dmCoop.mvarClientId & "," _
          & "'" & dbObj.SafeDbString(dmCoop.mvarClientName) & "'," _
          & "'" & dmCoop.mvarCooperateDate & "'," _
          & dmCoop.mvarSatisfaction & "," _
          & "'" & dbObj.SafeDbString(dmCoop.mvarRemark) & "'"

    ErrMsg = dbObj.RunSql(strSQL)

    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 DeleteCooperate " & lngID
    ErrMsg = dbObj.RunSql(strSQL)

    Return ErrMsg

  End Function

  Public Function IsExist(ByVal iCoopId 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 GetCooperateById " & iCoopId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    Return (dv.Count > 0)

  End Function


  '得到与此客户相关的所有合作信息
  '如果iClientId = 0, 则查找所有的合作信息
  Public Function Search(ByRef Ary As ArrayList, _
                  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


    If Ary Is Nothing Then
      Ary = New ArrayList
    End If

    strSQL = "Exec SearchCoopByClientId " & iClientId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    If ErrMsg <> "" Then
      Return ErrMsg
    End If

    ErrMsg = FillCoopDataModel(Ary, dv)

    Return ErrMsg

  End Function


  '// -----------------------------------------------------------------------------
  '// <summary>
  '// 获取合作信息
  '// </summary>
  '// <param name="dmCoop">[in][out]合作信息的数据模型</param>
  '// <param name="iCoopId">合作信息ID</param>
  '// <returns>成功返回空字符串，失败返回错误信息</returns>
  '// <remarks>
  '// </remarks>
  '// <history>
  '// 	[Tom]	2006-2-16	Created
  '// </history>
  '// -----------------------------------------------------------------------------
  Public Function GetCooperateInfo(ByRef dmCoop As CooperateDataModel, _
                ByVal iCoopId 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 GetCooperateById " & iCoopId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    If dv.Count < 1 Then
      Return ErrMsg
    End If

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

    Return ErrMsg

  End Function

  Private Function FillCoopDataModel(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 dmCoop As CooperateDataModel
    Try
      For i = 0 To dv.Count - 1
        dmCoop = New CooperateDataModel
        '下面直接给成员变量赋值，有两大优点：
        '  1）全部拥有访问权限
        '  2）减少函数调用，速度快
        With dmCoop
          .mvarClientId = dv(i)(Me.ClientIdField)
          .mvarClientName = dv(i)(Me.ClientNameField)
          .mvarCooperateDate = dv(i)(Me.CooperateDateField)
          .mvarID = dv(i)(Me.IDField)
          .mvarRemark = dv(i)(Me.RemarkField)
          .mvarSatisfaction = dv(i)(Me.SatisfactionField)
        End With
        Ary.Add(dmCoop)
      Next
    Catch ex As Exception
      ErrMsg = ex.Message
    End Try

    Return ErrMsg

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