前几天我发表了一篇博客,介绍了如何在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项目的业务逻辑层进行控制,而不是在数据库端进行控制;需要考虑同步的情况,比如同事给一个节点添加子节点,这需要再逻辑中线程同步,比如加锁。

About author
Disclaimer
The opinions and comments expressed herein are my own personal opinions and do not represent my employer's view in any way.
Comments
No comments.
Add comment
Title
Title is required.
Name
Name is required.
Email
Please input your personal email with valid format.
Comments
Please input comment content.
Captcha Refresh
Input captcha:

Subscription

Statistics

Locations of visitors to this page