MySQL in C#

Package Introduction

Database access is a core aspect of many applications. C# can interface with many different databases such as Oracle, Microsoft SQL, and MongoDB. In this case, we will be working with MySQL. The first step is to add the MySQL package to your project.

dotnet add package MySql.Data

Saving Your Credentials

When you run a client program to connect to the MySQL server, it is inadvisable to specify your password in a way that exposes it to discovery by other users. We will store our credentials in a .env file and load them into the environment variables. The code to load .env files is trivial, so we will just add it directly to the project. Add the .env file to your .gitignore so that you don’t upload your credentials to your code repository.

class DotEnv {
    public static void Load() {
        string filepath = Path.Combine(
            Directory.GetCurrentDirectory(),
            ".env"
        );

        DotEnv.Load(filepath);
    }

    public static void Load(string filePath) {
        if (!File.Exists(filePath)) return;

        foreach (var line in File.ReadAllLines(filePath)) {
            var parts = line.Split('=',StringSplitOptions.RemoveEmptyEntries);
            if (parts.Length != 2) continue;
            if (Environment.GetEnvironmentVariable(parts[0]) != null) continue;
            Environment.SetEnvironmentVariable(parts[0], parts[1]);
        }
    }
}

Example .env File

SQL_USER=username
SQL_PW=supersecret
SQL_SERVER=localhost

Creating a Connection

Before you can perform database operations, you need to establish a connection. This is achieved with a MySqlConnection object. We will add the variables from the .env file with string interpolation.

using MySql.Data.MySqlClient;

DotEnv.Load();
var env = Environment.GetEnvironmentVariables();

string cs = @$"
               server={env["SQL_SERVER"]};
               userid={env["SQL_USER"]};
               password={env["SQL_PW"]};
               database={env["SQL_DB"]}
            ";

var con = new MySqlConnection(cs);
con.Open();
Console.WriteLine($"MySQL version : {con.ServerVersion}");
con.Close();

Prepared Statements

After the connection has been established you will typically perform one or more operations, this is enabled by the MySqlCommand object. There are three primary methods that you will usually use:

  • ExecuteReader: Query the database.
  • ExecuteNonQuery: Insert, Update, Delete data.
  • ExecuteScalar: Retrieve a single value.

Although possible to build SQL queries using string interpolation, it is not advisable to put query values directly in the query string. Doing so leaves your database open to injection attacks. Instead of placing the value into the query directly, we will use placeholders, prefixed with ‘@’.

Unsafe

string sql = $"SELECT Name FROM Country WHERE Continent = {continent};

Safe

string sql = "SELECT Name FROM Country WHERE Continent = @continent;

When using parameterized strings the values are added using the Parameters field of the MySqlCommand object.

Update the Database

To insert a new record into the database we use the ExecuteNonQuery method.


MySqlCommand.ExecuteNonQuery()

Executes a SQL statement returning the affected row count. [*]

        string sql = "INSERT INTO users(username, password, email) values (@username, @password, @email)";
        using var cmd = new MySqlCommand(sql, con);
        cmd.Parameters.AddWithValue("@username", username);
        cmd.Parameters.AddWithValue("@password", password);
        cmd.Parameters.AddWithValue("@email", email);
        cmd.ExecuteNonQuery();

Query the Database

Sends the CommandText value to MySqlConnection and builds a MySqlDataReader object.

The Reader object contains the rows that result from the SQL command execution. The Read method advances the reader to the next row, returning true if a row exists. The using keyword ensures that classes that implement the IDisposable interface call their dispose method.

using (MySqlDataReader reader = cmd.ExecuteReader()){
    while (reader.Read()) {
        ...
    }
}

You retrieve values from the current row with the class indexer which can retrieve values by column index or name. There are also several methods to retrieve a value by type, for example, GetInt32. The following statements perform the same action.

var name = reader[0];
var name = reader["name"];
var name = reader.GetString(0);
var name = reader.GetString("name");

External Reading

Official MySQL/Net Tutorial

https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-sql-command.html

Nuget MySQL Package

https://www.nuget.org/packages/MySql.Data/

MySQL/Net API

https://dev.mysql.com/doc/dev/connector-net/8.0/