booktype.cls

sql的实例源码

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 20 图书类型名称 Allow Null = False
'3 UID int 上级图书类型编号 Allow Null = False

Public TypeID As Long
Public TypeName As String
Public UID As Long

'初始化
Public Sub Init()
  TypeID = 0
  TypeName = ""
  UID = 0
End Sub

'判断是否存在此分类名称
Public Function IsExistTypeName(ByVal paraTypeName As String) As Boolean
  '定义rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM BookType WHERE TypeName = '" + Trim(StrReplace(paraTypeName)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    IsExistTypeName = False
  Else
    IsExistTypeName = True
  End If
  
  '断开连接
  DBDisconnect
End Function

'根据图书分类名称得到其他信息
Public Function GetInfo(ByVal paraTypeName As String) As Boolean
  '设置rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM BookType WHERE TypeName = '" + Trim(StrReplace(paraTypeName)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '若没有数据，则需要初始化
    Init
    '返回False
    GetInfo = False
  Else
    '===========写入RecordSet集中的数据===============
    '写入图书类型编号
    TypeID = rs.Fields(0)
    '得到图书类型名称
    TypeName = Trim(paraTypeName)
    '得到上级图书类型编号
    UID = rs.Fields(2)
    '=========写入RecordSet集中的数据成功=============

    '返回True
    GetInfo = True
  End If

  '断开连接
  DBDisconnect
End Function

'根据分类编号得到图书分类名称
Public Function GetTypeName(ByVal paraID As Long) As String
  '定义rs变量
  Dim rs As New ADODB.Recordset
  
  '设置SQL语句
  SQLStmt = "SELECT * FROM BookType WHERE TypeID = " + Trim(Str(paraID))
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '若没有数据，则返回空
    GetTypeName = ""
  Else
    '若找到对应值，返回此值
    GetTypeName = Trim(rs.Fields(1))
  End If

  '断开连接
  DBDisconnect
End Function

'判断是否存在子结点
Public Function HaveSubNode(ByVal paraUID As Long) As Boolean
  Dim rs As New ADODB.Recordset
  '设置要执行的SQL语句
  SQLStmt = "SELECT TypeID FROM BookType WHERE UID = " _
          + Trim(Str(paraUID))
  '执行SQL语句
  Set rs = SQLQuery(SQLStmt)
  '如果没有满足条件查询结果
  If rs.EOF Then
    HaveSubNode = False
  Else
    HaveSubNode = True
  End If

  '断开连接
  DBDisconnect
End Function

'判断指定类型中是否存在图书
Public Function HaveBookInfo(ByVal paraTypeID As Long) As Boolean
  Dim rs As New ADODB.Recordset
  '设置要执行的SQL语句
  SQLStmt = "SELECT * FROM BookInfo WHERE TypeID = " _
          + Trim(Str(paraTypeID))
  '执行SQL语句
  Set rs = SQLQuery(SQLStmt)
  '如果没有满足条件查询结果
  If rs.EOF Then
    HaveBookInfo = False
  Else
    HaveBookInfo = True
  End If

  '断开连接
  DBDisconnect
End Function

'插入操作
Public Sub Insert()
  '设置INSERT语句
  SQLStmt = "INSERT INTO BookType(TypeName, UID) VALUES('" _
          + Trim(StrReplace(TypeName)) + "', " + Trim(Str(UID)) + ")"
  '执行INSERT语句
  SQLExt SQLStmt
End Sub

'更新数据
Public Sub Update(ByVal paraTypeID As Long)
  SQLStmt = "UPDATE BookType SET TypeName = '" + Trim(StrReplace(TypeName)) _
          + "' WHERE TypeID = " + Trim(Str(paraTypeID))
  '执行UPDATE语句
  SQLExt SQLStmt
End Sub

'删除操作
Public Sub Delete(ByVal paraTypeID As Long)
  SQLStmt = "DELETE FROM BookType WHERE TypeID = " _
          + Trim(Str(paraTypeID))
  '执行DELETE语句
  SQLExt SQLStmt
End Sub