clienttype.vb

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

Public Class ClientType

#Region "字段常量字义"
  Protected Const TypeNameField As String = "TypeName" '类型名称
  Protected Const IDField As String = "TypeId" '类型Id
  Protected Const SuperIDField As String = "SuperId" '上级Id
#End Region

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

  '新增一个客户类型，并返回操作的结果
  Public Function AddNew(ByRef dmCtType As ClientTypeDataModel) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView


    '检测客户类型是否已经存在
    If Me.IsExist(dmCtType.TypeName) Then
      ErrMsg = "名称重复"
      Return ErrMsg
    End If

    '检测上级客户类型是否存在
    If dmCtType.SuperID <> 0 And (Not Me.IsExist(dmCtType.SuperID)) Then
      ErrMsg = "上级类型不存在"
      Return ErrMsg
    End If


    '构造SQL语句，注意需调用SafeDbString去除字符串中的单引号
    strSQL = "EXEC AddClientType '" & dbObj.SafeDbString(dmCtType.TypeName) & "'," _
              & dmCtType.SuperID

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

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

    Return ErrMsg

  End Function

  '修改客户类型信息，返回操作结果
  Public Function Update(ByVal dmCtType As ClientTypeDataModel) As String
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String

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

    '检测客户类型是否已经存在
    If Me.IsExist(dmCtType.TypeName) Then
      ErrMsg = "名称重复"
      Return ErrMsg
    End If

    '构造SQL语句，注意需调用SafeDbString函数去除字符串中的单引号
    strSQL = "EXEC UpdateClientType '" & dbObj.SafeDbString(dmCtType.TypeName) & "'," _
              & dmCtType.SuperID & "," _
              & dmCtType.ID

    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

    '如果该客户类型下面有客户，则也不能删除
    If Me.IsExistClient(lngID) Then
      ErrMsg = "存在子客户"
      Return ErrMsg
    End If

    '如果该客户类型下有子客户类型，则不能删除
    If Me.IsExistChild(lngID) Then
      ErrMsg = "存在子客户类型"
      Return ErrMsg
    End If

    strSQL = "Exec DeleteClientType " & lngID
    ErrMsg = dbObj.RunSql(strSQL)

    Return ErrMsg

  End Function

  '是否存在名称是szTypeName的类型
  Public Function IsExist(ByVal szTypeName As String) As Boolean
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView

    strSQL = "Exec GetClientTypeByName " & "'" & dbObj.SafeDbString(szTypeName) & "'"
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    Return (dv.Count > 0)

  End Function

  '是否存在ID是iTypeId的类型
  Public Function IsExist(ByVal iTypeId 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 GetClientTypeById " & iTypeId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    Return (dv.Count > 0)

  End Function

  'ID为iTypeId的类型是否还存在子类型
  Public Function IsExistChild(ByVal iTypeId 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 GetChildClientTypeById " & iTypeId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    Return (dv.Count > 0)

  End Function

  'ID为iTypeId的类型是否存在下属客户
  Public Function IsExistClient(ByVal iTypeId 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 GetChildClientById " & iTypeId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    Return (dv.Count > 0)

  End Function

  Public Function IsExistClient(ByVal iTypeId As Integer, _
                                ByVal szName As String, _
                                ByVal szTel As String) As Boolean
    Dim dbObj As New DBOpProvider.ConnSqlServer.SQLClass
    Dim ErrMsg As String = ""
    Dim strSQL As String
    Dim dv As New DataView

    strSQL = "Exec SearchDirectChildClient " & iTypeId & "," _
                  & "'" & dbObj.SafeDbString(szName) & "'," _
                  & "'" & dbObj.SafeDbString(szTel) & "'"

    ErrMsg = dbObj.GetDataView(strSQL, dv)
    Return (dv.Count > 0)

  End Function


  '返回客户类型信息
  Public Function GetTypeInfo(ByRef dmType As ClientTypeDataModel, _
                              ByVal iTypeId 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 GetTypeInfo " & iTypeId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    If dv.Count < 1 Then
      Return ErrMsg
    End If

    dmType.ID = dv(0)(Me.IDField)
    dmType.SuperID = dv(0)(Me.SuperIDField)
    dmType.TypeName = dv(0)(Me.TypeNameField)

    Return ErrMsg

  End Function


  '返回所有子类型
  Public Function GetAllDirectSubTypes(ByRef Ary As ArrayList, _
                                  ByVal iTypeId 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 GetAllDirectSubTypes " & iTypeId
    ErrMsg = dbObj.GetDataView(strSQL, dv)

    If ErrMsg <> "" Then
      Return ErrMsg
    End If

    If Ary Is Nothing Then
      Ary = New ArrayList
    End If
    Ary.Clear()
    Ary.Capacity = dv.Count
    Dim i As Integer
    Dim dmType As ClientTypeDataModel
    For i = 0 To dv.Count - 1
      dmType = New ClientTypeDataModel
      dmType.ID = dv(i)(Me.IDField)
      dmType.SuperID = dv(i)(Me.SuperIDField)
      dmType.TypeName = dv(i)(Me.TypeNameField)
      Ary.Add(dmType)
    Next

    Return ErrMsg

  End Function



End Class