Encapsulates the database Access classes, support Access, using open source MIT agreement,

After a few days ago to try find VB class modules, I am not very good, and I used to code a global variable serious (most was written during his first year of undergraduate course) abuse, so I these two days to refactor the code, one of my projects under and using VB6 class module encapsulates the database access, convenient pile inquisitor,

CodePudding user response:

The code address: https://code.csdn.net/snippets/576568

CodePudding user response:

Authorization code: MIT
Code:

 
'licensing agreement: MIT 
'founder: Sun Rui 
Private m_Res As ADODB. You 

Private m_Conn As ADODB. Connection 

Private m_Command mand the As ADODB.Com 

Private m_ConnString As String 

Private m_FilePath As String 

Private m_Params As New Collection 

'rule: every function, after the completion of execution must empty m_Command, and create an empty object 
'[class initialization, release] 
Private Sub Class_Initialize () 

End Sub 

Private Sub Class_Terminate () 
The Set m_Res=Nothing 
The Set m_Conn=Nothing 
The Set m_Command=Nothing 
End Sub 

'[class attribute] 
'the database connection string 
Public Property Get the ConnectionString () As String 
The ConnectionString=m_ConnString 
End Property 

Public Property Let the ConnectionString (ByVal vNewValue As String) 
M_ConnString=vNewValue 
End Property 

'[methods] 
The Public Function ExecQuery (ByVal SqlStr As String) As you 

Dim tempRes As New you 

Mand Set m_Command=New ADODB.Com 
Call openConn 
M_Command. ActiveConnection=m_Conn 
M_Command.Com mandText SqlStr=
The Set tempRes=m_Command. The Execute () 
TempRes. ActiveConnection=Nothing 
Call closeConn 
The Set ExecQuery=tempRes 
The Set m_Command=Nothing 

End the Function 

The Public Function ExecParamQuery (ByVal SqlStr As String, ParamArray Params ()) 

Dim tempRes As New you 

Dim As Long I 

Mand Set m_Command=New ADODB.Com 
'to open the connection 
Call openConn 
M_Command. ActiveConnection=m_Conn 
M_Command.Com mandText SqlStr=
M_Command.Com mandType=adCmdText 

'set the parameter 
With m_Command 

For Each param In Params 

Dim Para As ADODB library. The Parameter 

The Set Para=. CreateParameter (CStr (I), GetVarType (param), adParamInput, LenB (param)) 
Para. Value=https://bbs.csdn.net/topics/param 
. The Parameters. Append Para 
Next 

End With 

Get the execution record set after 
The Set tempRes=m_Command. The Execute () 
'with the database connection decoupling 
TempRes. ActiveConnection=Nothing 
'close the database connection 
Call closeConn 
'return data set object reference 
The Set ExecParamQuery=tempRes 
'empty command object 
The Set m_Command=Nothing 

End the Function 

The Public Function ExecNonQuery (ByVal SqlStr As String) As Long 

'the influence lines of variables 
Dim affectedRows As Long 

'create binding Command objects 
Mand Set m_Command=New ADODB.Com 
'to open the connection 
Call openConn 
'binding Command to the database connection 
M_Command. ActiveConnection=m_Conn 
'set the SQL statement 
M_Command.Com mandText SqlStr=
'set the SQL type 
M_Command.Com mandType=adCmdText 
'get executed affect the number of rows 
M_Command. Execute affectedRows 
'close the database connection 
Call closeConn 
'empty command object 
The Set m_Command=Nothing 
'return to affect the number of rows 
ExecNonQuery=affectedRows 
End the Function 

The Public Function ExecParamNonQuery (ByVal SqlStr As String, ParamArray Params ()) As Long 

Dim As Long I 

Dim affectedRows As Long 

Mand Set m_Command=New ADODB.Com 
'to open the connection 
Call openConn 
M_Command. ActiveConnection=m_Conn 
M_Command.Com mandText SqlStr=
M_Command.Com mandType=adCmdText 

'set the parameter 
With m_Command 

For Each param In Params 

Dim Para As ADODB library. The Parameter 

The Set Para=. CreateParameter (CStr (I), GetVarType (param), adParamInput, LenB (param)) 
Para. Value=https://bbs.csdn.net/topics/param 
. The Parameters. Append Para 
Next 

End With 

Get the execution record set after 
M_Command. Execute affectedRows 
'close the database connection 
Call closeConn 
'empty command object 
The Set m_Command=Nothing 
'return to affect the number of rows 
ExecParamNonQuery=affectedRows 
End the Function 

As Public Sub SetConnToFile (ByVal FilePath String) 
M_ConnString="PROVIDER=Microsoft. Jet. The OLEDB. 4.0. Data Source="& amp; FilePath & amp; ";" 
End Sub 

Public Sub ReleaseRecordset (ByRef dbRes As ADODB. You) 
The Set dbRes=Nothing 
End Sub 

'[method] within 
Private Sub openConn () 
The Set m_Conn=New ADODB. Connection 
M_Conn. CursorLocation=adUseClient 
M_Conn. Open the ConnectionString 
End Sub 

Private Sub closeConn () 
M_Conn. Close 
The Set m_Conn=Nothing 
End Sub 

The Public Function GetVarType (ByRef Value As the Variant) As DataTypeEnum 

Dim k As New ADODB.Com mand 

The Select Case VarType (Value) 

Case VbVarType. VbString 
GetVarType=DataTypeEnum. AdVarChar 

Case VbVarType. VbInteger 
GetVarType=DataTypeEnum. AdSmallInt 

Case VbVarType. VbBoolean 
GetVarType=DataTypeEnum. AdBoolean 

Case VbVarType. VbCurrency 
nullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnullnull