'******************************************************
' *
' * Name:         clsAdoConn
' *
' * Design Phase:
' *     Author:   John Miner
' *     Date:     07/01/2008
' *     Purpose:  A class to handle ADO operations.
' *
' ******************************************************

' Define all variables
Option Explicit


' Define module constants - locks
Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3
Const adLockBatchOptimistic = 4


' Define module constants - cursors
Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3


'
' Define the base class
'

Class clsAdoConn


    ' -- Data element 1 --
    Private strConn
        
    Public Property Get ConnStr
        ConnStr = strConn
    End Property
    
    Public Property Let ConnStr(varConn)
        strConn = varConn
    End Property


    ' -- Data element 2 --
    Private intConnTimeout
        
    Public Property Get ConnTimeout
        ConnTimeout = intConnTimeout
    End Property
    
    Public Property Let ConnTimeout(varConnTimeout)
        intConnTimeout = varConnTimeout
    End Property


    ' -- Data element 3 --
    Private intCmdTimeout
        
    Public Property Get CmdTimeout
        CmdTimeout = intCmdTimeout
    End Property
    
    Public Property Let CmdTimeout(varCmdTimeout)
        intCmdTimeout = varCmdTimeout
    End Property

    
    ' -- Data element 4 - Connection object --
    Private objConn


    ' -- Data element 5 - Command object --
    Private objCmd

    
    ' -- Data element 6 - Recordset object --
    Private objRecSet 


    ' -- Record counter --
    Public REC
    Public EOF


    '
    ' A - initialize the class
    '
    
    Private Sub Class_Initialize()
    
        ' Set default values
        intConnTimeout = 120
        intCmdTimeout = 30

        ' Create a Connection object
        Set objConn = CreateObject("ADODB.Connection")

        ' Create a Command object
        Set objCmd = CreateObject("ADODB.Command")

        ' Create a Recordset object
        Set objRecSet = CreateObject("ADODB.Recordset")
        
    End Sub


    '
    ' B - destroy the class
    '
    
    Private Sub Class_Terminate()

       ' Release the object
       Set objConn = Nothing

       ' Release the object
       Set objCmd = Nothing
           
       ' Release the object
       Set objRecSet = Nothing
       
    End Sub


    '
    ' C - Open the database
    '
    Public Sub OpenDatabase()

        ' Connection timeout value
	objConn.ConnectionTimeout = intConnTimeout

        ' Command timeout value
	objConn.CommandTimeout = intCmdTimeout

        ' Set connection string
        objConn.ConnectionString = strConn

        ' Open the connection
	objConn.Open 

    End Sub


    '
    ' D - close the database
    '
    Public Sub CloseDatabase()

        ' Close the connection
	objConn.Close
     
    End Sub


    '
    ' E - open the recordset
    '
    Public Sub OpenRecSet(strSqlStmt)

       ' Open record set	
       objRecSet.ActiveConnection = objConn

       ' Set lock type
       objRecSet.LockType = 2

       ' Set the source
       objRecSet.Source = strSqlStmt

       ' Open the record set
       objRecSet.Open

       ' Set record counter
       REC = 0

       ' Set end of file marker
       EOF = False

    End Sub


    '
    ' F - close the recordset
    '
    Public Sub CloseRecSet()

        ' Close the recordset
	objRecSet.Close

    End Sub


    '
    ' G - read from the recordset
    '
    Public Sub ReadRecSet(aryData)

        ' File does not exist?
        If EOF Then
            Exit Sub
        End If

        ' Start of data?
        If REC = 0 Then
            objRecSet.MoveFirst
          
        ' Middle of data?    
        Else
            objRecSet.MoveNext
        End If

        ' Increment counter
        REC = REC + 1

        ' End of data!
        If objRecSet.EOF or objRecSet.BOF Then

            ' Set flags
            EOF = True
            REC = -1

            ' All done
            Exit Sub

        End If

        ' Temporary variables
        Dim intCnt
        Dim intSize

        ' Move data to dynamic array
        intSize = objRecSet.Fields.Count
        If intSize > 0 Then        
            Redim aryData(intSize)
            For intCnt = 0 to intSize - 1
                aryData(intCnt) = objRecSet(intCnt)
            Next
        End If

    End Sub


    '
    ' H - execute a stmt, return # affected rows
    '
    Public Function ExecNonQuery(strSqlStmt)

        ' Declare local variables
        Dim intRecs

        ' Set the connection
        objCmd.ActiveConnection = objConn

        ' Set the text
        objCmd.CommandText = strSqlStmt

        ' Always text
        objCmd.CommandType = 1

        ' Get number of records effected
        objCmd.Execute intRecs

        ' Return that value
        ExecNonQuery = intRecs

    End Function


    '
    ' I - execute a stmt, return just the specified field
    '
    Public Function ExecScalar(strSqlStmt, strFieldName)

        ' Declare local variables
        Dim objRecTemp

        ' Set the connection
        objCmd.ActiveConnection = objConn

        ' Set the text
        objCmd.CommandText = strSqlStmt

        ' Always sql text stmts
        objCmd.CommandType = 1

        ' Get the resulting record set
        Set objRecTemp = objCmd.Execute

        ' Return the value
        If objRecTemp.EOF and objRecTemp.BOF Then
            ExecScalar = ""
        Else
            ExecScalar = objRecTemp(strFieldName)
        End If

        ' Release the object
        objRecTemp.Close
        Set objRecTemp = nothing

    End Function


    '
    ' J - write to the recordset
    '
    Public Sub WriteRecSet(aryFields, aryValues)

        ' Move to the first record
        objRecSet.AddNew aryFields, aryValues
          
        ' Increment counter
        REC = REC + 1
 
    End Sub


End Class