ModSQL.vb

Imports MySql.Data.MySqlClient

Module ModMySQL
Public con As MySqlConnection
    Public cmd As MySqlCommand
    Public dtr As MySqlDataReader
    Dim mydatatable As DataTable
    Dim adapter As MySqlDataAdapter
    Private bindingSource1 As New BindingSource()
    Private myHashRow As Boolean

    Public Sub CloseDB()
        con = New MySqlConnection
        con.Close()
        con.Dispose()
    End Sub

    Public Function OpenDB() As Boolean
        con = New MySqlConnection
        Dim host As String
        host = "localhost"
        con.ConnectionString = "server=" & host & ";" _
            & "uid=root;" _
            & "pwd=;" _
            & "database=nicemoneydb;"
        Try
            con.Open()
            Return True
        Catch ex As Exception
            MsgBox("Terjadi Error : " & ex.Message)
            Return False
        End Try
    End Function

    Public Sub ComboData(ByVal sql As String, ByVal obj As Object)
        OpenDB()
        cmd = New MySqlCommand(sql, con)
        Try

            dtr = cmd.ExecuteReader
            While dtr.Read()
                obj.Items.Add(dtr.Item(0).ToString)
            End While

        Catch ex As Exception
            MsgBox("Terjadi Error : " & ex.Message)
        Finally
            con.Close()
            con = Nothing
        End Try


    End Sub

    Public Sub TableData(ByVal sql As String, ByVal obj As Object)
        OpenDB()
        cmd = New MySqlCommand(sql, con)

        Try
            'obj.DataSource = Nothing
            dtr = cmd.ExecuteReader
            obj = New DataTable
            obj.Load(dtr)

        Catch ex As Exception
            MsgBox("Terjadi Error : " & ex.Message)
        Finally
            dtr.Close()
            con.Close()
        End Try

    End Sub

    Public Function DataToValue(ByVal sql As String) As String

        OpenDB()
        cmd = New MySqlCommand
        Try
            Dim p As String
            p = ""
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sql
            dtr = cmd.ExecuteReader
            If dtr.HasRows Then
                dtr.Read()
                p = (dtr.Item(0))
            End If
            Return p.ToString
        Catch ex As Exception
            Return ""
        Finally
            'dtr.Close()
            con.Close()
        End Try

    End Function

    Public Sub RunSQL(ByVal sql As String)
        OpenDB()
        cmd = New MySqlCommand(sql, con)
        Try

            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message)
        Finally
            con.Close()
        End Try
    End Sub

    Public Sub BindingView(ByVal sql As String, ByVal grid As DataGridView)
        OpenDB()


        Try
            cmd = New MySqlCommand(sql, con)
            adapter = New MySqlDataAdapter(cmd)
            With grid
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCells
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.ColumnHeader
            End With

            Dim commandBuilder As New MySqlCommandBuilder(adapter)
            Dim table As New DataTable()

            table.Locale = System.Globalization.CultureInfo.InvariantCulture

            adapter.Fill(table)

            bindingSource1.DataSource = table

            grid.DataSource = bindingSource1

            grid.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke

            grid.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

            Dim data As New DataSet()

            data.Locale = System.Globalization.CultureInfo.InvariantCulture

        Catch ex As Exception

        End Try
    End Sub

    Function _isBOF(ByVal sqlScript As String) As Boolean
        OpenDB()
        cmd = New MySqlCommand
        Dim p As String
        Try
            cmd.Connection = con
            cmd.CommandType = CommandType.Text
            cmd.CommandText = sqlScript
            dtr = cmd.ExecuteReader
            If dtr.HasRows Then
                dtr.Read()
                p = (dtr.Item(0))
                If p = "" Then
                    Return False
                Else
                    Return True
                End If

            End If
        Catch ex As MySqlException
            MsgBox(ex.Message)
        End Try
    End Function
End Module