MySQL CRUD Tutorials in C#: A Step-by-Step Guide

MySQL is a widely-used open-source relational database management system, and C# is a versatile programming language. In this tutorial, we will explore the basics of performing CRUD (Create, Read, Update, Delete) operations in MySQL using C#. We’ll cover the following steps:

Setting Up Your Environment:
- Installing MySQL.
- Setting up your C# development environment.
Connecting to MySQL:
- Creating a connection to your MySQL server.
Creating a Table:
- Writing C# code to create a table in your MySQL database.
Inserting Data:
- Demonstrating how to insert data into the table.
Querying Data:
- Retrieving data from the table.
Updating Data:
- Modifying existing records in the table.
Deleting Data:
- Deleting records from the table.

1. Setting Up Your Environment

Installing MySQL

Download and install MySQL from the official MySQL website.

Setting Up Your C# Development Environment

Install Visual Studio or Visual Studio Code, and ensure you have the .NET SDK installed.

2. Connecting to MySQL

To connect to your MySQL server from a C# application, you can use the MySQL .NET Connector (MySql.Data). Install it using NuGet Package Manager or the .NET CLI:

dotnet add package MySql.Data

Now, let’s create a connection to your MySQL server:

using System;
using MySql.Data.MySqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost;Port=3306;Database=mydatabase;User=myuser;Password=mypassword;";
        MySqlConnection connection = new MySqlConnection(connectionString);

        try
        {
            connection.Open();
            Console.WriteLine("Connected to MySQL!");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        finally
        {
            connection.Close();
        }
    }
}

Replace localhost:3306, mydatabase, myuser, and mypassword with your MySQL server details.

3. Creating a Table

Let’s create a simple users table in your MySQL database:

string createTableSql = "CREATE TABLE IF NOT EXISTS users (" +
    "id INT AUTO_INCREMENT PRIMARY KEY," +
    "name VARCHAR(255) NOT NULL," +
    "email VARCHAR(255) NOT NULL)";
MySqlCommand createTableCommand = new MySqlCommand(createTableSql, connection);

try
{
    connection.Open();
    createTableCommand.ExecuteNonQuery();
    Console.WriteLine("Table created!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code creates a table named users with columns id, name, and email.

4. Inserting Data

Now, let’s insert a user into the users table:

string insertSql = "INSERT INTO users (name, email) VALUES (@name, @email)";
MySqlCommand insertCommand = new MySqlCommand(insertSql, connection);

// Parameters
insertCommand.Parameters.AddWithValue("@name", "John Doe");
insertCommand.Parameters.AddWithValue("@email", "[email protected]");

try
{
    connection.Open();
    int rowsAffected = insertCommand.ExecuteNonQuery();
    Console.WriteLine($"Inserted {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code inserts a user with the name “John Doe” and email “[email protected]” into the users table.

5. Querying Data

Let’s retrieve data from the users table:

string query = "SELECT * FROM users";
MySqlCommand queryCommand = new MySqlCommand(query, connection);

try
{
    connection.Open();
    using (MySqlDataReader reader = queryCommand.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}, Email: {reader["email"]}");
        }
    }
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code queries and displays all records in the users table.

6. Updating Data

Let’s update a user’s email address:

string updateSql = "UPDATE users SET email = @newEmail WHERE name = @name";
MySqlCommand updateCommand = new MySqlCommand(updateSql, connection);

// Parameters
updateCommand.Parameters.AddWithValue("@newEmail", "[email protected]");
updateCommand.Parameters.AddWithValue("@name", "John Doe");

try
{
    connection.Open();
    int rowsAffected = updateCommand.ExecuteNonQuery();
    Console.WriteLine($"Updated {rowsAffected} row(s)!");
}
catch (Exception ex)
{
    Console.WriteLine($"Error: {ex.Message}");
}
finally
{
    connection.Close();
}

This code updates the email address of the user with the name “John Doe” in the users table.

7. Deleting Data

Let’s delete a user from the users table:

string deleteSql = "DELETE FROM users WHERE name = @name";
MySqlCommand deleteCommand = new MySqlCommand(deleteSql, connection);

// Parameter
deleteCommand.Parameters.AddWithValue("@name", "John Doe