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
