How to INSERT data into a MySQL database using C#

A guide on how to use Prepared Statements in C# to insert data into a table in a MySQL database safely and effectively.

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

using System;
using MySql.Data.MySqlClient;

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

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

            // INSERT statement with Prepared Statement
            string insertQuery = "INSERT INTO students (id, name, age) VALUES (@id, @name, @age)";

            using (MySqlCommand cmd = new MySqlCommand(insertQuery, conn))
            {
                // Assign values to the parameters
                cmd.Parameters.AddWithValue("@id", 1);
                cmd.Parameters.AddWithValue("@name", "John Doe");
                cmd.Parameters.AddWithValue("@age", 20);

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

                Console.WriteLine($"{rowsAffected} record(s) inserted.");
            }
        }
    }
}

Detailed explanation:

1. using MySql.Data.MySqlClient;: Imports the MySql.Data library to allow connections and operations with MySQL.
2. string connectionString = "...": The connection string containing details to connect to the MySQL database.
3. using (MySqlConnection conn = new MySqlConnection(connectionString)): Creates a connection to the MySQL database.
4. conn.Open(): Opens the connection to the database.
5. string insertQuery = "INSERT INTO students (id, name, age) VALUES (@id, @name, @age)": Defines the INSERT statement with parameters.
6. using (MySqlCommand cmd = new MySqlCommand(insertQuery, conn)): Creates a MySqlCommand to execute the INSERT statement.
7. cmd.Parameters.AddWithValue("@id", 1);: Assigns a value to the @id parameter.
8. cmd.Parameters.AddWithValue("@name", "John Doe");: Assigns a value to the @name parameter.
9. cmd.Parameters.AddWithValue("@age", 20);: Assigns a value to the @age parameter.
10. int rowsAffected = cmd.ExecuteNonQuery();: Executes the INSERT statement and returns the number of rows inserted.
11. Console.WriteLine($"{rowsAffected} record(s) inserted.");: Displays the number of records that were inserted.

System Requirements:

• C# .NET Framework 4.x or .NET Core
• Library: MySql.Data from MySQL Connector/NET

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

1. Install MySQL Connector/NET from NuGet Package Manager:

   Install-Package MySql.Data
   

Tips:

• Ensure your MySQL server is running before attempting to connect.
• Use Prepared Statements to protect against SQL injection attacks.