C# DataGridView MySQL – ADD RETRIEVE UPDATE DELETE

 

This is a C# MySQL DataGridView tutorial.We se how to insert data to MySQL database,select the data on button click,update the data when update button is clicked and delete data also.Our component of choice is a datagridview and we use multiple columns.

 

Here's the code :

using MySql.Data.MySqlClient;
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;

namespace CS_DGView_MySQL
{

  public partial class Form1 : Form
  {

    static string conString = "Server=localhost;Database=peopledb;Uid=root;Pwd=;";
    MySqlConnection con = new MySqlConnection(conString);
    MySqlCommand cmd;
    MySqlDataAdapter adapter;
    DataTable dt = new DataTable();

    public Form1()
    {
      InitializeComponent();

      //DATAGRIDVIEW PROPERTIES
      dataGridView1.ColumnCount = 4;
      dataGridView1.Columns[0].Name = "ID";
      dataGridView1.Columns[1].Name = "Name";
      dataGridView1.Columns[2].Name = "Position";
      dataGridView1.Columns[3].Name = "Team";

      dataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;

      //SELECTION MODE
      dataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect;
      dataGridView1.MultiSelect = false;

    }

    //INSERT INTO DB
    private void add(string name,string pos,string team)
    {
      //SQL STMT
      string sql = "INSERT INTO peopleTB(Name,Position,Team) VALUES(@PNAME,@POSITION,@TEAM)";
      cmd = new MySqlCommand(sql, con);

      //ADD PARAMETERS
      cmd.Parameters.AddWithValue("@PNAME", name);
      cmd.Parameters.AddWithValue("@POSITION", pos);
      cmd.Parameters.AddWithValue("@TEAM", team);
      //OPEN CON AND EXEC insert
      try
      {
        con.Open();

        if(cmd.ExecuteNonQuery()>0)
        {
          clearTxts();
          MessageBox.Show("Successfully Inserted");
        }

        con.Close();

        retrieve();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
        con.Close();
      }
    }

    //ADD TO DGVIEW
    private void populate(String id,String name,string pos,string team)
    {
      dataGridView1.Rows.Add(id, name, pos, team);
    }

    //RETRIEVE FROM DB
    private void retrieve()
    {
      dataGridView1.Rows.Clear();

      //SQL STMT
      string sql = "SELECT * FROM peopleTB ";
      cmd = new MySqlCommand(sql, con);

      //OPEN CON,RETRIEVE,FILL DGVIEW
      try
      {
        con.Open();

        adapter = new MySqlDataAdapter(cmd);

        adapter.Fill(dt);

        //LOOP THRU DT
        foreach(DataRow row in dt.Rows)
        {
          populate(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[0].ToString());
        }

        con.Close();

        //CLEAR DT
        dt.Rows.Clear();
      }
      catch (Exception ex)
      {

        MessageBox.Show(ex.Message);
        con.Close();
      }
    }

    //UPDATE DB
    private void update(int id,string name,string pos,string team)
    {
      //SQL STMT
      string sql = "UPDATE peopleTB SET Name='" + name + "',Position='" + pos + "',Team='" + team + "' WHERE ID=" + id + "";
      cmd = new MySqlCommand(sql, con);

      //OPEN CON,UPDATE,RETRIEVE DGVIEW
      try
      {
        con.Open();
        adapter = new MySqlDataAdapter(cmd);

        adapter.UpdateCommand = con.CreateCommand();
        adapter.UpdateCommand.CommandText = sql;

        if(adapter.UpdateCommand.ExecuteNonQuery()>0)
        {
          clearTxts();
          MessageBox.Show("Successfully Updated");
        }

        con.Close();

        retrieve();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
        con.Close();
      }

    }

    //DELETE FROM DB
    private void delete(int id)
    {
      //SQLSTMT
      string sql = "DELETE FROM peopleTB WHERE ID=" + id + "";
      cmd = new MySqlCommand(sql, con);

      //'OPEN CON,EXECUTE DELETE,CLOSE CON
      try
      {
        con.Open();
        MessageBox.Show(con.State.ToString());
        adapter = new MySqlDataAdapter(cmd);

        adapter.DeleteCommand = con.CreateCommand();

        adapter.DeleteCommand.CommandText = sql;

         //PROMPT FOR CONFIRMATION
        if(MessageBox.Show("Sure ??","DELETE",MessageBoxButtons.OKCancel,MessageBoxIcon.Warning)==DialogResult.OK)
        {
          if(cmd.ExecuteNonQuery()>0)
          {
            clearTxts();
            MessageBox.Show("Successfully deleted");
          }
        }

        con.Close();

        retrieve();
      }
      catch (Exception ex)
      {
        MessageBox.Show(ex.Message);
        con.Close();
      }

    }

    //clear txtx
    private void clearTxts()
    {
      nameTxt.Text = "";
      posTxt.Text = "";
      teamTxt.Text = "";
    }

    private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
    {
      nameTxt.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
      posTxt.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
      teamTxt.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
    }

    private void addBtn_Click(object sender, EventArgs e)
    {
      add(nameTxt.Text, posTxt.Text, teamTxt.Text);
    }

    private void retrieveBtn_Click(object sender, EventArgs e)
    {
      retrieve();
    }

    private void updateBtn_Click(object sender, EventArgs e)
    {
      String selected = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
      int id = Convert.ToInt32(selected);

      update(id, nameTxt.Text, posTxt.Text, teamTxt.Text);
    }

    private void deleteBtn_Click(object sender, EventArgs e)
    {
      String selected = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
      int id = Convert.ToInt32(selected);

      delete(id);
    }

    private void clearBtn_Click(object sender, EventArgs e)
    {
      dataGridView1.Rows.Clear();
    }
  }
}

Best Regards.