namespace WindowsFormsApplication6
{
    public partial class Form10 : Form
    {
        MySqlConnection liga = new MySqlConnection("data source=localhost; database=pessoas; user id=root; pwd=''");
        MySqlCommand cmd;
        MySqlDataAdapter adapt;
        int id = 0;
        public Form10()
        {
            InitializeComponent();
            DisplayData();
        }
        private void DisplayData()
        {
            DataTable dt = new DataTable();
            adapt = new MySqlDataAdapter("select * from pessoas2015", liga);
            adapt.Fill(dt);
            dataGridView1.DataSource = dt;
            liga.Close();
        }

        private void ClearData()
        {
            textBox1.Text = "";
            textBox2.Text = "";
            maskedTextBox1.Text = "";
            dateTimePicker1.Text = "";
            id = 0;
        }

        private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            id  = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString());
            textBox1.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
            textBox2.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
            dateTimePicker1.Text = dataGridView1.Rows[e.RowIndex].Cells[3].Value.ToString();
            maskedTextBox1.Text = dataGridView1.Rows[e.RowIndex].Cells[4].Value.ToString();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            string query = "insert into pessoas2015(nome, morada, datanasc,cp) values (?n,?m,?d,?c)";
            try
            {
                cmd = new MySqlCommand(query, liga);
                cmd.Parameters.AddWithValue("?n", textBox1.Text);
                cmd.Parameters.AddWithValue("?m", textBox2.Text);
                cmd.Parameters.AddWithValue("?d", dateTimePicker1.Text);
                cmd.Parameters.AddWithValue("?c", maskedTextBox1.Text);
                liga.Open();
                DialogResult result = MessageBox.Show("Deseja inserir o registro na tabela?", "Inserir registros", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (result == DialogResult.OK)
                {
                    cmd.ExecuteNonQuery();
                    DisplayData();
                    ClearData();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                liga.Close();
            }
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string query = "delete from pessoas2015 where Nome = ?n ";
            try
            {
                MySqlCommand cmd = new MySqlCommand(query, liga);
                cmd.Parameters.AddWithValue("?n", textBox1.Text);
                liga.Open();
                DialogResult result = MessageBox.Show("Deseja eliminar o registro na tabela?", "Eliminar registros", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
                if (result == DialogResult.OK)
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                liga.Close();
            }
        }
    }
}