Retrieving and Deleting Data Using MySQL Database and C#.Net

Retrieving and Deleting Data Using MySQL Database and C#.Net

In this tutorial, I will teach you how to retrieve and delete data in the database using MySQL database and C#.net. These functionalities will help you to display the data from the database to the Datagridview. And you can also delete the data that has been saved in the MySQL database. I will use Microsoft Visual Studio 2008 and MySQL for the database.

Let’s begin:

Step 1: Open Microsoft Visual Studio 2008 and create a new windows application for C#. Do the form just like this.

Step 2: Go to the solution explorer and hit the “view code“.

Step 3: In the code editor, declare all the classes and variables that are needed.

Note: Add MySQL.Data.dll as your references to access mysql server library.

 //initialize sql connection
 MySqlConnection con = new MySqlConnection();
 //initialize all classes
 MySqlCommand cmd = new MySqlCommand();
 MySqlDataAdapter da = new MySqlDataAdapter();
 DataTable dt = new DataTable();

 //declaring variables
 string strquery;
 int result;

Step 4: Go back to the design view, double-click the form and do the following codes for establishing the connection between MySQL Database and C#.net.

 private void Form1_Load(object sender, EventArgs e)
 {
 //set a connection string 
 con.ConnectionString = "server=localhost;user id=root;password=;Database=dbuser;";
 }

Step 5: Go back to the design view again, double-click the “Display” button and do the following codes for displaying the data from the database to the Datagridview.

 private void btndisplay_Click(object sender, EventArgs e)
 {
 try
 {
 con.Open();
 //create a query for retrieving data in the database.
 strquery = "SELECT ID,`UNAME` as Name, `UUSERNAME` as Username, `UPASSWORD` as Password, `UTYPE` as Type FROM `tblusers` ";
 //initialize new Sql commands
 cmd = new MySqlCommand();
 //hold the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = strquery;
 //initialize new Sql data adapter
 da = new MySqlDataAdapter();
 //fetching query in the database.
 da.SelectCommand = cmd;
 //initialize new datatable
 dt = new DataTable();
 //refreshes the rows in specified range in the datasource. 
 da.Fill(dt);

 
 dataGridView1.DataSource = dt;

 
 
 }
 catch (Exception ex)
 {
 //catching error
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();
 con.Close();
 }
 }

Step 6: Go back to the design view again, double-click the “Delete” button and do the following codes for deleting the data in the database.

 private void btnDel_Click(object sender, EventArgs e)
 {
 try
 {
 con.Open();
 //create a query for delete data in the database.
 strquery = "Delete From tblusers WHERE ID=" + dataGridView1.CurrentRow.Cells[0].Value;

 //initialize new Sql commands
 cmd = new MySqlCommand();
 //hold the data to be executed.
 cmd.Connection = con;
 cmd.CommandText = strquery;

 result = cmd.ExecuteNonQuery();

 if (result > 0)
 {
 MessageBox.Show("User has been deleted in the database.");
 
 }
 }
 catch (Exception ex)
 {
 //catching error
 MessageBox.Show(ex.Message);
 }
 finally
 {
 da.Dispose();
 con.Close();
 }
 }

Result:

For all students who need a programmer for your thesis system or anyone who needs a source code in any programming languages. You can contact me @ :
Email – [email protected]
Mobile No. – 09305235027 – TNT

Download Source code: Click here