How to Insert, Delete, Update and View Data From MySQL Database using C#.NET? [With Source Code]

By Er. Ran Bahadur B.K. Last updated Aug 15, 2021

Hello guyz, Today in tutorial we are gonna talk about inserting, deleting updating and viewing data in C#.NET from MySQL database. These are the most basic function in either big or small project. so we hope this may helpful for you guyz. you can also see the designing part in the video.

if you are new to the MySQL and C# and you are gonna connect C# with MySQL for the very first time, then you need to download and install following software which are free of code.

MySQL for Visual Studio: https://dev.mysql.com/downloads/windows/visualstudio/

MySQL Connector for Visual studio: https://dev.mysql.com/downloads/connector/net/

Wampserver: http://www.wampserver.com/en/

Steps:

[sociallocker]

1. After downloading and installing wamp server start it, then open your localhost, for this open up your any browsing software and type localhost or 127.0.0.1 in the address bar. Then new page will appear then scroll down and find phpmyadmin and click on that. Then you will be able to see the login page. Login in by entering username and password (Default username is root and password is empty). And then click on new and create one database having any name you want and the click ok, then create table having any number of field you want but i am using four field in this case and click on go. After that, you need to insert the field name like: Roll No, Name, Address etc. and then select the length as well as data type as your requirement and then click on save. You can watch the video for this designing part, you may understand better in video. Make sure that make any one filed primary key, generally Roll No is set as PK because of unique identification than other field.

2. Open your visual studio of any version and then go to file new and then click on project. Then new window will appear and from the left pane select visual c#. under visual c# select window and then select window form application from the middle pane and give the name of your project and click ok.

3. Drag and drop four label, textbox and button as you need in your forms and change the text and name of these control from the properties of these controls.

4. Then add the reference of MySQL. To do this, right click on reference select add reference and the click on extension then scroll down and find the MySQL.data and click on check box and click ok.

5. Go to the .cs file of your form and import namespce as:

using MySql.Data.MySqlClient;

6. Now from the left pane select server explorer and right click on data connection and click on add connection. After clicking on add connection new window will appear and you will see the Data Source option and then you will also see the change button, click on change button and select MySQL database (This option will not appear if you are using the Visual studio of version other than professional and ultimate, to view this option you need to install this software, https://dev.mysql.com/downloads/windows/visualstudio/), Then click ok, after doing this you will see new window and there you need to proview your server name, username and password(i.e. localhost, root, the password filed is empty i.e. nothing and these value are default value, if you have set any username and password provide your own). Then click Ok.

7. Write the following piece of code after double clicking each button (i.e insert, update, delete and new).

a. Insert button:

MySqlConnection con = new MySqlConnection("server=localhost;user id=root;database=projectmysql");
con.Open();
MySqlCommand cmd = new MySqlCommand("INSERT INTO projectmysql (`name`, `address`, `phone`, `email`) VALUES ('" + txtname.Text + "','" + txtadd.Text + "','" + txtphone.Text + "','" + txtemail.Text + "')", con);
cmd.ExecuteNonQuery();
MessageBox.Show("successful");
con.Close();

b. Delete Button:

MySqlConnection con = new MySqlConnection("server=localhost;user id=root;database=projectmysql");
con.Open();
MySqlCommand cmd = new MySqlCommand("DELETE FROM `projectmysql` WHERE rollno ='" + txtroll.Text + "'", con);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("successful");

c. Update Button:

MySqlConnection con = new MySqlConnection("server=localhost;user id=root;database=projectmysql");
con.Open();
MySqlCommand cmd = new MySqlCommand("UPDATE `projectmysql` SET `name`='" + txtname.Text + "',`address`='" + txtadd.Text + "',`phone`='" + txtphone.Text + "',`email`='" + txtemail.Text + "' WHERE rollno = '" + txtroll.Text + "'", con);
cmd.ExecuteNonQuery();
MessageBox.Show("successfully updated");
con.Close();

d: New Button:

txtadd.Clear();
txtemail.Clear();
txtphone.Clear();
txtroll.Clear();
txtname.Clear();

8. Now drag and drop datagridview from you toolbox and then right click on datagridview and click on edit column and new window will appear and then add your column over there and after adding the column click on close button of the window. then go to the .cs section of your form and create one function having name gridview() inside the class and then write the following code:


MySqlConnection con = new MySqlConnection("server=localhost;user id=root;database=projectmysql");
con.Open();
MySqlDataAdapter sda = new MySqlDataAdapter("select * from projectmysql", con);
DataTable dt = new DataTable();
sda.Fill(dt);
foreach(DataRow item in dt.Rows)
{
int n = dataGridView1.Rows.Add();
dataGridView1.Rows[n].Cells[0].Value = item["rollno"].ToString();
dataGridView1.Rows[n].Cells[1].Value = item["name"].ToString();
dataGridView1.Rows[n].Cells[2].Value = item["address"].ToString();
dataGridView1.Rows[n].Cells[3].Value = item["phone"].ToString();
dataGridView1.Rows[n].Cells[4].Value = item["email"].ToString();

}

9. Copy the function name and paste it inside the constuctor of your form but below the InitializeComponent() function.

10. Select the datagridview and from the properties window of the datagridview find the SelectionMode option and change it to FullRowSelect and MultiSelect to true and click on event button as:

and find the MouseDoubleCick and click on that this will create the mouse double click event of the datagridview and write the following code inside that event:

int n = dataGridView1.SelectedRows[0].Index;
txtroll.Text = dataGridView1.Rows[n].Cells[0].Value.ToString();
txtname.Text = dataGridView1.Rows[n].Cells[1].Value.ToString();
txtadd.Text = dataGridView1.Rows[n].Cells[2].Value.ToString();
txtphone.Text = dataGridView1.Rows[n].Cells[3].Value.ToString();
txtemail.Text = dataGridView1.Rows[n].Cells[4].Value.ToString();

That’s it guyz,

[/sociallocker]

if you have any confusion you can watch the full video: