Modules/Database/Adapters/ODBC.vb

Imports System.Data.Odbc
Imports System.Data
Imports NLog

Public Class ODBC
    Private _Logger As Logger

    Private _connectionDatasource As String
    Private _connectionUsername As String
    Private _connectionPassword As String
    Private _connectionString As String

    Public Sub New(Datasource As String, User As String, Password As String)
        Try
            _Logger = LogManager.GetCurrentClassLogger()

            _connectionDatasource = Datasource
            _connectionPassword = Password
            _connectionUsername = User
            _connectionString = GetConnectionString(Datasource, User, Password)

            _Logger.Debug("Connecting to database..")

            ' Test the connection
            Dim oConnection = GetConnection()

            If oConnection Is Nothing Then
                Throw New Exceptions.DatabaseException()
            End If

            ' If initial connection was successfully, close it
            oConnection.Close()

            _Logger.Debug("Connection sucessfully established!")
        Catch ex As Exception
            _Logger.Error(ex)
        End Try
    End Sub

    Public Function GetConnection() As OdbcConnection
        Try
            Dim oConnection As New OdbcConnection(_connectionString)
            oConnection.Open()

            Return oConnection
        Catch ex As Exception
            _Logger.Error(ex)
            Return Nothing
        End Try
    End Function

    Public Function GetConnectionString(Datasource As String, User As String, Password As String) As Object
        Return $"DSN={Datasource};UID={User};PWD={Password}"
    End Function

    ''' <summary>
    ''' Executes a non-query command.
    ''' </summary>
    ''' <param name="SqlQuery">The command to execute</param>
    ''' <param name="Connection">The Firebird connection to use</param>
    ''' <returns>True, if command was executed sucessfully. Otherwise false.</returns>
    Public Function ExecuteNonQueryWithConnection(SqlQuery As String, Connection As OdbcConnection) As Object
        _Logger.Debug("Fetching Non-Query: {0}", SqlQuery)

        Dim oResult As Object

        If Connection Is Nothing Then
            _Logger.Warn("Connection is nothing!")
            Return Nothing
        End If

        Try
            Dim oCommand As New OdbcCommand(SqlQuery, Connection)
            oResult = oCommand.ExecuteNonQuery()
        Catch ex As Exception
            _Logger.Error(ex, $"Error in ExecuteNonQueryWithConnection while executing command: '{SqlQuery}'")
            Throw ex
        End Try

        Return oResult
    End Function

    ''' <summary>
    ''' Executes a non-query command.
    ''' </summary>
    ''' <param name="SqlCommand">The command to execute</param>
    ''' <returns>True, if command was executed sucessfully. Otherwise false.</returns>
    Public Function ExecuteNonQuery(SqlCommand As String) As Boolean
        Dim oConnection As OdbcConnection = GetConnection()
        Dim oScalarValue As Object = ExecuteNonQueryWithConnection(SqlCommand, oConnection)
        oConnection.Close()

        Return oScalarValue
    End Function

    ''' <summary>
    ''' Executes a sql query resulting in a scalar value.
    ''' </summary>
    ''' <param name="SqlQuery">The query to execute</param>
    ''' <param name="Connection">The Firebird connection to use</param>
    ''' <returns>The scalar value if the command was executed successfully. Nothing otherwise.</returns>
    Public Function GetScalarValueWithConnection(SqlQuery As String, Connection As OdbcConnection) As Object
        _Logger.Debug("Fetching Datatable: {0}", SqlQuery)

        Dim oResult As Object

        If Connection Is Nothing Then
            _Logger.Warn("Connection is nothing!")
            Return Nothing
        End If

        Try
            Dim oCommand As New OdbcCommand(SqlQuery, Connection)
            oResult = oCommand.ExecuteScalar()
        Catch ex As Exception
            _Logger.Error(ex, $"Error in GetDatatableWithConnection while executing command: '{SqlQuery}'")
            Throw ex
        End Try

        Return oResult
    End Function

    ''' <summary>
    ''' Executes a sql query resulting in a table of values.
    ''' </summary>
    ''' <param name="SqlQuery">The query to execute</param>
    ''' <returns>A datatable containing the results if the command was executed successfully. Nothing otherwise.</returns>
    Public Function GetScalarValue(SqlQuery As String) As Object
        Dim oConnection As OdbcConnection = GetConnection()
        Dim oDatatable As DataTable = GetDatatableWithConnection(SqlQuery, oConnection)
        oConnection.Close()

        Return oDatatable
    End Function

    ''' <summary>
    ''' Executes a sql query resulting in a table of values.
    ''' </summary>
    ''' <param name="SqlQuery">The query to execute</param>
    ''' <param name="Connection">The Firebird connection to use</param>
    ''' <returns>A datatable containing the results if the command was executed successfully. Nothing otherwise.</returns>
    Public Function GetDatatableWithConnection(SqlQuery As String, Connection As OdbcConnection) As DataTable
        _Logger.Debug("Fetching Datatable: {0}", SqlQuery)

        Dim oDatatable As New DataTable() With {
            .TableName = "DDRESULT"
        }

        If Connection Is Nothing Then
            _Logger.Warn("Connection is nothing!")
            Return Nothing
        End If

        Try

            Dim oAdapter As New OdbcDataAdapter(SqlQuery, Connection)
            oAdapter.Fill(oDatatable)
        Catch ex As Exception
            _Logger.Error(ex, $"Error in GetDatatableWithConnection while executing command: '{SqlQuery}'")
            Throw ex
        End Try

        Return oDatatable
    End Function

    ''' <summary>
    ''' Executes a sql query resulting in a table of values.
    ''' </summary>
    ''' <param name="SqlQuery">The query to execute</param>
    ''' <returns>A datatable containing the results if the command was executed successfully. Nothing otherwise.</returns>
    Public Function GetDatatable(SqlQuery As String) As DataTable
        Dim oConnection As OdbcConnection = GetConnection()
        Dim oDatatable As DataTable = GetDatatableWithConnection(SqlQuery, oConnection)
        oConnection.Close()

        Return oDatatable
    End Function
End Class