Insert, Update, Delete, Display Data in MySQL Using C#

This article shows how to insert, update, delete and display data in MySQL.

I have updated the Article and source code on my personal blog. you can get it here.

Introduction

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. We can use MySQL with C#, Java and many other languages. Here we will use C#.

Diagram 1

Username=Ehtesham
Password=1234

Note: You need to include this assembly.

using MySql.Data.MySqlClient;  //Its for MySQL

 

Insert Data

private void button1_Click(object sender, EventArgs e)
{
    try
   {
            //This is my connection string i have assigned the database file address path
            string MyConnection2 = "datasource=localhost;port=3307;username=root;password=root";
            //This is my insert query in which i am taking input from the user through windows forms
            string Query = "insert into student.studentinfo(idStudentInfo,Name,Father_Name,Age,Semester) values('" +this.IdTextBox.Text+ "','" +this.NameTextBox.Text+ "','" +this.FnameTextBox.Text+ "','" +this.AgeTextBox.Text+ "','" +this.SemesterTextBox.Text+ "');";
            //This is  MySqlConnection here i have created the object and pass my connection string.
            MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
            //This is command class which will handle the query and connection object.
            MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
            MySqlDataReader MyReader2;
            MyConn2.Open();
            MyReader2 = MyCommand2.ExecuteReader();     // Here our query will be executed and data saved into the database.
            MessageBox.Show("Save Data");
            while (MyReader2.Read())
            {
            }
            MyConn2.Close();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
      }
}

Update Data

private void button2_Click(object sender, EventArgs e)
{
    try
    {
        //This is my connection string i have assigned the database file address path
        string MyConnection2 = "datasource=localhost;port=3307;username=root;password=root";
        //This is my update query in which i am taking input from the user through windows forms and update the record.
        string Query = "update student.studentinfo set idStudentInfo='" + this.IdTextBox.Text + "',Name='" + this.NameTextBox.Text + "',Father_Name='" + this.FnameTextBox.Text + "',Age='" + this.AgeTextBox.Text + "',Semester='" + this.SemesterTextBox.Text + "' where idStudentInfo='" + this.IdTextBox.Text + "';";
        //This is  MySqlConnection here i have created the object and pass my connection string.
        MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
        MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
        MySqlDataReader MyReader2;
        MyConn2.Open();
        MyReader2 = MyCommand2.ExecuteReader();
        MessageBox.Show("Data Updated");
        while (MyReader2.Read())
        {
        }
        MyConn2.Close();//Connection closed here
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Delete Data

private void button3_Click(object sender, EventArgs e)
{
    try
    {
        string MyConnection2 = "datasource=localhost;port=3307;username=root;password=root";
        string Query = "delete from student.studentinfo where idStudentInfo='" + this.IdTextBox.Text + "';";
        MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
        MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
        MySqlDataReader MyReader2;
        MyConn2.Open();
         MyReader2 = MyCommand2.ExecuteReader();
         MessageBox.Show("Data Deleted");
         while (MyReader2.Read())
         {
         }
         MyConn2.Close();
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

Display Data

private void button4_Click(object sender, EventArgs e)
{
    try
    {
        string MyConnection2 = "datasource=localhost;port=3307;username=root;password=root";
        //Display query
        string Query = "select * from student.studentinfo;";
        MySqlConnection MyConn2 = new MySqlConnection(MyConnection2);
        MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
        //  MyConn2.Open();
       //For offline connection we weill use  MySqlDataAdapter class.
       MySqlDataAdapter MyAdapter = new MySqlDataAdapter();
       MyAdapter.SelectCommand = MyCommand2;
       DataTable dTable = new DataTable();
       MyAdapter.Fill(dTable);
       dataGridView1.DataSource = dTable; // here i have assign dTable object to the dataGridView1 object to display data.
        // MyConn2.Close();
   }
  catch (Exception ex)
  {
        MessageBox.Show(ex.Message);
   }
}

Diagram 2

I have also attached the source code so you can download it. Remember that you need to make your database and also your connection strings and so on.