booktype.cls

VB和Oracle结合的程序

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "BookType"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 TypeId  Int 分类编号
'2 TypeName  Varchar 50  分类名称
'3 Describe  Varchar 400 描述信息
'4 UpperId Int 上级分类的编号

Public TypeId As Long
Public TypeName As String
Public Describe As String
Public UpperId As Long

Public Sub Init()
  TypeId = -1
  TypeName = ""
  Describe = ""
  UpperId = -1
End Sub

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

  DB_Disconnect
End Sub

Public Function GetId(ByVal TmpName As String) As Long
  DB_Connect
  
  SqlStmt = "SELECT TypeId FROM BookType WHERE TypeName='" _
          + Trim(TmpName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND Then
    ColVal = String(400, 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)
  TypeId = TmpId
  
  DB_Connect
  
  SqlStmt = "SELECT * FROM BookType WHERE TypeId=" + 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)
    TypeName = TrimStr(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 3, 1, ColVal, Len(ColVal), pcblen)
    Describe = TrimStr(ColVal)
    
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 4, 1, ColVal, Len(ColVal), pcblen)
    UpperId = Val(ColVal)
  Else
    Init
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  
  DB_Disconnect
End Sub

Public Function GetName(ByVal TmpId As Long) As String
  DB_Connect
  
  SqlStmt = "SELECT TypeName FROM BookType WHERE TypeId=" _
          + 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 GetNewId() As Long
  Dim TmpId As Long
  '连接数据库
  DB_Connect
  '设置要执行的SQL语句
  SqlStmt = "SELECT TypeId FROM BookType ORDER BY TypeId"
  '执行SQL语句
  OdbcExt (SqlStmt)
  '读取SELECT语句返回的查询结果
  i = 1
  Do While SQLFetch(Hstmt) <> SQL_NO_DATA_FOUND
    ColVal = String(40, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    TmpId = Val(ColVal)
    If TmpId = i Then
      i = i + 1
    Else
      GetNewId = i
      Rc = SQLFreeStmt(Hstmt, SQL_DROP)
      DB_Disconnect
      Exit Function
    End If
  Loop
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
  '将得到的新编号作为函数的返回值
  GetNewId = i
End Function

Public Function HaveSon(ByVal TmpUpperId As Long) As Boolean
  '连接数据库
  DB_Connect
  '设置要执行的SQL语句
  SqlStmt = "SELECT TypeId FROM BookType WHERE UpperId=" _
          + Trim(Str(TmpUpperId))
  '执行SQL语句
  OdbcExt (SqlStmt)
  '如果SQLFetch()函数返回SQL_NO_DATA_FOUND，则表示没有满足条件查询结果
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    HaveSon = False
  Else
    HaveSon = True
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  '断开与数据库的连接
  DB_Disconnect
End Function

Public Function In_DB(ByVal TypeName As String) As Boolean
  DB_Connect
  
  SqlStmt = "SELECT TypeId FROM BookType WHERE TypeName='" _
          + Trim(TypeName) + "'"
  OdbcExt (SqlStmt)
  If SQLFetch(Hstmt) = SQL_NO_DATA_FOUND Then
    In_DB = False
  Else
    In_DB = True
  End If
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)

  DB_Disconnect
End Function

Public Function Insert() As Long
  TypeId = GetNewId
  
  DB_Connect
  
  SqlStmt = "INSERT INTO BookType VALUES(" + Trim(TypeId) + ",'" + Trim(TypeName) _
          + "','" + Trim(Describe) + "'," + Trim(Str(UpperId)) + ")"
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
  Insert = TypeId
End Function

Public Sub Load_by_Upper(ByVal UpperId As Integer)
  Dim i As Long
  '对数组进行初始化
  Erase Arr_BookType
  ReDim Arr_BookType(0)
  Erase Arr_TypeId
  ReDim Arr_TypeId(0)
  '连接数据库
  DB_Connect
  '设置SQL语句
  SqlStmt = "SELECT TypeId, TypeName FROM BookType WHERE UpperId=" _
          + Trim(Str(UpperId)) + " ORDER BY TypeName"
  '执行SQL语句
  OdbcExt (SqlStmt)
  i = 0
  '依次把读取的数据赋值到数组中
  Do Until SQLFetch(Hstmt) = SQL_NO_DATA_FOUND
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 1, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_TypeId(i + 1)
    Arr_TypeId(i) = Val(ColVal)
    
    ColVal = String(400, 0)
    Rc = SQLGetData(Hstmt, 2, 1, ColVal, Len(ColVal), pcblen)
    ReDim Preserve Arr_BookType(i + 1)
    Arr_BookType(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 BookType SET TypeName='" + Trim(TypeName) _
          + "',Describe='" + Trim(Describe) + "' WHERE TypeId=" + Trim(Str(TmpId))
  OdbcExt (SqlStmt)
  Rc = SQLFreeStmt(Hstmt, SQL_DROP)
  DB_Disconnect
End Sub