RSS

Module CRUD MySql dengan VB.NET

CRUD (CREATE, READ, UPDATE, DELETE) merupakan bagian yang tidak mungkin terpisahkan dari pemrograman, apalagi jika program yang digunakan sangat tergantung kepada database. berikut adalah module CRUD VB.NET dangan database MYSQL

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

    'INSERT
    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

    'SELECT
    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

    'UPDATE
    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

    'DELETE
    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


    'SELECT DATA untuk Gridview
    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

    'SELECT DATA untuk Listview1
    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

    'SELECT DATA untuk Listview2
    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

End Module

==========================================
Untuk penggunaannya tinggal panggil fungsi tersebut, contoh 

      cSELECT("SELECT * FROM data)
      Do While rdr.read()
             txtData1.text = rdr.item("data1")
             txtData2.text = rdr.item("data2")
      loop
      rdr.close()

==========================================

  • Digg
  • Del.icio.us
  • StumbleUpon
  • Reddit
  • RSS

0 comments:

Posting Komentar