using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace MySQLModifyData {
	public partial class FormMain : Form {
		private DataTable dataTable = new DataTable ();

		public FormMain () {
			InitializeComponent ();

			mySqlConnection.Open ();
			mySqlDataAdapter.Fill (dataTable);
			mySqlConnection.Close ();
			dataGridViewTeachers.DataSource = dataTable;			
		}

		private void groupBoxData_Enter (object sender, EventArgs e) {

		}

		private void buttonInsert_Click (object sender, EventArgs e) {
			mySqlCommandModify.CommandText =
				"INSERT INTO teacher (teacherId, name, familyname) " +
				"VALUES ( " +
				"	'" + textBoxId.Text + "', " +
				"	'" + textBoxName.Text + "', " +
				"	'" + textBoxSurname.Text + "')";

			try {
				mySqlConnection.Open ();
				int rowsInserted = mySqlCommandModify.ExecuteNonQuery ();
				mySqlConnection.Close ();
				MessageBox.Show ("Inserted " + rowsInserted.ToString () + " rows.");
			} catch (Exception ex) {
				MessageBox.Show (ex.Message);
			}
		}

		private void buttonRefresh_Click (object sender, EventArgs e) {
			dataTable.Clear ();
			mySqlConnection.Open ();
			mySqlDataAdapter.Fill (dataTable);
			mySqlConnection.Close ();
			dataGridViewTeachers.DataSource = dataTable;			
		}

		private void dataGridViewTeachers_RowEnter (object sender, DataGridViewCellEventArgs e) {
			textBoxId.Text = dataGridViewTeachers.Rows[e.RowIndex].Cells[0].Value.ToString ();
			textBoxName.Text = dataGridViewTeachers.Rows[e.RowIndex].Cells[1].Value.ToString ();
			textBoxSurname.Text = dataGridViewTeachers.Rows[e.RowIndex].Cells[2].Value.ToString ();
		}

		private void buttonUpdate_Click (object sender, EventArgs e) {
			mySqlCommandModify.CommandText =
				"UPDATE teacher SET " +
				"	name = '" + textBoxName.Text + "', " +
				"	familyname = '" + textBoxSurname.Text + "' " +
				"WHERE teacherId = '" + textBoxId.Text + "'";

			try {
				mySqlConnection.Open ();
				int rowsInserted = mySqlCommandModify.ExecuteNonQuery ();
				mySqlConnection.Close ();
				MessageBox.Show ("Update " + rowsInserted.ToString () + " rows.");
			} catch (Exception ex) {
				MessageBox.Show (ex.Message);
			}
		}

		private void buttonDelete_Click (object sender, EventArgs e) {
			if (MessageBox.Show (
				"Delete user " + textBoxSurname.Text + "?",
				"Delete user",
				MessageBoxButtons.YesNo,
				MessageBoxIcon.Exclamation
				) == DialogResult.Yes) 
			{
				mySqlCommandModify.CommandText =
					"DELETE FROM teacher " +
					"WHERE teacherId = '" + textBoxId.Text + "'";

				try {
					mySqlConnection.Open ();
					int rowsInserted = mySqlCommandModify.ExecuteNonQuery ();
					mySqlConnection.Close ();
					MessageBox.Show ("Delete " + rowsInserted.ToString () + " rows.");
				} catch (Exception ex) {
					MessageBox.Show (ex.Message);
				}
			}
		}

		private void buttonTransaction_Click (object sender, EventArgs e) {
			if (MessageBox.Show ("Delete user " + textBoxSurname.Text + "?",
				"Delete user", MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation
				) == DialogResult.Yes) 
			{

				try {
					mySqlConnection.Open ();
					MySqlTransaction myTransaction = mySqlConnection.BeginTransaction ();
					MySqlCommand commandModify = mySqlConnection.CreateCommand ();
					commandModify.Transaction = myTransaction;
					try {
						commandModify.CommandText =
							"DELETE FROM teacher " +
							"WHERE teacherId = '" + textBoxId.Text + "'";
						int rowsInserted = commandModify.ExecuteNonQuery ();

						commandModify.CommandText =
							"UPDATE subject SET gestor = 'KI001' " +
							"WHERE gestor = '" + textBoxId.Text + "'";
						commandModify.ExecuteNonQuery ();

						myTransaction.Commit ();
						MessageBox.Show ("Delete " + rowsInserted.ToString () + " rows.");
					} catch (Exception ex) {
						myTransaction.Rollback ();
						MessageBox.Show (ex.Message);
					}
				} catch (Exception ee) {
					MessageBox.Show (ee.Message);
				} finally {
					mySqlConnection.Close ();
				}
			}
		}
	}
}