一个数据库的表至少要对应一个类
一个例子
'Module Name:BillResult
'File Name :BillResult.cls
'Function :两票不合格原因信息
'
'Project :两票管理系统
'Company :北京盈科世纪技术开发有限公司
'
'Version :1.0.0
'Author :John
'
'Notes :需要ADO2.1类库的支持
' :modCommon模块支持
'
'
'InterFace :
' Add :添加一新的两票不合格原因信息
' Delete :删除
' Update :修改现有的两票不合格原因信息
' SaveInfo:保存指定的两票不合格原因信息
' GetInfo :得到指定ID的两票不合格原因信息
' GetInfoByTypeNo :得到指定名称的两票不合格原因信息
' GetSummary :得到所有的两票不合格原因的ID编号
' CopyTo :自身复制
' Validation :检验合格与否
'
'
'Modify :
'John 2002-12-11
' 1、规范所有Sql语句为标准的Sql语句Option ExplicitPublic ID As Long '票据不合格原因编号
Public strName As String '票据不合格原因内容
Public strMethod As String '票据不合格原因解决方法
Public strNote As String '票据不合格原因备注
Public strRev As String '保留字段
Private Const csTBL_NAME_DATA As String = "BS_BillError"
Private Const csERR_NAME As String = "-+-+-"
Private mrec As ADODB.Recordset
Private mcnn As ADODB.ConnectionPublic Function Add() As Variant
On Error GoTo ErrDeal
Dim varR As Variant, varName As Variant
Dim strSql As String
Dim lngL As Long
Dim varAryID As Variant, varCount As Variant
'validate the member information
varR = Validation()
If varR <> True Then GoTo ErrDeal
'get database connection
varR = GetAdoCnn(mcnn)
If varR <> True Then GoTo ErrDeal
varR = IsExist(Me.ID)
If varR = True Then
varR = "指定的工作票不合格原因信息已存在!"
GoTo ErrDeal
End If
'using the sql statement to execute the task
strSql = "Insert into " & csTBL_NAME_DATA & _
" (ID,Name,Method,Note,strRev)" & _
" Values (" & ID & _
",'" & strName & "'" & _
",'" & strMethod & "'" & _
",'" & strNote & "'" & _
",'" & strRev & "'" & ")"
Debug.Print strSql
mcnn.Execute strSql, lngL
If lngL <> 1 Then
Debug.Assert False
varR = "添加工作票不合格原因信息失败!"
End If varR = True
ErrDeal:
If Err Then
varR = "Add" & vbCrLf & Err.Description
End If
Add = varR
End FunctionPublic Function Delete() As Variant
On Error GoTo ErrDeal
Dim varR As Variant
Dim strSql As String
Dim lngR As Long
'validate the id information
Debug.Assert (ID <> 0)
'get the database connection
varR = GetAdoCnn(mcnn)
If varR <> True Then GoTo ErrDeal
'using the sql statement to finish the task
strSql = "delete " & _
" from " & csTBL_NAME_DATA & _
" where ID=" & ID
mcnn.Execute strSql, lngR
If lngR = 0 Then
varR = "不能删除指定的工作票不合格原因信息!"
Else
varR = True
End If
ErrDeal:
If Err Then
varR = "Delete" & vbCrLf & Err.Description
End If
Delete = varR
End Function'由于ID号不是自动增加,所以要修改ID号时,需用删除原来的,再添加新的
Public Function Update(Optional ByVal varOldID As Variant = 0) As Variant
On Error GoTo ErrDeal
Dim varR As Variant
Dim strSql As String
Dim lngR As Long
Dim varAryID As Variant, varCount As Variant
'validate the member information
Debug.Assert (ID <> 0)
varR = Validation()
If varR <> True Then GoTo ErrDeal
'get the database connection
varR = GetAdoCnn(mcnn)
'if varOldID is not zero then delete the old and add a new
''' If varOldID <> 0 Then
''' 'delete the old one
''' lngR = Me.ID
''' Me.ID = varOldID
''' varR = Delete()
''' If varR Then
''' 'add a new one
''' Me.ID = lngR
''' varR = Add()
''' GoTo ErrDeal 'return add result message
''' Else 'return delete error message
''' GoTo ErrDeal
''' End If
''' End If
'check the message exist or not
Dim varE As Variant
varE = IsExist(Me.ID)
strSql = "Update " & csTBL_NAME_DATA & _
" set ID=" & ID & _
",Name='" & strName & "'" & _
",Method='" & strMethod & "'" & _
",Note='" & strNote & "'" & _
",strRev='" & strRev & "'"
If varOldID = 0 Then
'update the member information by sql statement
strSql = strSql & _
" where ID=" & ID
Debug.Print strSql
mcnn.Execute strSql, lngR
If lngR = 0 Then
varR = "修改指定的工作票不合格原因失败!"
Else
varR = True
End If
Else
If (varE = True) And (varOldID <> ID) Then
varR = "指定的工作票不合格原因已存在!"
Else
'update the member information by sql statement
strSql = strSql & _
" where ID=" & varOldID
Debug.Print strSql
mcnn.Execute strSql, lngR
If lngR = 0 Then
varR = "修改指定的工作票不合格原因失败!"
Else
varR = True
End If
End If
End If
ErrDeal:
If Err Then
varR = "Update" & vbCrLf & Err.Description
End If
Update = varR
End Function
一个例子
'Module Name:BillResult
'File Name :BillResult.cls
'Function :两票不合格原因信息
'
'Project :两票管理系统
'Company :北京盈科世纪技术开发有限公司
'
'Version :1.0.0
'Author :John
'
'Notes :需要ADO2.1类库的支持
' :modCommon模块支持
'
'
'InterFace :
' Add :添加一新的两票不合格原因信息
' Delete :删除
' Update :修改现有的两票不合格原因信息
' SaveInfo:保存指定的两票不合格原因信息
' GetInfo :得到指定ID的两票不合格原因信息
' GetInfoByTypeNo :得到指定名称的两票不合格原因信息
' GetSummary :得到所有的两票不合格原因的ID编号
' CopyTo :自身复制
' Validation :检验合格与否
'
'
'Modify :
'John 2002-12-11
' 1、规范所有Sql语句为标准的Sql语句Option ExplicitPublic ID As Long '票据不合格原因编号
Public strName As String '票据不合格原因内容
Public strMethod As String '票据不合格原因解决方法
Public strNote As String '票据不合格原因备注
Public strRev As String '保留字段
Private Const csTBL_NAME_DATA As String = "BS_BillError"
Private Const csERR_NAME As String = "-+-+-"
Private mrec As ADODB.Recordset
Private mcnn As ADODB.ConnectionPublic Function Add() As Variant
On Error GoTo ErrDeal
Dim varR As Variant, varName As Variant
Dim strSql As String
Dim lngL As Long
Dim varAryID As Variant, varCount As Variant
'validate the member information
varR = Validation()
If varR <> True Then GoTo ErrDeal
'get database connection
varR = GetAdoCnn(mcnn)
If varR <> True Then GoTo ErrDeal
varR = IsExist(Me.ID)
If varR = True Then
varR = "指定的工作票不合格原因信息已存在!"
GoTo ErrDeal
End If
'using the sql statement to execute the task
strSql = "Insert into " & csTBL_NAME_DATA & _
" (ID,Name,Method,Note,strRev)" & _
" Values (" & ID & _
",'" & strName & "'" & _
",'" & strMethod & "'" & _
",'" & strNote & "'" & _
",'" & strRev & "'" & ")"
Debug.Print strSql
mcnn.Execute strSql, lngL
If lngL <> 1 Then
Debug.Assert False
varR = "添加工作票不合格原因信息失败!"
End If varR = True
ErrDeal:
If Err Then
varR = "Add" & vbCrLf & Err.Description
End If
Add = varR
End FunctionPublic Function Delete() As Variant
On Error GoTo ErrDeal
Dim varR As Variant
Dim strSql As String
Dim lngR As Long
'validate the id information
Debug.Assert (ID <> 0)
'get the database connection
varR = GetAdoCnn(mcnn)
If varR <> True Then GoTo ErrDeal
'using the sql statement to finish the task
strSql = "delete " & _
" from " & csTBL_NAME_DATA & _
" where ID=" & ID
mcnn.Execute strSql, lngR
If lngR = 0 Then
varR = "不能删除指定的工作票不合格原因信息!"
Else
varR = True
End If
ErrDeal:
If Err Then
varR = "Delete" & vbCrLf & Err.Description
End If
Delete = varR
End Function'由于ID号不是自动增加,所以要修改ID号时,需用删除原来的,再添加新的
Public Function Update(Optional ByVal varOldID As Variant = 0) As Variant
On Error GoTo ErrDeal
Dim varR As Variant
Dim strSql As String
Dim lngR As Long
Dim varAryID As Variant, varCount As Variant
'validate the member information
Debug.Assert (ID <> 0)
varR = Validation()
If varR <> True Then GoTo ErrDeal
'get the database connection
varR = GetAdoCnn(mcnn)
'if varOldID is not zero then delete the old and add a new
''' If varOldID <> 0 Then
''' 'delete the old one
''' lngR = Me.ID
''' Me.ID = varOldID
''' varR = Delete()
''' If varR Then
''' 'add a new one
''' Me.ID = lngR
''' varR = Add()
''' GoTo ErrDeal 'return add result message
''' Else 'return delete error message
''' GoTo ErrDeal
''' End If
''' End If
'check the message exist or not
Dim varE As Variant
varE = IsExist(Me.ID)
strSql = "Update " & csTBL_NAME_DATA & _
" set ID=" & ID & _
",Name='" & strName & "'" & _
",Method='" & strMethod & "'" & _
",Note='" & strNote & "'" & _
",strRev='" & strRev & "'"
If varOldID = 0 Then
'update the member information by sql statement
strSql = strSql & _
" where ID=" & ID
Debug.Print strSql
mcnn.Execute strSql, lngR
If lngR = 0 Then
varR = "修改指定的工作票不合格原因失败!"
Else
varR = True
End If
Else
If (varE = True) And (varOldID <> ID) Then
varR = "指定的工作票不合格原因已存在!"
Else
'update the member information by sql statement
strSql = strSql & _
" where ID=" & varOldID
Debug.Print strSql
mcnn.Execute strSql, lngR
If lngR = 0 Then
varR = "修改指定的工作票不合格原因失败!"
Else
varR = True
End If
End If
End If
ErrDeal:
If Err Then
varR = "Update" & vbCrLf & Err.Description
End If
Update = varR
End Function
'判断指定的ID是否存在
Public Function IsExist(ByVal varID As Variant) As Variant
On Error GoTo ErrDeal
Dim varR As Variant
Dim strSql As String
varR = False
'validate the user id
Debug.Assert (varID <> 0)
If (varID = 0) Then GoTo ErrDeal
'get the connection
varR = GetAdoCnn(mcnn)
If varR <> True Then GoTo ErrDeal
'get the values
strSql = "Select ID" & _
" from " & csTBL_NAME_DATA & _
" where ID=" & varID
Set mrec = New ADODB.Recordset
mrec.Open strSql, mcnn, adOpenKeyset, adLockOptimistic
If (Not mrec.EOF) And (Not mrec.BOF) Then
varR = True
Else
varR = "没有找到指定的工作票不合格原因信息!"
End If
mrec.CloseErrDeal:
If Err Then
varR = "IsExist" & vbCrLf & Err.Description
End If
IsExist = varR
End FunctionPublic Function SaveInfo() As Variant
On Error GoTo ErrDeal
Dim varR As Variant
'update information first
varR = Update()
'if update result error message then add a newer
If varR <> True Then
varR = Add()
End If
ErrDeal:
If Err Then
varR = "SaveInfo" & vbCrLf & Err.Description
End If
SaveInfo = varR
End FunctionPublic Function GetInfo(ByVal varID As Variant) As Variant
On Error GoTo ErrDeal
Dim varR As Variant
Dim strSql As String
varR = False
'validate the user id
Debug.Assert (varID <> 0)
If (varID = 0) Then GoTo ErrDeal
'get the connection
varR = GetAdoCnn(mcnn)
If varR <> True Then GoTo ErrDeal
'get the values
strSql = "Select ID,Name,Method,Note,strRev" & _
" from " & csTBL_NAME_DATA & _
" where ID=" & varID
Set mrec = New ADODB.Recordset
mrec.Open strSql, mcnn, adOpenKeyset, adLockOptimistic
If (Not mrec.EOF) And (Not mrec.BOF) Then
ID = mrec!ID
strName = mrec!Name
strMethod = mrec!Method
strNote = mrec!Note
strRev = mrec!strRev
varR = True
Else
varR = "没有找到指定的工作票不合格原因信息!"
End If
mrec.CloseErrDeal:
If Err Then
varR = "GetInfo" & vbCrLf & Err.Description
End If
GetInfo = varR
End Function'得到指定要求的概要信息
'varAryID :返回的ID数组
'varCount :返回的ID的个数
'varID :要查的ID编号,为0所有的,否则查指定的
'varContent :要查的内容,为空查所有的,否则查类似的
'Return:返回True可错误信息
Public Function GetSummary(ByRef varAryID As Variant, ByRef varCount As Variant, _
Optional ByVal varID As Variant = 0, Optional ByVal varContent As Variant = "") As Variant
On Error GoTo ErrDeal
Dim varR As Variant
Dim varAryT() As Variant, lngL As Long
Dim strSql As String, strM As String
lngL = -1
varR = False
'get the database connection
varR = GetAdoCnn(mcnn)
If varR <> True Then GoTo ErrDeal
'get datavalue
If varContent <> "" Then
strM = " and (Name like '%" & varContent & "%')"
End If
If varID <> 0 Then
strM = strM & " and (ID=" & varID & ")"
End If
strSql = "Select ID" & _
" from " & csTBL_NAME_DATA & _
" where (1=1) " & strM & _
" order by ID"
Debug.Print strSql
Set mrec = New ADODB.Recordset
mrec.Open strSql, mcnn, adOpenKeyset, adLockOptimistic
If (Not mrec.BOF) And (Not mrec.EOF) Then
mrec.MoveFirst
Do While (Not mrec.EOF)
lngL = lngL + 1
ReDim Preserve varAryT(0 To lngL)
varAryT(lngL) = mrec!ID
mrec.MoveNext
Loop
varAryID = varAryT
varR = True
Else
varR = "没有找到指定的工作票不合格原因信息!"
End If
mrec.Close
ErrDeal:
If Err Then
varR = "GetSummary" & vbCrLf & Err.Description
End If
varCount = lngL + 1
GetSummary = varR
End FunctionPublic Function CopyTo(ByRef varObj As Variant) As Variant
On Error GoTo ErrDeal
Dim varR As Variant
Debug.Assert (Not varObj Is Nothing)
'validate the object
If TypeName(varObj) <> TypeName(Me) Then
Debug.Assert False
varR = "指定的对象类型不对!"
Else
'copy the object
varObj.ID = Me.ID
varObj.strName = Me.strName
varObj.strMethod = Me.strMethod
varObj.strNote = Me.strNote
varObj.strRev = Me.strRev
varR = True
End If
ErrDeal:
If Err Then
varR = "CopyTo" & vbCrLf & Err.Description
End If
CopyTo = varR
End FunctionPublic Function Validation() As Variant
On Error GoTo ErrDeal
Dim varR As Variant
'validate the no.
If (Me.ID) <= 0 Then
varR = "不合格原因编号不能为空!"
GoTo ErrDeal
End If
'validate the error content
If (Trim(Me.strName) = "") Then
varR = "不合格原因内容不能为空!"
GoTo ErrDeal
End If
varR = True
ErrDeal:
If Err Then
varR = "Validation" & vbCrLf & Err.Description
End If
Validation = varR
End FunctionPrivate Sub Class_Terminate()
Set mrec = Nothing
Set mcnn = Nothing
End Sub
不过不一定降低了维护的难度,如果什么都放到类里面去写,去维护那些类就和维护应用程序没有什么两样了。而且太多的new操作符将降低程序的效率。
一般来说适合的时候采用类。
“类”你可以从字面上去理解它的含义,就是具有相同性状的东西的抽象结构。所以,如果你的程序里使用到某一种可以独立抽象出来作为对象的东西,而且在很多地方都要用到的话,就用类来完成比较好。
例如最近我作了一个单向链表的程序,这种程序按照常规的写法就很容易实现。但是我为了减少我自己编码的麻烦,就采用VB里的类和Collection的做法。很方便的实现了。
再如我在用C++开发一个网络游戏的时候,无论是NPC还是电脑玩家,他们都有许多相同的地方,如攻击力,防御力,金钱数量,携带物品等等。我就抽象出来作为一个抽象类,然后再派生出NPC类和Player类。然后再根据需要实例化。