Running a Query in C# Database

In C#, we use the SqlCommand command to execute queries in the database. Since I will continue with MySQL for our example, I will use the MySqlCommand command.

Start a new Windows Forms application and add two buttons and a ListView. Give the buttons the names "Get Data" and "Delete Data".

First, to establish our database connection, we make our connection commands and open the connection.

 
MySqlConnection connection = new MySqlConnection("server=localhost; userid=root; password=root; database=cryptograph");

In the click event of the button named "Get Data", we open our connection using error control commands and write the necessary commands to fetch the data.

try
{
       connection.Open();   
       MySqlCommand query = new MySqlCommand("SELECT * FROM blog ORDER BY id DESC", connection);
       MySqlDataReader reader = query.ExecuteReader();
       while (reader.Read())
       {
              listView1.Items.Add(reader["blog_baslik"].ToString());
       }
 }
 catch (Exception error)
 {
       MessageBox.Show(error.ToString(), "Error");
}
finally
{
       connection.Close();
}

We defined an object named query with the MySqlCommand command and specified our SQL query and which connection to use.

SELECT * FROM blog ORDER BY id DESC is our SQL query, where we wanted to bring data from the table named blog sorted in descending order by the id field.

We created a data reader object with MySqlDataReader, which is the object that will read the data from the database, assigned it to the object reader, and specified from which object it will take the information using the object query.

Then, by looping through it, we added the data to the ListView. When the queries inside the Try block are finished, the commands inside the finally block are executed, meaning that the operation is specified to be performed regardless of whether the operation is successful or not. Here we closed the database connection.

Now, for the click event of the button named "Delete Data", we write the following commands:

try
{
   connection.Open();
   string blogTitle = listView1.FocusedItem.Text.ToString();
   query = new MySqlCommand("DELETE FROM blog WHERE blog_baslik='" + blogTitle + "'", connection);
   try
   {
     query.ExecuteNonQuery();
     MessageBox.Show(blogTitle + " data is deleted");
     listView1.Items.Clear();
     try
     {
       MySqlCommand query2 = new MySqlCommand("SELECT * FROM blog ORDER BY id DESC", connection);
       MySqlDataReader reader = query2.ExecuteReader();
       while (reader.Read())
       {
         listView1.Items.Add(reader["blog_baslik"].ToString());
       }
     }
     catch (Exception error)
     {
        MessageBox.Show(error.ToString(), "Error");
     }
   }
   catch (Exception error)
   {
      MessageBox.Show(error.ToString(), "Error");
   }
}
catch (Exception error)
{
   MessageBox.Show(error.ToString(), "Error");
}
finally
{
   connection.Close();
}

Again, I created an object named query, this time I used the delete command from the database, DELETE * FROM , I used the command, we are deleting from the database according to the selected title information from the list. string blogTitle = listView1.FocusedItem.Text.ToString(); command, we take the selected information from the listview as a string and put it into the sql query.
Then we clear everything on the listview and fetch the information again, otherwise the deleted item will still remain on the listview, meaning we are refreshing the listview object, in the last operation, we close the database connection.
You can also do other operations related to the database using the same commands.

You can download the project file from here.