Module CRUD

Imports MySql.Data.MySqlClient
Module modCrud

    ''declaring a string
    Public result As String
    Public cmd As New MySqlCommand
    Public cmd2 As New MySqlCommand
    Public rdr As MySqlDataReader
    Public rdr2 As MySqlDataReader
    Public rdt As DataTable
    Public Sub cCall(ByVal strSql As String)

        Try
            With cmd2
                .Connection = conn
                .CommandText = strSql
                .CommandTimeout = 0
                'execute the data
                result = cmd2.ExecuteNonQuery
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Public Sub cInsert(ByVal strSql As String)

        Try
            With cmd2
                .Connection = conn
                .CommandTimeout = 0
                .CommandText = strSql

                'execute the data
                result = cmd2.ExecuteNonQuery
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    'for holding the data to retrieve.
    Public Sub cSelect(ByVal strSql As String)

        Try
            With cmd
                .Connection = conn
                .CommandText = strSql
                .CommandTimeout = 0
                rdr = cmd.ExecuteReader
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    'Fungsi Populate GridView
    'tanggal 18-08-16
    Public Sub PopDataGrid(ByVal strSql As String, gv As DataGridView)
        Using cmd As New MySqlCommand(strSql, conn)
            cmd.CommandType = CommandType.Text
            Using sda As New MySqlDataAdapter(cmd)
                Using dt As New DataTable()
                    sda.Fill(dt)
                    gv.DataSource = dt
                End Using
            End Using
        End Using
    End Sub
    'Fungsi Populate Listview
    'tanggal 18-08-16
    Public Sub PopDataLv(ByVal strSql As String, Lv As ListView)
        Using cmd As New MySqlCommand(strSql, conn)
            cmd.CommandType = CommandType.Text
            Using sda As New MySqlDataAdapter(cmd)
                Using dt As New DataTable()
                    sda.Fill(dt)
                    Dim itemctr As Integer
                    For itemctr = 0 To dt.Rows.Count - 1
                        Lv.Items.Add(dt.Rows(itemctr)(1))
                    Next
                End Using
            End Using
        End Using


    End Sub
    Sub FillListView(ByVal strSql As String, Lv As ListView)
        Using cmd As New MySqlCommand(strSql, conn)
            cmd.CommandType = CommandType.Text
            Using sda As New MySqlDataAdapter(cmd)
                Using ds As New DataSet()
                    sda.Fill(ds)

                    Dim c As DataColumn
                    For Each c In ds.Tables(0).Columns

                        'adding names of columns as Listview columns
                        Dim h As New ColumnHeader
                        h.Text = c.ColumnName
                        Lv.Columns.Add(h)
                    Next
                    Dim dt As DataTable = ds.Tables(0)
                    Dim str(ds.Tables(0).Columns.Count) As String
                    'adding Datarows as listview Grids
                    Dim rr As DataRow
                    For Each rr In dt.Rows
                        For col As Integer = 0 To ds.Tables(0).Columns.Count - 1

                            str(col) = rr(col).ToString()
                        Next
                        Dim ii As New ListViewItem(str)
                        Lv.Items.Add(ii)
                        'showing the number of records still added
                    Next

                End Using
            End Using
        End Using
    End Sub

    Public Sub cSelect2(ByVal strSql As String)
        Dim conn As New MySqlConnection
        Dim cmd As New MySqlCommand
        Try
            With cmd
                .Connection = conn
                .CommandText = strSql
                .CommandTimeout = 0
                rdr2 = cmd.ExecuteReader
            End With
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    'updating the data from the database.
    Public Sub cUpdate(ByVal strSql As String)

        Try
            With cmd
                .Connection = conn
                .CommandText = strSql
                .CommandTimeout = 0
                result = cmd.ExecuteNonQuery
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub

    'deleting the data from the database
    Public Sub cDelete(ByVal strSql As String)

        Try
            With cmd
                .Connection = conn
                .CommandText = strSql
                .CommandTimeout = 0
                result = cmd.ExecuteNonQuery
            End With

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

End Module