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

In this tutorial, we’ll walk through the basics of performing CRUD (Create, Read, Update, Delete) operations in MariaDB using C#.

Posted on

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

  1. Setting Up Your Environment:

    • Installing MariaDB.
    • Setting up your C# development environment.

  2. Connecting to MariaDB:

    • Creating a connection to your MariaDB server.

  3. Creating a Table:

    • Writing C# code to create a table in your MariaDB database.

  4. Inserting Data:

    • Demonstrating how to insert data into the table.

  5. Querying Data:

    • Retrieving data from the database.

  6. Updating Data:

    • Modifying existing records in the table.

  7. Deleting Data:

    • Deleting records from the table.

1. Setting Up Your Environment

Installing MariaDB

Setting Up Your C# Development Environment

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

2. Connecting to MariaDB

To connect to your MariaDB 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 MariaDB server:

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 MariaDB!");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        finally
        {
            connection.Close();
        }
    }
}

Replace the connection string values with your MariaDB server details.

3. Creating a Table

Let’s create a simple users table in your MariaDB 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 will create 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.”

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

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

This code deletes the user with the name “John Doe” from the users table.

With these CRUD operations, you have a solid foundation for working with MariaDB in your C# applications. Feel free to extend and adapt these examples to meet the requirements of your specific project.