'Codé par Rixy
'Classe pour facilité l'utilisation du MySQL en VB.Net
'Merci de ne pas retirer ceci =)

Imports MySql.Data.MySqlClient

Public Class SQLManager

    Private Connection As MySqlConnection
    Private State As ConnectionState = ConnectionState.Closed

    Private User As String = ""
    Private PassWord As String = ""
    Private Host As String = ""

#Region "Delegates"



#End Region

    Sub New(ByVal IP As String, ByVal User As String, ByVal PassWord As String, ByVal BDD As String, ByVal Port As Integer)
        Me.User = User
        Me.PassWord = PassWord
        Me.Host = IP
        If BDD = "" Then 'En Master
            Connection = New MySqlConnection("Server=" & IP & ";Uid=" & User & ";Pwd=" & PassWord & ";")
        ElseIf Port > 0 Then
            Connection = New MySqlConnection("Server=" & IP & ";Uid=" & User & ";Pwd=" & PassWord & ";Database=" & BDD & ";Port=" & Port & ";")
        Else
            Connection = New MySqlConnection("Server=" & IP & ";Uid=" & User & ";Pwd=" & PassWord & ";Database=" & BDD & ";")
        End If
    End Sub

#Region "Quickly Functions"

    Public Function getListDatabase() As Object
        Dim o(4500000) As Object
        Dim c As Integer = 0
        Dim r1 As MySql.Data.MySqlClient.MySqlDataReader = ExecuteQueryWithResult("SHOW DATABASES")
        While r1.Read
            o(c) = r1("database")
            c += 1
        End While
        r1.Close()
        Return o
    End Function

    Public Function getListTable(ByVal FromDatabase As String) As Object
        Dim o(4500000) As Object
        Dim c As Integer = 0
        Dim TempManager As New SQLManager(Host, User, PassWord, FromDatabase, 0)
        TempManager.Connect()
        Dim r1 As MySql.Data.MySqlClient.MySqlDataReader = TempManager.ExecuteQueryWithResult("SHOW TABLES")
        While r1.Read
            Dim n2 As String = FromDatabase
            n2 = n2.Replace(" ", "_")
            o(c) = r1("Tables_in_" & n2)
            c += 1
        End While
        r1.Close()
        TempManager.Disconnect()
        Return o
    End Function

#End Region

#Region "Fonctions"

    Public Function isOpen() As Boolean
        Dim result As Boolean = False
        If Connection.State = ConnectionState.Open Then
            result = True
        End If
        Return result
    End Function

    Public Function getState() As ConnectionState
        Return State
    End Function

    Public Function getConnection() As MySqlConnection
        Return Connection
    End Function

    Public Function ExecuteQueryWithResult(ByVal Query As String) As MySqlDataReader
        If Not State = ConnectionState.Open Then Return Nothing
        Dim SQLCommand As New MySqlCommand(Query, Connection)
        Dim Result As MySqlDataReader = SQLCommand.ExecuteReader
        Return Result
    End Function

#End Region

#Region "Sub"

    Public Sub Connect()
        Try
            State = ConnectionState.Connecting
            Connection.Open()
            State = ConnectionState.Open
        Catch ex As Exception
            MsgBox(ex.Message)
            State = ConnectionState.Closed
        End Try
    End Sub

    Public Sub Disconnect()
        Try
            Connection.Close()
            State = ConnectionState.Closed
        Catch ex As Exception
            MsgBox(ex.Message)
            State = ConnectionState.Closed
        End Try
    End Sub

    Public Sub ExecuteQuery(ByVal SQLTexte As String)
        Try
            'SQLTexte = SQLTexte.Replace(";", "")
            State = ConnectionState.Executing
            Dim SQLCommand As New MySqlCommand(SQLTexte, Connection)
            SQLCommand.ExecuteNonQuery()
            State = ConnectionState.Open
        Catch ex As Exception
            MsgBox(ex.Message)
            State = ConnectionState.Open
        End Try
    End Sub

    Public Sub CreateDatabase(ByVal Database As String)
        Try
            Dim TempManager As New SQLManager(Host, User, PassWord, "", 0)
            TempManager.Connect()
            TempManager.ExecuteQuery("CREATE DATABASE " & Database)
            TempManager.Disconnect()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub DeleteDatabase(ByVal Database As String)
        Try
            ExecuteQuery("DROP DATABASE " & Database)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    Public Sub ChangeColumnName(ByVal Database As String, ByVal TableName As String, ByVal OldColumnName As String, ByVal NewColumnName As String)
        Dim TempManager As New SQLManager(Host, User, PassWord, Database, 0)
        TempManager.Connect()
        TempManager.ExecuteQuery("ALTER TABLE " & TableName & " CHANGE " & OldColumnName & " " & NewColumnName & " VARCHAR (50)")
        TempManager.Disconnect()
    End Sub

#End Region

End Class