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

Public Class clsLoginDA

#Region "GetUsers"

    Public Shared Function GetUsers() As ArrayList

        Try
            '1. Select en Pars aanmaken
            Dim sSQL As String = "SELECT * FROM tblUser"

            '3. De gegevens selecteren en naar een DataTable overbrengen
            Dim oDataTable As DataTable = clsDatabase.GetDT(sSQL)

            '4. De gegegevens van de DataTable naar 
            '   een ArrayList van album-objecten overbrengen
            Dim arlUsers As ArrayList = New ArrayList
            Dim oRow As DataRow
            For Each oRow In oDataTable.Rows

                Dim iUserID As Integer
                Dim sUserName, sUserPassword As String


                iUserID = Convert.ToString(oRow.Item("UserID"))
                sUserName = Convert.ToString(oRow.Item("UserName"))
                sUserPassword = Convert.ToString(oRow.Item("UserPassword"))


                Dim oUser As New clsUser(iUserID, sUserName, sUserPassword)
                arlUsers.Add(oUser)
            Next

            '5. De ArrayList returnen
            Return arlUsers

        Catch ex As Exception
            Debug.WriteLine(ex.Message)

            Throw
        End Try
    End Function

    Public Shared Function GetUserById(ByVal iId As Integer)

        Dim oUser As clsUser
        For Each oUser In GetUsers()
            If oUser.UserID = iId Then
                Return oUser
            End If
        Next
    End Function
#End Region

#Region "Voeg User Toe"

    Public Shared Sub VoegUserToe(ByVal oUser As clsUser)

        Dim sValue As String
        sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
        Dim oCon As System.Data.OleDb.OleDbConnection
        oCon = New OleDbConnection(sValue)
        oCon.Open()

        Dim oCommand As OleDbCommand
        oCommand = oCon.CreateCommand()

        oUser.UserID = getVolgendUserId()
        Dim sInsert As String
        sInsert = "INSERT INTO tblUser(UserID,UserName,UserPassword)VALUES(?,?,?)"

        Dim oPar1 As New OleDbParameter("@UserID", oUser.UserID)
        Dim oPar2 As New OleDbParameter("@UserName", oUser.UserName)
        Dim oPar3 As New OleDbParameter("@UserPassword", oUser.UserPassword)
       
        oCommand.CommandText = sInsert
        oCommand.Parameters.Add(oPar1)
        oCommand.Parameters.Add(oPar2)
        oCommand.Parameters.Add(oPar3)
       
        oCommand.ExecuteNonQuery()
        oCon.Close()
    End Sub

    Private Shared Function getVolgendUserId() As Integer
        Try
            Dim sSQL As String = "SELECT MAX(UserID) FROM tblUser"
            Dim iMaxId As Integer
            Try
                iMaxId = Convert.ToInt32(clsDatabase.ExecuteScalar(sSQL))
            Catch
                iMaxId = 0

            End Try
            Return iMaxId + 1
        Finally
        End Try
    End Function

   

#End Region

#Region "Wijzig User"
    Public Shared Function WijzigUser(ByVal oUser As clsUser)

        Dim oCon As OleDbConnection
        Try
            Dim sValue As String
            sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
            Dim oCon2 As System.Data.OleDb.OleDbConnection
            oCon2 = New OleDbConnection(sValue)
            oCon2.Open()

            Dim oCommand2 As OleDbCommand
            oCommand2 = oCon2.CreateCommand()
            Dim sInsert As String

            sInsert = "UPDATE tblUser SET UserName=?, UserPassword=? WHERE UserID=?"


            Dim oPar1 As New OleDbParameter("@UserName", oUser.UserName)
            Dim oPar2 As New OleDbParameter("@UserPassword", oUser.UserPassword)
            Dim oPar3 As New OleDbParameter("@UserID", oUser.UserID)
            oCommand2.CommandText = sInsert
            oCommand2.Parameters.Add(oPar1)
            oCommand2.Parameters.Add(oPar2)
            oCommand2.Parameters.Add(oPar3)
            oCommand2.ExecuteNonQuery()
            oCon2.Close()


        Catch ex As Exception
            Debug.WriteLine(ex.Message)
            Throw
        Finally
            '4. Connectie vrijgeven
            If Not oCon Is Nothing Then
                oCon.Close()
                oCon.Dispose()
            End If
        End Try
    End Function

#End Region

#Region "Verwijder User"


    Public Shared Function VerwijderUser(ByVal iId As Integer)
        Dim oCon As OleDbConnection

        Try
            Dim sValue As String
            sValue = System.Configuration.ConfigurationSettings.AppSettings.Item("ConnectionString")
            Dim oCon3 As System.Data.OleDb.OleDbConnection
            oCon3 = New OleDbConnection(sValue)
            oCon3.Open()

            Dim oCommand3 As OleDbCommand
            oCommand3 = oCon3.CreateCommand()
            Dim sInsert As String
            

            sInsert = "DELETE FROM tblUser WHERE UserID=?"

            Dim oPar As New OleDbParameter("@UserID", iId)
            oCommand3.CommandText = sInsert
            oCommand3.Parameters.Add(oPar)

            '3. Uitvoeren op de DB
            oCommand3.ExecuteNonQuery()

            oCon3.Close()
        Catch ex As Exception
            Debug.WriteLine(ex.Message)
            Throw
        Finally
            '4. Connectie vrijgeven
            If Not oCon Is Nothing Then
                oCon.Close()
                oCon.Dispose()
            End If
        End Try
    End Function
#End Region



End Class
