Month: May 2008

Enum SQLExecuteMode
    save rollback
End Enum

Public Class objSQLDB
    Private _SQL As String
    Private _OverrideConnectionString As String
    Private _cmd As New SqlCommand

    Public Property SQL() As String
        Get
            Return _SQL
        End Get
        Set(ByVal value As String)
            _SQL = value
        End Set
    End Property

    Public Property OverrideConnectionString() As String
        Get
            Return _OverrideConnectionString
        End Get
        Set(ByVal value As String)
            _OverrideConnectionString = value
        End Set
    End Property

    Public Property SQLCommand() As SqlCommand
        Get
            Return _cmd
        End Get
        Set(ByVal value As SqlCommand)
            _cmd = value
        End Set
    End Property

    Public Function RetrieveDataTable() As DataTable
        'NOTE: Get proper ConnectionString
        Dim strConnectionString As String = My.Settings.CHMS_DB
        If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString

        'NOTE: Create connection
        Dim MyConnection As New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = _cmd

        cmd.Connection = MyConnection
        cmd.CommandText = _SQL

        'NOTE: Fill DataTable with sql results
        Dim da As New SqlDataAdapter(cmd)
        Dim dt As New DataTable
        da.Fill(dt)

        'NOTE: Clear all objects not being returned from memory
        MyConnection = Nothing
        cmd = Nothing
        da = Nothing

        Return dt
    End Function

    Public Function ExecuteNonQuery(Optional ByVal executeMode As SQLExecuteMode = SQLExecuteMode.save) As Boolean
        'NOTE: Get proper ConnectionString
        Dim strConnectionString As String = My.Settings.CHMS_DB
        If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString

        'NOTE: Create connection
        Dim MyConnection As New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = _cmd

        MyConnection.Open() 'Connection must be open prior to creation of transaction

        Dim myTransaction As SqlTransaction = MyConnection.BeginTransaction()

        cmd.Transaction = myTransaction
        cmd.Connection = MyConnection
        cmd.CommandText = _SQL

        'NOTE: Execute SQL
        Try
            cmd.ExecuteNonQuery()
            Select Case executeMode
                Case SQLExecuteMode.save
                    myTransaction.Commit()
                Case SQLExecuteMode.rollback
                    myTransaction.Rollback()
            End Select
        Catch ex As Exception
            myTransaction.Rollback()
            Throw New Exception("Error while attempting to write to database")
            Return False
        Finally
            MyConnection.Close()
            MyConnection = Nothing
            cmd = Nothing
        End Try

        Return True
    End Function

    Public Function ExecuteScalar() As Object
        'NOTE: Get proper ConnectionString
        Dim strConnectionString As String = My.Settings.CHMS_DB
        If String.IsNullOrEmpty(_OverrideConnectionString) = False Then strConnectionString = _OverrideConnectionString

        'NOTE: Create connection
        Dim MyConnection As New SqlConnection(strConnectionString)
        Dim cmd As SqlCommand = _cmd

        cmd.Connection = MyConnection
        cmd.CommandText = _SQL

        'NOTE: Retrieve Single Value
        Dim objSQLReturn As Object
        Try
            MyConnection.Open()
            objSQLReturn = cmd.ExecuteScalar()
            MyConnection.Close()
        Catch ex As Exception
            Throw New Exception("Error while attempting to retrieve value from database")
            Return False
        End Try

        'NOTE: Clear all objects not being returned from memory
        MyConnection = Nothing
        cmd = Nothing

        Return objSQLReturn
    End Function
End Class

Public Function getEmployeeReport(ByVal lngCompanyID as Integer, _
    ByVal dtmFilterBeginDate As DateTime, _
    ByVal dtmFilterEndDate As DateTime, _
    ByVal lngUserID As Integer) As DataTable

    objSQLDB.SQL = "dbo.report_amt_employee"
    objSQLDB.SQLCommand.CommandType = CommandType.StoredProcedure
    objSQLDB.SQLCommand.Parameters.AddWithValue("@lngCompanyID", lngCompanyID)
    objSQLDB.SQLCommand.Parameters.AddWithValue("@dtmFilterBeginDate", dtmFilterBeginDate)
    objSQLDB.SQLCommand.Parameters.AddWithValue("@dtmFilterEndDate", dtmFilterEndDate)
    objSQLDB.SQLCommand.Parameters.AddWithValue("@lngUserID", lngUserID)

    Return objSQLDB.RetrieveDataTable
End Function