Option Explicit
'--------------------------------------------------------
'[Class Name]:  DBHelper
'[Description]:      databse access class
'--------------------------------------------------------
Private mConn As ADODB.Connection
Private mIsOpen As Boolean
Private mIsBeginTran As Boolean
Private Const MAX_TIME_OUT As Integer = 1800 'command timeout（unit：second）

'--------------------------------------------------------
'[Function name]:  OpenConnection
'[Description]:    DB connection
'[Parameter]:    (1)Server name (2)Database name (3)User name (4)Password
'--------------------------------------------------------
Public Sub OpenConnection(ByVal Server As String, ByVal Database As String, _
    ByVal Username As String, ByVal Password As String)

    Dim connString As String
    connString = OleDbConnectionString(Server, Database, Username, Password)
    
    Set mConn = New ADODB.Connection
    mConn.CommandTimeout = MAX_TIME_OUT
    mConn.Open connString
    mIsOpen = True
End Sub

'--------------------------------------------------------
'[Function name]:  Dispose
'[Description]:    Dispose
'--------------------------------------------------------
Public Sub Dispose()
 
    If mIsOpen Then
        mConn.Close
    End If
    mIsOpen = False
    Set mConn = Nothing
End Sub

'--------------------------------------------------------
'[Function name]:  ExecuteNoQuery
'[Description]:    SQL execute
'[Parameter]:    (1)SQL statement
'--------------------------------------------------------
Public Sub ExecuteNoQuery(ByVal strSQL As String)
    
    If mIsOpen Then
        mConn.Execute (strSQL)
    End If
End Sub

'--------------------------------------------------------
'[Function name]:  ExecuteRecordset
'[Description]:    SQL excute
'[Parameter]:    (1)SQL
'[Return Value]:  ADODB.Recordset
'--------------------------------------------------------
Public Function ExecuteRecordset(ByVal strSQL As String) As ADODB.recordSet
    
    Dim rs As New ADODB.recordSet
    If mIsOpen Then
        rs.CursorLocation = adUseClient
        rs.Open strSQL, mConn, adOpenForwardOnly, adLockReadOnly
    End If
    
    Set ExecuteRecordset = rs
End Function

'--------------------------------------------------------
'[Function name]:  BenginTrans
'[Description]:    begin trans
'--------------------------------------------------------
Public Sub BeginTrans()
    
    If mIsOpen Then
        mConn.BeginTrans
        mIsBeginTran = True
    End If
End Sub

'--------------------------------------------------------
'[Function name]:  CommitTrans
'[Description]:    commint trans
'--------------------------------------------------------
Public Sub CommitTrans()
    
    If mIsOpen And mIsBeginTran Then
        mConn.CommitTrans
        mIsBeginTran = False
    End If
End Sub

'--------------------------------------------------------
'[Function name]:  RollbankTrans
'[Description]:    rollback trans
'--------------------------------------------------------
Public Sub RollbackTrans()
    
    If mIsOpen And mIsBeginTran Then
        mConn.RollbackTrans
        mIsBeginTran = False
    End If
End Sub

'--------------------------------------------------------
'[Function name]:  OleDbConnectionString
'[Description]:    create DB conn string
'[Parameter]:    (1)server (2)database (3)username (4)password
'[Return Value]:  DB conn string
'--------------------------------------------------------
Private Function OleDbConnectionString(ByVal Server As String, ByVal Database As String, _
    ByVal Username As String, ByVal Password As String) As String

    If Username = "" Then
        OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
            & ";Initial Catalog=" & Database _
            & ";Integrated Security=SSPI;Persist Security Info=False;"
    Else
        OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
            & ";Initial Catalog=" & Database _
            & ";User ID=" & Username & ";Password=" & Password & ";"
    End If

End Function

'--------------------------------------------------------
'[Function name]:  Class_Initialize
'[Description]:    class initialize
'--------------------------------------------------------
Private Sub Class_Initialize()
    
End Sub

'--------------------------------------------------------
'[function name]:  Class_Initialize
'[description]:    terminate
'--------------------------------------------------------
Private Sub Class_Terminate()    
    Call Dispose
End Sub