How to UPDATE data in a MySQL database using C#

A guide on how to use Prepared Statements in C# to update data in a MySQL table safely and efficiently with multiple parameters.

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

using System;
using MySql.Data.MySqlClient;

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;";

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

            // UPDATE statement with Prepared Statement
            string updateQuery = "UPDATE students SET name = @name, age = @age WHERE id = @id";

            using (MySqlCommand cmd = new MySqlCommand(updateQuery, conn))
            {
                // Add parameters
                cmd.Parameters.AddWithValue("@name", "Jane Doe");
                cmd.Parameters.AddWithValue("@age", 22);
                cmd.Parameters.AddWithValue("@id", 1);

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

                // Print the number of records updated
                Console.WriteLine($"{rowsAffected} record(s) updated.");
            }
        }
    }
}

Detailed explanation:

1. using MySql.Data.MySqlClient;: Imports the MySql.Data.MySqlClient library to work with MySQL in C#.
2. string connectionString = "Server=localhost;Database=test_db;User ID=root;Password=password;";: The connection string contains the necessary details to connect to MySQL, such as server, database, user, and password.
3. using (MySqlConnection conn = new MySqlConnection(connectionString)): Initializes the connection to MySQL.
4. conn.Open();: Opens the connection to the database.
5. string updateQuery = "UPDATE students SET name = @name, age = @age WHERE id = @id";: The UPDATE statement with placeholders for parameters.
6. using (MySqlCommand cmd = new MySqlCommand(updateQuery, conn)): Initializes MySqlCommand with the UPDATE statement and the connection.
7. cmd.Parameters.AddWithValue("@name", "Jane Doe");: Adds the @name parameter with the value "Jane Doe".
8. cmd.Parameters.AddWithValue("@age", 22);: Adds the @age parameter with the value 22.
9. cmd.Parameters.AddWithValue("@id", 1);: Adds the @id parameter with the value 1.
10. int rowsAffected = cmd.ExecuteNonQuery();: Executes the UPDATE statement and retrieves the number of records updated.
11. Console.WriteLine($"{rowsAffected} record(s) updated.");: Prints the number of records updated to the console.

System Requirements:

• .NET Framework or .NET Core
• MySQL.Data (library for MySQL connectivity)

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

Install the MySql.Data library via NuGet:

Install-Package MySql.Data

Tips:

• Ensure your MySQL server is running before attempting to connect.
• Double-check your connection details to avoid errors.
• Use Prepared Statements to protect your application against SQL injection attacks.