在VB6/Access数据库中实现三十二进制Duotricemary

223 views 0 comments posted at about 8 years ago Raymond Tang

前几天我发表了一篇博客,介绍了如何在C#中实现三十二进制的算法;虽然这解决了在系统逻辑层等的实现,但是由于我们的系统需要借助三十二进制实现一个ID主键的自增长,而这在Access数据库中实现就比较麻烦了,不过最终我完成了这个VB版本的三十二进制模块,它可以在Access中的窗体、查询等等对象中直接使用,也可以通过.NET调用,下面将依次介绍。

1,VB6/Access数据库中的模块代码

'All Rights Reservered by Raymond Tang
'Blog: http://hi.baidu.com/1987raymond
'Email: china.raymond[at]hotmail.com

'Website: http://www.polarlight.net

Option Compare Database
'On Error Resume Next
Public Const CHARS = "0123456789ABCDEFGHJKLMNPQRTUVWXY"
Public Const SYSTEM = 32
Public Const SPLITOR = "-"
Const ERROR = "X-ERROR"
Const MAXLENGTH = 4


'Create child node ID
Public Function CreateChildNode(ParentNodeID As String) As String
Dim CurrentID() As String
CurrentID = Split(ParentNodeID, SPLITOR)

If IsNull(CurrentID) Or IsEmpty(CurrentID) Then
CreateChildNode = ERROR
Exit Function
Else
'try to get the maximum child node id
Dim Sql As String
Sql = "SELECT TOP 1 Nodes.NodeID, Nodes.ParentNodeID " _
& "FROM Nodes WHERE (Nodes.ParentNodeID) = '" & ParentNodeID _
& "' ORDER BY Nodes.NodeID DESC;"
Dim RS As DAO.Recordset
Dim Db As DAO.Database
Set Db = CurrentDb
Set RS = Db.OpenRecordset(Sql)
Dim MaximumChildNode As Integer
If (RS.EOF And RS.BOF) Then
MaximumChildNode = -1
Else
MaximumChildNode = CLng(ToInt(RS.Fields("NodeID").Value))
End If
RS.Close
Db.Close
Set RS = Nothing
Set Db = Nothing

CreateChildNode = ToString(MaximumChildNode + 1)
End If
End Function

'add duotricemary
Public Function Add(Duo As String, Num As Integer) As String
Dim Length As Integer
Length = Len(Duo)

If Length > MAXLENGTH Then
Add = ERROR
Exit Function
Else
Dim Value As Long
Value = ToInt(Duo) + Num
Add = ToString(Value)
End If
End Function

Public Function Subtract(Duo As String, Num As Integer) As String
Dim Length As Integer
Length = Len(Duo)

If Length > MAXLENGTH Then
Subtract = ERROR
Exit Function
Else
Dim Value As Long
Value = ToInt(Duo) - Num
Subtract = ToString(Value)
End If
End Function

'Convert duotricemary sting to int
Public Function ToInt(Duo As String) As Long
Dim Value As Long
Value = 0
Dim Length As Integer
Length = Len(Duo)
Dim X, Y As Integer
Y = Length - 1
X = 0
For X = 1 To Length
Dim C As String
C = Mid(Duo, X, 1)
If IsNull(C) Or IsEmpty(C) Then
Value = 0
Exit For
Else
Dim Index As Integer
Index = InStr(Duotricemary.CHARS, C)
Value = Value + (Duotricemary.SYSTEM ^ Y) * (Index - 1)
End If
Y = Y - 1
Next

ToInt = Value
End Function

'Convert int to duotricemary string
Public Function ToString(Num As Long) As String
Dim Str As String
Str = Empty
Dim Temp As Long
Dim N As Integer
Temp = Num
N = 0

While Temp > 0
N = Int(Temp Mod Duotricemary.SYSTEM)
Temp = CLng((Temp - N) / 32)
Str = Mid(Duotricemary.CHARS, N + 1, 1) & Str
Wend

ToString = Str
End Function

如上面的代码所述,实现了简单的三十二进制字符串与整型之间的相互转换,还实现了求和与相减的方法,值得说明一点的是,我们也可以直接通过一个模块类来封装,这样类似于c#中的那个类,我这里由于时间原因,暂且直接用全局性的模块实现。

2,在Access中的窗体事件中使用

新建一个Access的Form对象在按钮的单击事件中添加如下代码:

Private Sub CommandTest_Click()
Dim Str As String
Str = "DFGF"
Dim I As Long

I = 22211
MsgBox Str & "=" & Duotricemary.ToInt(Str)
MsgBox I & "=" & Duotricemary.ToString(I)
MsgBox Str & "+32 =" & Duotricemary.Add(Str, 32)
MsgBox Str & "-32 =" & Duotricemary.Subtract(Str, 32)
End Sub

运行结果如下:



可以看出,运行结果与c#版本的一致。

3,在Access的查询语句中使用

在Access数据库中新建一个数据表Nodes,其结构如下:

在表中插入几条测试数据:

Nodes
NodeID ParentNodeID
0 X
EF X
322 EF
E2 EF
G6 EF
在Access数据库中新建一查询:

SELECT Nodes.NodeID, Nodes.ParentNodeID, ToInt([Nodes]![NodeID]) AS NodeIDIntValue, Add([Nodes]![NodeID],200) AS NodeIDAdd200
FROM Nodes;
查询结果为:

TestQuery
NodeID ParentNodeID NodeIDIntValue NodeIDAdd200
0 X 0 68
322 EF 3138 38A
E2 EF 450 LA
EF X 463 LP
G6 EF 518 NE

请注意查询中用到的红色下划线标注的模块中的方法。

4,关于CreateChildNode添加子节点的方法

由于在我们的系统中需要运用到ID自增长,所以增加了此方法,其使用如下:

在窗体中添加一个按钮在其单击事件中添加如下代码:

Private Sub CommandTestAddChild_Click()
Dim ParentID As String
ParentID = "EF"
For I = 1 To 10
CurrentDb.Execute ("insert into Nodes values('" & CreateChildNode(ParentID) _
& "','" & ParentID & "')")
Next
MsgBox "Succeed!"
End Sub

上段代码的作用为指定的父节点EF添加10个子节点,运行结果如下:

Nodes
NodeID ParentNodeID
0 X
322 EF
E2 EF
EF X
G6 EF
G7 EF
G8 EF
G9 EF
GA EF
GB EF
GC EF
GD EF
GE EF
GF EF
GG EF

上面表格红色标注的即为新添加的节点。

5,在.NET中调用

经尝试 目前暂无好的方法,由于这些VB函数需要VB环境的支持,所以无法直接调用;如果大家有好的方法解决这一个问题请分享,谢谢。

一个可以替代的方式是将CreateChildNode方法转移到比如.NET项目的业务逻辑层进行控制,而不是在数据库端进行控制;需要考虑同步的情况,比如同事给一个节点添加子节点,这需要再逻辑中线程同步,比如加锁。

Add comment

Comments (0)

No comments yet.
In this Page