tmp-dbmanager

Imports MySql.Data.MySqlClient

Public Class DBManager
    Private connectionString = "server=localhost;user id=root;password=;database=block3m4;"
    Private connect As MySqlConnection

    Public Sub New()
        Try
            Me.connect = New MySqlConnection(connectionString)
            Me.connect.Open()
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
        End Try
    End Sub

    Public Sub testConnection()
        If Me.connect.State = ConnectionState.Open Then
            MsgBox("I'm wide open, baby!")
        Else
            MsgBox("I'm NOT very open")
        End If
    End Sub

    Public Function getAllUsers()
        Dim query As String = "SELECT * FROM users WHERE status = 1"
        Dim dataset As New DataSet

        Try
            Dim cmd As New MySqlCommand(query, Me.connect)
            Dim adapter As New MySqlDataAdapter(cmd)

            adapter.Fill(dataset, "users")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
        End Try

        Return dataset.Tables("users").Rows
    End Function

    Public Function getAllUsersList() As List(Of UserBean)
        Dim query As String = "SELECT * FROM users WHERE status = 1"
        Dim dataset As New DataSet
        Dim userlist As New List(Of UserBean)

        Try
            Dim cmd As New MySqlCommand(query, Me.connect)
            Dim adapter As New MySqlDataAdapter(cmd)

            adapter.Fill(dataset, "users")

            For Each row As DataRow In dataset.Tables("users").Rows
                userlist.Add(New UserBean(row("id"), row("fname"), row("lname"), row("age"), row("gender"), row("status")))
            Next
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
        End Try

        Return userlist
    End Function

    Public Function getSingleUser(ByVal id As Integer) As UserBean
        Dim query As String = "SELECT * FROM users WHERE id = " & id
        Dim user = New UserBean()

        Try
            Dim cmd As New MySqlCommand(query, Me.connect)
            Dim reader As MySqlDataReader = cmd.ExecuteReader()

            If reader.Read() Then
                user.Id = reader.GetInt16(0)
                user.Firstname = reader.GetString(1)
                user.Lastname = reader.GetString(2)
                user.Age = reader.GetInt16(3)
                user.Gender = reader.GetString(4)
                user.Status = reader.GetBoolean(5)
            End If

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
        End Try

        Return user
    End Function

    Public Sub addUser(ByVal user As UserBean)
        Dim cmd As New MySqlCommand

        Try
            cmd.Connection = Me.connect
            cmd.CommandText = "INSERT INTO users VALUES(DEFAULT, @fname, @lname, @age, @gender, DEFAULT)"
            cmd.Parameters.AddWithValue("@fname", user.Firstname)
            cmd.Parameters.AddWithValue("@lname", user.Lastname)
            cmd.Parameters.AddWithValue("@age", user.Age)
            cmd.Parameters.AddWithValue("@gender", user.Gender)

            Dim rowAffected = cmd.ExecuteNonQuery()
            MsgBox("Row Affected : " & rowAffected)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
        End Try
    End Sub

    Public Sub editUser(ByVal user As UserBean)
        Dim cmd As New MySqlCommand

        Try
            cmd.Connection = Me.connect
            cmd.CommandText = "UPDATE users SET fname = @fname, lname = @lname, age = @age, gender = @gender WHERE id = @id"
            cmd.Parameters.AddWithValue("@fname", user.Firstname)
            cmd.Parameters.AddWithValue("@lname", user.Lastname)
            cmd.Parameters.AddWithValue("@age", user.Age)
            cmd.Parameters.AddWithValue("@gender", user.Gender)
            cmd.Parameters.AddWithValue("@id", user.Id)

            Dim rowAffected = cmd.ExecuteNonQuery()
            MsgBox("Row Affected : " & rowAffected)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
        End Try
    End Sub

    Public Sub deleteUser(ByVal id As Integer)
        Dim cmd As New MySqlCommand

        Try
            cmd.Connection = Me.connect
            cmd.CommandText = "UPDATE users SET status = 0 WHERE id = @id"
            cmd.Parameters.AddWithValue("@id", id)

            Dim rowAffected = cmd.ExecuteNonQuery()
            MsgBox("Row Affected : " & rowAffected)
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
        End Try
    End Sub


End Class