Class Crud

Imports System.Data.SqlClient
Public Class Crud
    Dim db As New Database

    Public Sub create(nm_dpn As String, nm_blk As String)
        db.koneksi()
        Dim query = "INSERT INTO ex (nama_depan, nama_belakang) VALUES (@nm_dpn, @nm_blk)"
        Dim cmd As New SqlCommand(query, db.konek)

        cmd.Parameters.Add("@nm_dpn", SqlDbType.VarChar).Value = nm_dpn
        cmd.Parameters.Add("@nm_blk", SqlDbType.VarChar).Value = nm_blk

        If cmd.ExecuteNonQuery Then
            MsgBox("Data tersimpan!", vbInformation)
            Form1.txtNmDpn.Text = ""
            Form1.txtNmBlk.Text = ""
            db.konek.Close()
            tampil() 'untuk merefresh datagridview ketika data baru ditambahkan
        Else
            MsgBox("Gagal disimpan!", vbCritical)
        End If
    End Sub

    Public Sub tampil()
        db.koneksi()
        Dim query = "SELECT * FROM ex"
        Dim da As New SqlDataAdapter(query, db.konek)
        Dim dt As New DataTable
        da.Fill(dt)

        Form1.dgvData.DataSource = dt
    End Sub

    Public Sub hapus(id As Integer)
        db.koneksi()
        Dim query = "DELETE FROM ex WHERE id = @id"
        Dim cmd As New SqlCommand(query, db.konek)

        cmd.Parameters.Add("@id", SqlDbType.Int).Value = id

        If cmd.ExecuteNonQuery Then
            MsgBox("Data terhapus!", vbInformation)
            db.konek.Close()
            tampil()
        Else
            MsgBox("Gagal menghapus data!", vbCritical)
        End If
    End Sub

    Public Sub getDataById(id As Integer)
        db.koneksi()
        Dim query = "SELECT * FROM ex WHERE id = @id"
        Dim cmd As New SqlCommand(query, db.konek)

        cmd.Parameters.Add("@id", SqlDbType.Int).Value = id

        Dim er = cmd.ExecuteReader
        er.Read()

        If er.HasRows Then
            ubah.txtNmDpn.Text = er(1)
            ubah.txtNmBlk.Text = er(2)
        Else
            MsgBox("Data tidak ditemukan!", vbInformation)
        End If
    End Sub

    Public Sub update(nm_dpn As String, nm_blk As String, id As Integer)
        db.koneksi()
        Dim query = "UPDATE ex SET nama_depan = @nm_dpn, nama_belakang = @nm_blk WHERE id = @id"
        Dim cmd As New SqlCommand(query, db.konek)

        cmd.Parameters.Add("@nm_dpn", SqlDbType.VarChar).Value = nm_dpn
        cmd.Parameters.Add("@nm_blk", SqlDbType.VarChar).Value = nm_blk
        cmd.Parameters.Add("@id", SqlDbType.Int).Value = id

        If cmd.ExecuteNonQuery Then
            MsgBox("Update berhasil!", vbInformation)
            db.konek.Close()
            ubah.Close()
            tampil()
        Else
            MsgBox("Update gagal!", vbCritical)
        End If
    End Sub
End Class