Imports System.Data.OleDb
Imports System.Configuration.ConfigurationSettings
Imports Mediatheek.Data

Public Class clsDatabase
    '-- Enumerations --
    '-- Fields --
    '-- Properties --
    Private Shared ReadOnly Property ConnectionString() As String
        Get
            Return System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
        End Get
    End Property
    '-- Constructors --
    Private Sub New()
    End Sub
    '-- Methoden --
    'Connection maken en openen
    Private Shared Function GetConnection() As OleDbConnection
        Dim oCon As OleDbConnection
        oCon = New OleDbConnection(ConnectionString)
        oCon.Open()
        Return oCon
    End Function
    'De connection sluiten en het object vrijgeven
    Private Shared Sub ReleaseConnection(ByVal oCon As OleDbConnection)
        If Not oCon Is Nothing Then
            oCon.Close()    'Sluiten
            oCon.Dispose()  'Object vrij maken
        End If
    End Sub
    'Command maken op basis van bestaande connection, SQL en parameters
    Private Shared Function BuildCommand(ByVal oCon As OleDbConnection, ByVal sSQL As String, ByVal ParamArray dbParams() As OleDbParameter) As OleDbCommand
        '1. Creëren
        Dim oCommand As OleDbCommand = oCon.CreateCommand

        '2. Text plaatsen
        oCommand.CommandType = CommandType.Text
        oCommand.CommandText = sSQL

        '3. Parameters toevoegen
        For Each oPar As OleDbParameter In dbParams
            oCommand.Parameters.Add(oPar)
        Next

        '4. Returnen
        Return oCommand
    End Function
    'Command maken op basis van SQL en parameters
    Private Shared Function BuildCommand(ByVal sSQL As String, ByVal ParamArray dbParams() As OleDbParameter) As OleDbCommand
        '1. Open Connection ophalen

        Dim oCon As OleDbConnection
        oCon = GetConnection()

        '2. Command object ophalen en returnen
        Return BuildCommand(oCon, sSQL, dbParams)
    End Function
    'DataTable ophalen
    Public Shared Function GetDT(ByVal sSQL As String, ByVal ParamArray dbParams() As OleDbParameter) As DataTable
        Dim oCom As OleDbCommand
        Try
            '1. Command object aanmaken
            oCom = BuildCommand( sSQL, dbParams)

            '2. DataAdapter aanmaken en er het Command-object aan doorgeven
            Dim oDA As New OleDbDataAdapter
            oDA.SelectCommand = oCom

            '3. De data selecteren en naar een datatable overbrengen
            Dim oDT As New DataTable
            oDA.Fill(oDT)

            '4. De datatable returnen
            Return oDT
        Finally
            'Connectie sluiten
            ReleaseConnection(oCom.Connection)
        End Try
    End Function
    'DataReader ophalen
    Public Shared Function GetDR(ByVal sSQL As String, ByVal ParamArray dbParams() As OleDbParameter) As OleDbDataReader
        '1. Command object aanmaken
        Dim oCom As OleDbCommand = BuildCommand(sSQL, dbParams)

        '2. Select op de database uitvoeren
        Dim oDR As OleDbDataReader
        oDR = oCom.ExecuteReader(CommandBehavior.CloseConnection)

        '3. De Reader returnen
        Return oDR
    End Function
    'Select met maar 1 resultaat
    Public Shared Function ExecuteScalar(ByVal sSQL As String, ByVal ParamArray dbParams() As OleDbParameter) As Object
        Dim oCommand As OleDbCommand
        Try
            '1. Command object aanmaken
            oCommand = BuildCommand(sSQL, dbParams)

            '2. SQL op DB uitvoeren
            Dim oObject As Object = oCommand.ExecuteScalar()

            '3. Resultaat returnen
            Return oObject
        Finally
            '3.Connectie sluiten
            ReleaseConnection(oCommand.Connection)
        End Try

    End Function
    'SQL zonder terugkeer resultaat uitvoeren
    Public Shared Sub ExecuteSQL(ByVal sSQL As String, ByVal ParamArray dbParams() As OleDbParameter)

        Dim oCommand As OleDbCommand
        Try
            '1. Command object aanmaken
            oCommand = BuildCommand(sSQL, dbParams)

            '2. SQL op DB uitvoeren
            oCommand.ExecuteNonQuery()
        Finally
            '3.Connectie sluiten
            ReleaseConnection(oCommand.Connection)
        End Try
    End Sub

    '-- Events --
End Class
