How to DELETE data from a MySQL database using C#

A guide on how to use Prepared Statements in C# to delete data from a table in a MySQL database safely and effectively using multiple parameters.

In this article, you'll learn how to connect to a MySQL database and use C# with Prepared Statements to execute a DELETE statement, allowing you to remove records from a table in the database using multiple parameters.

using System;
using MySql.Data.MySqlClient;

namespace MySQLDeleteExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // Connection string to the MySQL database
            string connectionString = "Server=localhost;Database=test_db;User ID=root;Password=password;";

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                try
                {
                    // Open the connection to the database
                    conn.Open();

                    // DELETE statement with Prepared Statement
                    string deleteQuery = "DELETE FROM students WHERE id = @id AND name = @name";

                    using (MySqlCommand cmd = new MySqlCommand(deleteQuery, conn))
                    {
                        // Add parameters to the DELETE statement
                        cmd.Parameters.AddWithValue("@id", 1);
                        cmd.Parameters.AddWithValue("@name", "John Doe");

                        // Execute the DELETE statement
                        int rowsAffected = cmd.ExecuteNonQuery();

                        // Display the number of records deleted
                        Console.WriteLine($"{rowsAffected} record(s) deleted.");
                    }
                }
                catch (Exception ex)
                {
                    // Handle errors
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
        }
    }
}

Detailed explanation:

1. using MySql.Data.MySqlClient;: Imports the MySQL Connector/NET library to enable connections to MySQL from C#.
2. string connectionString = "Server=localhost;Database=test_db;User ID=root;Password=password;";: Declares the connection string for the MySQL database.
3. using (MySqlConnection conn = new MySqlConnection(connectionString)): Creates a MySQL connection object.
4. conn.Open();: Opens the connection to the MySQL database.
5. string deleteQuery = "DELETE FROM students WHERE id = @id AND name = @name";: Declares the DELETE statement with parameters.
6. using (MySqlCommand cmd = new MySqlCommand(deleteQuery, conn)): Creates a MySqlCommand object to execute the statement.
7. cmd.Parameters.AddWithValue("@id", 1); and cmd.Parameters.AddWithValue("@name", "John Doe");: Adds the necessary parameters to the DELETE statement.
8. int rowsAffected = cmd.ExecuteNonQuery();: Executes the DELETE statement and gets the number of records deleted.
9. Console.WriteLine($"{rowsAffected} record(s) deleted.");: Displays the number of records that were deleted.

System Requirements:

• C# .NET Framework or .NET Core
• Library: MySQL Connector/NET

How to install the libraries needed to run the C# code above:

1. Install MySQL Connector/NET via NuGet:

Install-Package MySql.Data

Tips:

• Make sure your MySQL server is running before attempting to connect.
• Double-check your connection details like Server, Database, User ID, and Password to avoid connection errors.
• Always use Prepared Statements to protect against SQL injection attacks.