Tutorial CRUD C# Sederhana dengan MySql – Studi Kasus Pendataan Nilai Siswa

public MySqlCommand cmd;
public MySqlDataReader dr;
public String id;

private void Tampil()
{
    try
    {

      Koneksi.conn.Open(); 
      MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM `vnilai`", Koneksi.conn);
      DataSet ds = new DataSet();
      da.Fill(ds);
      dgsiswa.DataSource = ds.Tables[0];
      Koneksi.conn.Close();
    }
      catch (Exception)
    {
       MessageBox.Show("Duh!!, Ada Error Nih");
    }
}

private void Nilai_Load(object sender, EventArgs e)
{
    Tampil();

    MySqlDataAdapter adp = new MySqlDataAdapter("select * from mapel", Koneksi.conn);
    DataTable dt = new DataTable();
    adp.Fill(dt);

    cbmapel.DataSource = dt;
    cbmapel.DisplayMember = "nama_mapel";
    cbmapel.ValueMember = "id";

    MySqlDataAdapter adp2 = new MySqlDataAdapter("select * from siswa", Koneksi.conn);
    DataTable dt2 = new DataTable();
    adp2.Fill(dt2);

    cbsiswa.DataSource = dt2;
    cbsiswa.DisplayMember = "nama";
    cbsiswa.ValueMember = "id";

    btDelete.Enabled = false;
    btUpdate.Enabled = false;
    btCancel.Enabled = false;

}

private void Clear()
{
    cbmapel.Text = "";
    cbsiswa.Text = "";
    txtnilai.Text = "";
    
    txtcari.Text = "";

    btDelete.Enabled = false;
    btUpdate.Enabled = false;
    btCancel.Enabled = false;
    btInsert.Enabled = true;
}

try
  {
      Koneksi.conn.Open();
      String Queri = "INSERT INTO nilai (`siswa_id`, `mapel_id`, `nilai`) VALUES ('" + cbsiswa.SelectedValue + "','" + cbmapel.SelectedValue + "', '" + txtnilai.Text + "')";
      cmd = new MySqlCommand(Queri, Koneksi.conn);
      cmd.ExecuteNonQuery();
      MessageBox.Show("Berhasil Simpan Nilai Siswa");
      Koneksi.conn.Close();

      Tampil();
      Clear();
  }
  catch (Exception)
  {

      MessageBox.Show("Tambah Data Gagal");
  }

btDelete.Enabled = true;
btUpdate.Enabled = true;
btCancel.Enabled = true;
btInsert.Enabled = false;

int baris = dgsiswa.CurrentCell.RowIndex;
id = dgsiswa.Rows[baris].Cells[0].Value.ToString();
cbsiswa.Text = dgsiswa.Rows[baris].Cells[1].Value.ToString();
cbmapel.Text = dgsiswa.Rows[baris].Cells[3].Value.ToString();
txtnilai.Text = dgsiswa.Rows[baris].Cells[5].Value.ToString();

try
{
    Koneksi.conn.Open();
   
    String Queri = "UPDATE `nilai` SET `siswa_id`='"+cbsiswa.SelectedValue+"', `mapel_id`='"+cbmapel.SelectedValue+"', `nilai`='"+txtnilai.Text+"' WHERE  `id`='"+id+"'";
    cmd = new MySqlCommand(Queri, Koneksi.conn);
    cmd.ExecuteNonQuery();
    MessageBox.Show("Berhasil Update Data Siswa");
    Koneksi.conn.Close();

    Tampil();
    Clear();
}
catch (Exception)
{

    MessageBox.Show("Update Gagal");
}

try
{
    Koneksi.conn.Open();


    cmd = new MySqlCommand("DELETE FROM nilai WHERE  `id`= '" + id + "'", Koneksi.conn);
    cmd.ExecuteNonQuery();
    MessageBox.Show("Berhasil Hapus Data Siswa");
    Koneksi.conn.Close();

    Tampil();
    Clear();
}
catch (Exception)
{

    MessageBox.Show("Hapus Data Gagal");
}

try
{

    Koneksi.conn.Open();
    MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM `vnilai` where nama LIKE '%" + txtcari.Text + "%'", Koneksi.conn);
    DataSet ds = new DataSet();

    da.Fill(ds);
    dgsiswa.DataSource = ds.Tables[0];
    Koneksi.conn.Close();
}
catch (Exception)
{

    MessageBox.Show("Duh!!, Ada Error Nih");
}

try
{

    Koneksi.conn.Open();
    MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM `vnilai` where nama LIKE '%" + txtcari.Text + "%'", Koneksi.conn);
    DataSet ds = new DataSet();

    da.Fill(ds);
    dgsiswa.DataSource = ds.Tables[0];
    Koneksi.conn.Close();
}
catch (Exception)
{

    MessageBox.Show("Duh!!, Ada Error Nih");
}