Data Pegawai-Jusmin

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.IO;

namespace Kelompok
{
    public partial class DataPegawai : Form
    {
        MySqlCommand query;
        Connection sambung;
        MySqlDataAdapter adapter;
        string sql;
        string sql1;

        public DataPegawai()
        {
            InitializeComponent();
            tampilkan();
            jabatan();
            jekel();
            agama();
            EnableBut();
        }

        private void DataPegawai_Load(object sender, EventArgs e)
        {
            dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
            delete.Enabled = false;
            ubah.Enabled = false;
            picpath.Visible = false;
        }

        void tampilkan()
        {
            ViewData tampil = new ViewData();
            DataTable tabel = new DataTable();

            tabel = tampil.ViewPegawai();
            dataGridView1.DataSource = tabel;
        }

        void jabatan()
        {
            sambung = new Connection();
            try
            {
                sql = "select*from jabatan ";
                sambung.buka();
                query = new MySqlCommand(sql, sambung.Koneksi);
                MySqlDataReader dr = query.ExecuteReader();
                while (dr.Read())
                {
                    string skode = dr.GetString("kode");
                    kjab.Items.Add(skode);
                }

            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            sambung.tutup();
        }

        private void kjab_SelectedIndexChanged(object sender, EventArgs e)
        {
            sambung = new Connection();
            try
            {
                sql = "select*from jabatan where kode = '" + kjab.Text + "'";
                sambung.buka();
                query = new MySqlCommand(sql, sambung.Koneksi);
                MySqlDataReader dr = query.ExecuteReader();
                while (dr.Read())
                {
                    string snama = dr.GetString("nama");
                    jab.Text = snama;
                }

            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            sambung.tutup();
        }

        void jekel()
        {
            sambung = new Connection();
            try
            {
                sql = "select*from jekel ";
                sambung.buka();
                query = new MySqlCommand(sql, sambung.Koneksi);
                MySqlDataReader dr = query.ExecuteReader();
                while (dr.Read())
                {
                    string skode = dr.GetString("kode");
                    kgen.Items.Add(skode);
                }

            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            sambung.tutup();
        }

        private void kgen_SelectedIndexChanged(object sender, EventArgs e)
        {
            sambung = new Connection();
            try
            {
                sql = "select*from jekel where kode = '" + kgen.Text + "'";
                sambung.buka();
                query = new MySqlCommand(sql, sambung.Koneksi);
                MySqlDataReader dr = query.ExecuteReader();
                while (dr.Read())
                {
                    string snama = dr.GetString("nama");
                    gender.Text = snama;
                }

            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            sambung.tutup();
        }

        void agama()
        {
            sambung = new Connection();
            try
            {
                sql = "select*from agama ";
                sambung.buka();
                query = new MySqlCommand(sql, sambung.Koneksi);
                MySqlDataReader dr = query.ExecuteReader();
                while (dr.Read())
                {
                    string skode = dr.GetString("kode");
                    kaga.Items.Add(skode);
                }

            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            sambung.tutup();
        }

        private void kaga_SelectedIndexChanged(object sender, EventArgs e)
        {

            sambung = new Connection();
            try
            {
                sql = "select*from agama where kode = '" + kaga.Text + "'";
                sambung.buka();
                query = new MySqlCommand(sql, sambung.Koneksi);
                MySqlDataReader dr = query.ExecuteReader();
                while (dr.Read())
                {
                    string snama = dr.GetString("nama");
                    aga.Text = snama;
                }

            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            sambung.tutup();
        }

        private void save_Click(object sender, EventArgs e)
        {
            sambung = new Connection();
            sql = "insert into pegawai (id_pegawai, kode_jabatan, nama, jekel, kode_agama, tanggal_lahir, " +
                " alamat, telpon, foto) VALUES ('"+id.Text + "','" + kjab.Text + "','" + nama.Text + "','" + kgen.Text + "'," +
                "'" + kaga.Text + "','" + tgl.Value.ToString("yyyy-MM-dd") + "','" + almt.Text + "','" + tlp.Text + "', @img);"+
                "INSERT INTO kehadiran (id_pegawai) VALUES ('" + id.Text + "')";

            try
            {
                sambung.buka();
                //query = new MySqlCommand(sql, sambung.Koneksi);

                /*MemoryStream ms = new MemoryStream();
                pic.Image.Save(ms, pic.Image.RawFormat);
                byte[] images = ms.GetBuffer();
                query.Parameters.Clear();
                query.Parameters.Add(new MySqlParameter("?gambar", MySqlDbType.LongBlob)).Value = images;*/
                byte[] image = null;
                FileStream fs = new FileStream(this.picpath.Text, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);
                image = br.ReadBytes((int)fs.Length);
                query = new MySqlCommand(sql, sambung.Koneksi);
                query.Parameters.Add(new MySqlParameter("@img", image));
                query.ExecuteReader();

                /*adapter = new MySqlDataAdapter(query);
                query.ExecuteNonQuery();*/

                pic = null;
                picpath.Text = "";

                MessageBox.Show("Data Anda Berhasil Tersimpan !!!", "Information");
                tampilkan();
            }
            catch (Exception)
            {
                MessageBox.Show("Data Sudah Ada atau Gambar Belum di input");
            }
            sambung.tutup();
        }

        private void gbr_Click(object sender, EventArgs e)
        {
            OpenFileDialog f = new OpenFileDialog();
            f.Filter = "ALL Files|*.*|JPEG|*.jpg|Bitmaps|*.bmp|GIFs|*.gif";
            //if (f.ShowDialog() != DialogResult.Cancel)
            if (f.ShowDialog() == DialogResult.OK)
            {
                //string file = f.FileName;
                /*if (file != "")
                {
                    pic.ImageLocation = file;
                    pic.SizeMode = PictureBoxSizeMode.StretchImage;
                }*/
                string file = f.FileName.ToString();
                picpath.Text = file;
                pic.ImageLocation = file;
                pic.SizeMode = PictureBoxSizeMode.StretchImage;
            }
        }

        private void ubah_Click(object sender, EventArgs e)
        {
            sambung = new Connection();
            sql = "UPDATE pegawai SET kode_jabatan = '" + kjab.Text + "', nama = '" + nama.Text + "', jekel = '" + gender.Text + "', "+
                " kode_agama = '" + kaga.Text + "', tanggal_lahir = '" + tgl.Value.ToString("yyyy-MM-dd") + "', alamat = '" + almt.Text + "', "+
                " telpon = '" + tlp.Text + "', foto =@img WHERE id_pegawai = '" + id.Text + "'";

            try
            {
                sambung.buka();

                byte[] image = null;
                FileStream fs = new FileStream(this.picpath.Text, FileMode.Open, FileAccess.Read);
                BinaryReader br = new BinaryReader(fs);
                image = br.ReadBytes((int)fs.Length);
                query = new MySqlCommand(sql, sambung.Koneksi);
                query.Parameters.Add(new MySqlParameter("@img", image));

                adapter = new MySqlDataAdapter(query);
                query.ExecuteNonQuery();

                pic = null;

                MessageBox.Show("Data Anda Berhasil Terupdate !!!", "Information");
                tampilkan();
            }
            catch (Exception er)
            {
                //MessageBox.Show("Data Salah atau Gambar Belum di input","ERROR !!!");
                MessageBox.Show(er.Message);
            }
            sambung.tutup();
            delete.Enabled = false;
            ubah.Enabled = false;
        }

        private void delete_Click(object sender, EventArgs e)
        {
            sambung = new Connection();
            sql = "DELETE FROM pegawai WHERE id_pegawai = '" + dataGridView1.CurrentRow.Cells[0].Value + "'";

            try
            {
                sambung.buka();
                query = new MySqlCommand(sql, sambung.Koneksi);
                adapter = new MySqlDataAdapter(query);
                query.ExecuteNonQuery();

                dataGridView1.Refresh();
                MessageBox.Show("Data Berhasil Di Hapus !!!", "Informasi");
                tampilkan();
                query.Dispose();
            }
            catch (Exception er)
            {
                MessageBox.Show("Data Gagal Di Hapus !!!", "Informasi");
            }
            sambung.tutup();
            delete.Enabled = false;
            ubah.Enabled = false;
        }


        private void ulang_Click(object sender, EventArgs e)
        {
            foreach (Control c in groupBox1.Controls)
            {
                kjab.SelectedIndex = -1;
                kaga.SelectedIndex = -1;
                kgen.SelectedIndex = -1;
                if (c is TextBox || c is NumericUpDown)
                {
                    c.Text = "";
                }
            }
            foreach (Control c in groupBox2.Controls)
            {
                tgl.Value = DateTime.Now;
                if (c is TextBox || c is NumericUpDown || c is RichTextBox)
                {
                    c.Text = "";
                }
            }
        }

        void EnableDedit()
        {
            if (delete.Enabled == false || ubah.Enabled == false)
            {
                delete.Enabled = true;
                ubah.Enabled = true;
            }
        }

        void EnableBut()
        {
            save.Enabled = !string.IsNullOrWhiteSpace(tlp.Text);
        }

        private void tlp_TextChanged(object sender, EventArgs e)
        {
            EnableBut();
        }

        private void dataGridView1_DoubleClick(object sender, EventArgs e)
        {

        }

        private void dataGridView1_CellMouseDoubleClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            EnableDedit();
            id.Enabled = false;
            DataGridViewRow row = new DataGridViewRow();
            row = dataGridView1.SelectedRows[0];
            if (row.Cells[0].Value != null && row.Cells[1].Value != null && row.Cells[2].Value != null)
            {
                id.Text = row.Cells[0].Value.ToString();
            }
        }

        private void perbarui_Click(object sender, EventArgs e)
        {
            id.Text = "";
            id.Enabled = true;
        }


    }
}