client.cls

来自「Visual Basic + Oracle 9i数据库应用系统开发与实例（企业购」· CLS 代码 · 共 234 行

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "Client"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1   ClientId    Int 客户编号
'2   ClientName  Varchar 50  客户名称
'3   ClientType  Tinyint 客户类型，1 - 供应商、2 - 购货商
'4   Bank    Varchar 50  开户银行
'5   BankAccount Varchar 50  银行帐号
'6   Contact Varchar 30  联系人
'7   Address Varchar 50  通信地址
'8   Postcode    Varchar 10  邮政编码
'9   Phone   Varchar 30  联系电话
'10  Fax Varchar 30  传真电话
'11  Describe    Varchar 100 客户描述

Public ClientId As Long
Public ClientName As String
Public ClientType As Integer
Public Bank As String
Public BankAccount As String
Public Contact As String
Public Address As String
Public Postcode As String
Public Phone As String
Public Fax As String
Public Describe As String

Public Sub Init()
  ClientId = -1
  ClientName = ""
  ClientType = -1
  Bank = ""
  BankAccount = ""
  Contact = ""
  Address = ""
  Postcode = ""
  Phone = ""
  Fax = ""
  Describe = ""
End Sub

'删除
Public Sub Delete(TmpId As Long)
  DB_Connect
  
  SqlStmt = "DELETE FROM Client WHERE ClientId=" _
          + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Sub

Public Function GetId(ByVal TmpName As String) As Long
  If TmpName = "" Then
    GetId = 0
    Exit Function
  End If
  DB_Connect
  
  SqlStmt = "SELECT ClientId FROM Client WHERE ClientName='" _
          + Trim(TmpName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    GetId = Val(ColVal)
  Else
    GetId = 0
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Sub GetInfo(TmpId As Long)
  ClientId = TmpId
  If TmpId = 0 Then
    Init
    Exit Sub
  End If
  DB_Connect
  
  SqlStmt = "SELECT * FROM Client WHERE ClientId=" _
          + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    ClientName = TrimStr(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Len(ColVal), pcblen)
    ClientType = Val(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Len(ColVal), pcblen)
    Bank = TrimStr(ColVal)
  
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 5, 1, ColVal, Len(ColVal), pcblen)
    BankAccount = TrimStr(ColVal)
  
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 6, 1, ColVal, Len(ColVal), pcblen)
    Contact = TrimStr(ColVal)
  
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 7, 1, ColVal, Len(ColVal), pcblen)
    Address = TrimStr(ColVal)
  
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 8, 1, ColVal, Len(ColVal), pcblen)
    Postcode = TrimStr(ColVal)
  
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 9, 1, ColVal, Len(ColVal), pcblen)
    Phone = TrimStr(ColVal)
  
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 10, 1, ColVal, Len(ColVal), pcblen)
    Fax = TrimStr(ColVal)
  
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 11, 1, ColVal, Len(ColVal), pcblen)
    Describe = TrimStr(ColVal)
  Else
    Init
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetName(TmpId As Long) As String
  DB_Connect
  
  SqlStmt = "SELECT ClientName FROM Client WHERE ClientId=" _
          + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    GetName = TrimStr(ColVal)
  Else
    GetName = ""
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Function

Public Function In_DB(ByVal TmpClientName As String) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT ClientId FROM Client WHERE ClientName='" + Trim(TmpClientName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    In_DB = True
  Else
    In_DB = False
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Function Insert() As Long
  ClientId = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO Client VALUES( CLIENTID.NEXTVAL, '" + Trim(ClientName) + "'," + Trim(Str(ClientType)) _
          + ",'" + Trim(Bank) + "','" + Trim(BankAccount) + "','" + Trim(Contact) _
          + "','" + Trim(Address) + "','" + Trim(Postcode) + "','" + Trim(Phone) + "','" _
          + Trim(Fax) + "','" + Trim(Describe) + "')"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
  Insert = ClientId
End Function

Public Sub Load_Client(ByVal TmpType As Integer)
  '初始化数组
  Erase Arr_Client
  ReDim Arr_Client(0)
  '连接数据库
  DB_Connect
  '设置SQL语句，读取指定类型的所有客户名称，即ClientType=TmpType的所有ClientName值
  SqlStmt = "SELECT ClientName FROM Client WHERE ClientType=" _
          + Trim(TmpType) + " ORDER BY ClientName"
  '执行SQL语句
  OdbcExt (SqlStmt)
  i = 0
  '依次把结果集中的数据读取到数组Arr_Client()中
  Do Until SQLFetch(Hstmt) = SQL_NO_DATA_FOUND
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_Client(i + 1)
    Arr_Client(i) = TrimStr(ColVal)
    i = i + 1
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Sub

'更新数据
Public Sub Update(ByVal TmpId As Long)
  DB_Connect
    
  SqlStmt = "UPDATE Client SET ClientName='" + Trim(ClientName) _
          + "', ClientType=" + Trim(Str(ClientType)) _
          + ", Bank='" + Trim(Bank) + "',BankAccount='" + Trim(BankAccount) _
          + "', Contact='" + Trim(Contact) + "',Address='" _
          + Trim(Address) + "',Postcode='" + Trim(Postcode) _
          + "', Phone='" + Trim(Phone) + "', Fax='" _
          + Trim(Fax) + "', Describe='" + Trim(Describe) _
          + "' WHERE ClientId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub