borrowinfo.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 = "BorrowInfo"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
'1 CardNo varchar 16 借阅证号 Allow Null = False
'2 BookNo varchar 20 图书编号 Allow Null = False 关键字
'3 BorrowDate datetime 借阅日期 Allow Null = False
'4 RenewDate datetime 续借日期 Allow Null = True
'5 ReturnDate datetime 归还日期 Allow Null = False
'6 Fine real 过期罚金 Allow Null = True 默认值：0
'7 BorrowState tinyint 借阅状态 Allow Null = False （0－借阅，1－续借，2－过期）

Public CardNo As String
Public BookNo As String
Public BorrowDate As Date
Public RenewDate As Date
Public ReturnDate As Date
Public Fine As Single
Public BorrowState As Byte

'初始化
Public Sub Init()
  CardNo = ""
  BookNo = ""
  BorrowDate = Now
  RenewDate = Now
  ReturnDate = Now
  Fine = 0
  BorrowState = 0
End Sub

'判断是否已经借阅过指定图书
Public Function IsExistBNo(ByVal paraBNo As String) As Boolean
  '设置rs变量
  Dim rs As New ADODB.Recordset

  '设置SQL语句
  SQLStmt = "SELECT * FROM BorrowInfo WHERE BookNo = '" + Trim(StrReplace(paraBNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '如果没查询到此图书编号，表明没有借出此书，返回False
    IsExistBNo = False
  Else
    '如果查询到此图书编号，表明已经借出此书，返回True
    IsExistBNo = True
  End If
End Function

'根据图书编号得到其他信息
Public Function GetInfo(ByVal paraBNo As String) As Boolean
  '设置rs变量
  Dim rs As New ADODB.Recordset

  '设置SQL语句
  SQLStmt = "SELECT * FROM BorrowInfo WHERE BookNo = '" + Trim(StrReplace(paraBNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  If rs.EOF Then
    '若没有数据，则需要初始化
    Init
    '返回False
    GetInfo = False
  Else
    '===========写入RecordSet集中的数据===============
    '得到借阅证号
    CardNo = Trim(rs.Fields(0))
    '写入图书编号
    BookNo = Trim(paraBNo)
    '得到借阅日期
    BorrowDate = rs.Fields(2)
    '得到续借日期
    If IsNull(rs.Fields(3)) Then
      RenewDate = Now
    Else
      RenewDate = rs.Fields(3)
    End If
    '得到归还日期
    ReturnDate = rs.Fields(4)
    '得到罚金
    If IsNull(rs.Fields(5)) Then
      Fine = 0
    Else
      Fine = rs.Fields(5)
    End If
    '得到借阅状态
    BorrowState = rs.Fields(6)
    '=========写入RecordSet集中的数据成功=============

    '返回True
    GetInfo = True
  End If

  '断开连接
  DBDisconnect
End Function

'根据借阅证号得到借阅书籍数量
Public Function GetCount(ByVal paraCNo As String) As Integer
  '设置rs变量
  Dim rs As New ADODB.Recordset

  '设置SQL语句
  SQLStmt = "SELECT * FROM BorrowInfo WHERE CardNo = '" + Trim(StrReplace(paraCNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  GetCount = 0
  While Not rs.EOF
    GetCount = GetCount + 1
    rs.MoveNext
  Wend

  '断开连接
  DBDisconnect
End Function

'插入操作
Public Sub Insert()
  '设置INSERT语句
  SQLStmt = "INSERT INTO BorrowInfo(CardNo, BookNo, BorrowDate, ReturnDate, BorrowState)" _
          + " VALUES('" + Trim(StrReplace(CardNo)) + "', '" _
          + Trim(StrReplace(BookNo)) + "', " + Format(BorrowDate, "yyyy-mm-dd") + ", " _
          + Format(ReturnDate, "yyyy-mm-dd") + ", " + Trim(Str(BorrowState)) + ")"
  '执行INSERT语句
  SQLExt SQLStmt
  
  '===========修正时间=============
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM BorrowInfo WHERE BookNo = '" + Trim(StrReplace(BookNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  rs.Fields(2) = Format(BorrowDate, "yyyy-mm-dd")
  rs.Fields(4) = Format(ReturnDate, "yyyy-mm-dd")
  rs.Update

  '断开连接
  DBDisconnect
End Sub

'更新时间
Public Sub UpdateDate(ByVal paraBNo As String, ByVal paraDate As Date, ByVal paraPos As Integer)
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM BorrowInfo WHERE BookNo = '" + Trim(StrReplace(paraBNo)) + "'"
  Set rs = SQLQuery(SQLStmt)
  rs.Fields(paraPos) = Format(paraDate, "yyyy-mm-dd")
  rs.Update

  '断开连接
  DBDisconnect
End Sub

'更新罚金金额
Public Sub UpdateFine(ByVal paraBNo As String, ByVal paraFine As Single)
  SQLStmt = "UPDATE BorrowInfo SET Fine = " + Trim(Str(paraFine)) _
          + " WHERE BookNo = '" + Trim(StrReplace(paraBNo)) + "'"
  SQLExt SQLStmt
End Sub

'更新借阅状态
Public Sub UpdateState(ByVal paraBNo As String, ByVal paraState As Byte)
  SQLStmt = "UPDATE BorrowInfo SET BorrowState = " + Trim(Str(paraState)) _
          + " WHERE BookNo = '" + Trim(StrReplace(paraBNo)) + "'"
  SQLExt SQLStmt
End Sub

'更新所有借阅状态
Public Sub UpdateAllState(ByVal paraState As Byte)
  '设置rs变量
  Dim rs As New ADODB.Recordset
  '设置SQL语句
  SQLStmt = "SELECT * FROM BorrowInfo"
  Set rs = SQLQuery(SQLStmt)
  While Not rs.EOF
    '过期书籍需要修改状态和罚金
    If rs.Fields(4) < Now Then
      rs.Fields(5) = 0.3 * (Now - rs.Fields(4))
      rs.Fields(6) = paraState
    End If
    rs.MoveNext
  Wend

  '断开连接
  DBDisconnect
End Sub

'删除操作
Public Sub Delete(ByVal paraBNo As String)
  SQLStmt = "DELETE FROM BorrowInfo WHERE BookNo = '" _
          + Trim(StrReplace(paraBNo)) + "'"
  '执行DELETE语句
  SQLExt SQLStmt
End Sub