[Jakarin Chawachat]

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;
namespace db
{
  public partial class Form1 : Form
  {
      private MySqlConnection connection;
      private string server;
      private string database;
      private string uid;
      private string password;
      public Form1()
      {
          InitializeComponent();
          server = "localhost";
          database = "connectcsharptomysql";
          uid = "root";
          password = "chung";
          string connectionString;
          connectionString = "SERVER=" + server + ";" + "DATABASE=" +
          database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";
          connection = new MySqlConnection(connectionString);
      }
      private bool OpenConnection()
      {
          try
          {
              connection.Open();
              return true;
          }
          catch (MySqlException ex)
          {
              //When handling errors, you can your application's response based 
              //on the error number.
              //The two most common error numbers when connecting are as follows:
              //0: Cannot connect to server.
              //1045: Invalid user name and/or password.
              switch (ex.Number)
              {
                  case 0:
                      MessageBox.Show("Cannot connect to server.  Contact administrator");
                      break;
                  case 1045:
                      MessageBox.Show("Invalid username/password, please try again");
                      break;
              }
              return false;
          }
      }
      //Close connection
      private bool CloseConnection()
      {
          try
          {
              connection.Close();
              return true;
          }
          catch (MySqlException ex)
          {
              MessageBox.Show(ex.Message);
              return false;
          }
      }
      private void Form1_Load(object sender, EventArgs e)
      {
      }
      private void button1_Click(object sender, EventArgs e)
      {
          string query = "INSERT INTO tableinfo (name, age) VALUES('John Smith', '33')";
          //open connection
          if (this.OpenConnection() == true)
          {
              //create command and assign the query and connection from the constructor
              MySqlCommand cmd = new MySqlCommand(query, connection);
              //Execute command
              cmd.ExecuteNonQuery();
              //close connection
              this.CloseConnection();
          }
      }
      private void button2_Click(object sender, EventArgs e)
      {
          string query = "UPDATE tableinfo SET name='Joe', age='22' WHERE name='John Smith'";
          //Open connection
          if (this.OpenConnection() == true)
          {
              //create mysql command
              MySqlCommand cmd = new MySqlCommand();
              //Assign the query using CommandText
              cmd.CommandText = query;
              //Assign the connection using Connection
              cmd.Connection = connection;
              //Execute query
              cmd.ExecuteNonQuery();
              //close connection
              this.CloseConnection();
          }
      }
      private void button3_Click(object sender, EventArgs e)
      {
          string query = "DELETE FROM tableinfo WHERE name='John Smith'";
          if (this.OpenConnection() == true)
          {
              MySqlCommand cmd = new MySqlCommand(query, connection);
              cmd.ExecuteNonQuery();
              this.CloseConnection();
          }
      }
      private void button4_Click(object sender, EventArgs e)
      {            
          string query = "SELECT * FROM tableinfo";
          //Open connection
          if (this.OpenConnection() == true)
          {
              //Create Command
              MySqlCommand cmd = new MySqlCommand(query, connection);
              //Create a data reader and Execute the command
              MySqlDataReader dataReader = cmd.ExecuteReader();
              //Read the data and store them in the list
              while (dataReader.Read())
              {
                  textbox1.Text += dataReader["id"] + ""+dataReader["name"] + ""+dataReader["age"] + "\r\n";
              }
              //close Data Reader
              dataReader.Close();
              //close Connection
              this.CloseConnection();
              //return list to be displayed                
          }
      }
      private void button5_Click(object sender, EventArgs e)
      {
          string query = "SELECT Count(*) FROM tableinfo";
          int Count = -1;
          //Open Connection
          if (this.OpenConnection() == true)
          {
              //Create Mysql Command
              MySqlCommand cmd = new MySqlCommand(query, connection);
              //ExecuteScalar will return one value
              Count = int.Parse(cmd.ExecuteScalar() + "");
              //close Connection
              this.CloseConnection();
              MessageBox.Show(Count.ToString());
          }
      }
  }
}