Make Modul Connection and CRUD (Create, Read, Update, Delete) Vb.Net

 Make Modul Connection and CRUD (Create, Read, Update, Delete) Vb.Net

Untuk modul koneksi

Imports MySql.Data.MySqlClient

Imports System.Data.DataTable

Module koneksi

    Public dbconn As New MySqlConnection

    Public sql As String

    Public dbcomm As MySqlCommand

    Public dbread As MySqlDataReader

    Public dbdata As MySqlDataAdapter

    Public ds As New DataSet

    Sub connectMysql()

        dbconn = New MySqlConnection("server=localhost; UserId=root; password=; database=bsm ")

        Try

            If dbconn.State = ConnectionState.Closed Then

                dbconn.Open()

            End If

        Catch ex As Exception

            MsgBox("DATABASE GAGAL TERHUBUNG" + vbNewLine + ex.Message)

        End Try

    End Sub

End Module

  

Untuk form Load

Private Sub siswa_Load(sender As Object, e As EventArgs) Handles Me.Load

        Call connectMysql()

        Call Tampil()

        Call DisableAllText()

    End Sub

Untuk memanggil sub tampil agar bisa tampil di datagrid

Private Sub Tampil()

        Dim mycommand As New MySqlCommand

        Dim myAdapter As New MySqlDataAdapter

        Dim myData As New DataTable

        Try

            sql = "select * from siswa"

            mycommand.Connection = dbconn

            mycommand.CommandText = sql

            myAdapter.SelectCommand = mycommand

            myAdapter.Fill(myData)

            dgvsiswa.DataSource = myData

            dgvsiswa.Columns(0).HeaderText = "NIS"

            dgvsiswa.Columns(1).HeaderText = "Nama"

            dgvsiswa.Columns(2).HeaderText = "Kelas"

            dgvsiswa.Columns(3).HeaderText = "Nama Orang Tua"

            dgvsiswa.Columns(4).HeaderText = "Alamat"

            dgvsiswa.Columns(5).HeaderText = "Pekerjaan Orang Tua"

            dbconn.Close()

        Catch ex As Exception

            MessageBox.Show("Error:" & ex.Message)

        Finally

            dbconn.Dispose()

        End Try

    End Sub

Untuk button tambah

Private Sub btntambah_Click(sender As Object, e As EventArgs) Handles btntambah.Click

        If btntambah.Text = "Batal" Then

            Call DisableAllText()

            btntambah.Text = "Tambah"

        Else

            Call EnableAllText()

            btntambah.Text = "Batal"

            btnhapus.Enabled = False

            btnubah.Enabled = False

        End If

    End Sub

Untuk button simpan

Private Sub btnsimpan_Click(sender As Object, e As EventArgs) Handles btnsimpan.Click

        Dim myCommand As New MySqlCommand

        Try

            sql = "INSERT INTO siswa (NIS, nama, kelas, nama_ortu, alamat, pekerjaan, tahun) VALUES " & _

    "('" & txtnis.Text & "', '" & txtnama.Text & "', '" & cmbkelas.Text & "', '" & txtortu.Text & "', '" & txtalamat.Text & "' , '" & txtkerja.Text & "', '" & cmbtahun.Text & "')"

            Call connectMysql()

            myCommand.Connection = dbconn

            myCommand.CommandText = sql

            myCommand.ExecuteNonQuery()

            Tampil()

            dbconn.Close()

            MsgBox("data berhasil disimpan", MsgBoxStyle.Information, "Informasi")

        Catch ex As Exception

            MessageBox.Show("Error:" & ex.Message)

        Finally

            dbconn.Dispose()

        End Try

        Call CleanAllText()

        Call DisableAllText()

        btnubah.Enabled = True

        btnhapus.Enabled = True

        btntambah.Text = "Tambah"

        btntambah.Enabled = True

    End Sub

Untuk button edit

Private Sub btnubah_Click(sender As Object, e As EventArgs) Handles btnubah.Click

        Dim myCommand As New MySqlCommand

        Try

            sql = "UPDATE siswa SET nama = '" & txtnama.Text & "'," & _

                    "kelas = '" & cmbkelas.Text & "'," & _

                    "nama_ortu = '" & txtortu.Text & "'," & _

                    "alamat = '" & txtalamat.Text & "'," & _

                    "pekerjaan = '" & txtkerja.Text & "'," & _

                    "tahun= '" & cmbtahun.Text & "'" & _

                   "WHERE NIS = '" & txtnis.Text & "'"

            ' dbconn.Open()

            Call connectMysql()

            myCommand.Connection = dbconn

            myCommand.CommandText = sql

            myCommand.ExecuteNonQuery()

            Tampil()

            dbconn.Close()

            MsgBox("data berhasil diedit", MsgBoxStyle.Information, "Informasi")

        Catch ex As Exception

            MessageBox.Show("Error:" & ex.Message)

        Finally

            dbconn.Dispose()

        End Try

        btntambah.Enabled = True

        btnsimpan.Enabled = True

        Call CleanAllText()

        Call DisableAllText()

    End Sub

Sebelum edit klik 2x di datagridview

Private Sub dgvsiswa_DoubleClick(sender As Object, e As EventArgs) Handles dgvsiswa.DoubleClick

        txtnis.Text = dgvsiswa.CurrentRow.Cells(0).Value

        txtnama.Text = dgvsiswa.CurrentRow.Cells(1).Value

        cmbkelas.Text = dgvsiswa.CurrentRow.Cells(2).Value

        txtortu.Text = dgvsiswa.CurrentRow.Cells(3).Value

        txtalamat.Text = dgvsiswa.CurrentRow.Cells(4).Value

        txtkerja.Text = dgvsiswa.CurrentRow.Cells(5).Value

        cmbtahun.Text = dgvsiswa.CurrentRow.Cells(6).Value

        Call EnableAllText()

        btntambah.Enabled = False

        btnsimpan.Enabled = False

        btnubah.Enabled = True

    End Sub

Untuk button hapus

Private Sub btnhapus_Click(sender As Object, e As EventArgs) Handles btnhapus.Click

        If MsgBox("Yakin akan menghapus data?", MsgBoxStyle.YesNo, "Konfirmasi") = MsgBoxResult.No Then Exit Sub

        Dim myCommand As New MySqlCommand

        Try

            sql = "DELETE FROM siswa WHERE NIS = '" & txtnis.Text & "'"

            'dbconn.Open()

            Call connectMysql()

            myCommand.Connection = dbconn

            myCommand.CommandText = sql

            myCommand.ExecuteNonQuery()

            MsgBox("Data Terhapus", MsgBoxStyle.Information, "Konfirmasi Sukses")

            Tampil()

            dbconn.Close()

            DisableAllText()

        Catch ex As Exception

            MessageBox.Show("Error:" & ex.Message)

        Finally

            dbconn.Dispose()

        End Try

        btntambah.Enabled = True

        btnsimpan.Enabled = True

        CleanAllText()

    End Sub

Untuk pencarian cukup sediakan textbox saja

Private Sub txtcari_TextChanged(sender As Object, e As EventArgs) Handles txtcari.TextChanged

        Call connectMysql()

        dbdata = New MySqlDataAdapter("select * from siswa where nama like '%" & txtcari.Text & "%'", dbconn)

        ds = New DataSet

        dbdata.Fill(ds)

        dgvsiswa.DataSource = ds.Tables(0)

    End Sub