I’m porting a working backend written in Python to .NET microservice written in C#, using the MySQLConnector and mySQL. I have a simple C# Database class instantiated from Program main as one of the first lines. It connects to the mysql service, then checks for existence of DB and creates it and the tables if needed, then populates it with a few seed records to make it operational.
I’m trying to do this with proper error checking and structure and learn the right way to do things.
My problems are:
- I’m not understanding how to structure the async code properly.
- One of the Async calls is blocking and never returning
- I’m not sure how to do an Async call to get a connection for a method that needs to make a query
Below is my current class, followed by my questions, and the original working Python database module below that. Any help, tips or tricks appreciated.
using System;
using System.Text.RegularExpressions;
using MySqlConnector;
// MySqlConnector notes and refs
// https://mysqlconnector.net/api/mysqlconnector/mysqlerrorcodetype/
// https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-reference.html
// Adventureworks naming conventions:
// /questions/3593582/database-naming-conventions-by-microsoft
public class Database
{
static private Database instance;
private bool isReady;
private string dbName;
private string dbUser;
private string dbPassword;
private string dbHost;
private string dbPort;
private string connectionString;
public Database(string dbName, string dbUser, string dbPassword, string dbHost, string dbPort)
{
this.dbName = dbName;
this.dbUser = dbUser;
this.dbPassword = dbPassword;
this.dbHost = dbHost;
this.dbPort = dbPort;
this.connectionString = $"server={dbHost};user={dbUser};port={dbPort};password={dbPassword}";
instance = this;
Initialize();
}
// is this the right approach for various methods in the service to get a connection?
static public MySqlConnection Connect()
{
try
{
var connection = new MySqlConnection(instance.connectionString);
connection.Open();
var command = new MySqlCommand($"USE {instance.dbName}", connection);
command.ExecuteNonQuery();
return connection;
}
catch (MySqlException ex)
{
switch ((MySqlErrorCode)ex.Number)
{
case MySqlErrorCode.AccessDenied:
Console.WriteLine("Access denied: Something is wrong with your user name or password");
break;
case MySqlErrorCode.UnknownDatabase:
Console.WriteLine($"Database {instance.dbName} does not exist");
break;
default:
Console.WriteLine(ex.Message);
break;
}
Environment.Exit(1);
return null; // this line will never be reached, but it's necessary to satisfy the return type of the function.
}
}
static public bool IsReady()
{
return (instance != null && instance.isReady);
}
//*************************************************************************
// Private methods
private async Task Initialize()
{
// create a connection and try to open the DB service
using var connection = new MySqlConnection(connectionString);
try
{
await connection.OpenAsync();
}
catch (MySqlException exConnection)
{
Console.WriteLine(exConnection.Message);
Environment.Exit(1);
}
// service exists, now check if our database exists
try
{
// if it got here, then it found it, need to make it active via USE
using var command = new MySqlCommand($"USE {dbName}", connection);
await command.ExecuteNonQueryAsync();
}
catch (MySqlException exUseDB)
{
if ((MySqlErrorCode)exUseDB.Number == MySqlErrorCode.UnknownDatabase)
{
Console.WriteLine($"Database {dbName} does not exist");
Console.WriteLine($"Creating {dbName}...");
// create the database...
try
{
// load the schema file and run the sql to create the db and tables
string schema = File.ReadAllText("CLIService/SQL/bbe-schema.sql");
string[] sqlCommands = schema.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
// create the tables
foreach (string sqlCommand in sqlCommands)
{
string cleanedCommand = Regex.Replace(sqlCommand, "[nr]", "");
if (!string.IsNullOrEmpty(cleanedCommand))
{
try
{
var command = new MySqlCommand(cleanedCommand, connection);
await command.ExecuteNonQueryAsync();
}
catch (MySqlException exSqlCommand)
{
Console.WriteLine(exSqlCommand.Message);
Environment.Exit(1);
}
}
}
}
catch (MySqlException exCreateDB)
{
Console.WriteLine($"Failed creating database: {exCreateDB.Message}");
Environment.Exit(1);
}
}
else
{
Console.WriteLine(exUseDB.Message);
Environment.Exit(1);
}
}
// database exists and is used, now populate it if empty
try
{
// there is always 1 admin account created by default, so try to get one record
var command = new MySqlCommand("SELECT * FROM account LIMIT 1", connection);
// should I use ExecuteReaderAsync() instead? If so how?
using (var reader = command.ExecuteReader())
{
// if no records, means it's a new, empty database
if (!reader.HasRows)
{
// load the data file and run the sql to create the table records of initial data
string schema = File.ReadAllText("CLIService/SQL/bbe-data.sql");
string[] sqlCommands = schema.Split(new[] { ";" }, StringSplitOptions.RemoveEmptyEntries);
// create the records
foreach (string sqlCommand in sqlCommands)
{
string cleanedCommand = Regex.Replace(sqlCommand, "[nr]", "");
if (!string.IsNullOrEmpty(cleanedCommand))
{
try
{
command = new MySqlCommand(cleanedCommand, connection);
#if USE_ASYNC_AWAIT
// the following line if used it hangs/blocks and never returns
await command.ExecuteNonQueryAsync();
#else
// alternatively the following line returns: "56 Inserted System.Threading.Tasks.Task`1[System.Int32] records"
// but no records are inserted into the DB
var rowCount = command.ExecuteNonQueryAsync();
Console.WriteLine($"Inserted {rowCount} records");
#endif
}
catch (MySqlException exSqlCommand)
{
Console.WriteLine(exSqlCommand.Message);
Environment.Exit(1);
}
}
}
}
}
}
catch (MySqlException exSelectDB)
{
Console.WriteLine($"An error occurred: {exSelectDB.Message}");
}
// database opened, selected and populated
isReady = true;
Console.WriteLine("Database is ready");
}
}
Questions:
- What am I doing wrong in the Initialize() method for the USE_ASYNC_AWAIT section of the code? The current code successful connects to the service, detects the DB is missing and creates it and the tables using a file of sql commands, detects there are no records, loads the data file and loops through the insert commands. In the code above where is says
#if USE_ASYNC_AWAIT, if USE_ASYNC_AWAIT is true thenawait command.ExecuteNonQueryAsyncblocks and never returns, and if false it completes with 56 tasks created but no records are ever created. - Is the overall structure of Initialize method the right way to do things and handle exceptions?
- How should the Connect function be structured using Async when services ask for a connection to run queries on?
Original working Python backend:
import mysql
from mysql.connector import errorcode
import re
import os
import tomllib
def connect(config):
# open the database connection
try:
connection = mysql.connector.connect(pool_name="bbepool", **config["DATABASE"]["CONNECTION"])
except mysql.connector.Error as err:
if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
print("Access denied: Something is wrong with your user name or password")
else:
print(err)
exit(1)
# set the active database
name = config["DATABASE"]["NAME"]
connection.name = name
cursor = connection.cursor()
try:
cursor.execute("USE {}".format(name))
except mysql.connector.Error as err:
print(err)
exit(1)
return connection
def initialize(config):
connection = None
cursor = None
# open the database connection
try:
connection = mysql.connector.connect(**config["DATABASE"]["CONNECTION"])
cursor = connection.cursor()
except mysql.connector.Error as err:
print(err)
exit(1)
# connection successful, check if the db exists already
name = config["DATABASE"]["NAME"]
try:
cursor.execute("USE {}".format(name))
except mysql.connector.Error as err:
print("Database {} does not exist".format(name))
if err.errno == errorcode.ER_BAD_DB_ERROR:
print("Creating {}...".format(name))
try:
cursor.execute("CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8mb4'".format(name))
except mysql.connector.Error as err:
print("Failed creating database: {}".format(err))
exit(1)
print("Database {} created successfully".format(name))
# set the active database
connection.database = name
else:
print(err)
exit(1)
# also set connection to use the new database
config["DATABASE"]["CONNECTION"]["database"] = config["DATABASE"]["NAME"]
# load and run schema
# safe to do as it doesn't create tables if they exist
with open(config["DATABASE"]["SCHEMA"], "rb") as f:
tables = f.read().decode("utf-8").split(";")
for table in tables:
table = re.sub("[nr]", "", table)
if len(table) > 0:
try:
cursor.execute(table)
connection.commit()
except mysql.connector.Error as err:
print(err)
exit(1)
# if there is test data
if config["DATABASE"]["DATA"] != "":
# if the db is empty
cursor.execute("SELECT * FROM account LIMIT 1")
# must fetch the data for the cursor
cursor.fetchone()
if cursor.rowcount < 1:
# populate
with open(config["DATABASE"]["DATA"], "rb") as f:
text = f.read().decode("utf-8")
lines = text.split(");n")
for line in lines:
if len(line) > 0:
try:
cursor.execute(line + ");")
connection.commit()
except mysql.connector.Error as err:
print(err)
exit(1)
# cleanup
cursor.close()
connection.close()
if __name__ == "__main__":
# ensure the current working directory is same as script
os.chdir(os.path.dirname(__file__))
# module vars we use multiple places
config = {}
# In VS Code we can test development or production by setting
# "DEPLOYMENT_ENVIRONMENT": "development", "testing" or "production" in .vscode/launch.json
# but in case the environment variable isn't set, make a default to development
if "DEPLOYMENT_ENVIRONMENT" not in os.environ:
os.environ["DEPLOYMENT_ENVIRONMENT"] = "development"
config_file = "../Settings/config." + os.environ["DEPLOYMENT_ENVIRONMENT"] + ".toml"
with open(config_file, "rb") as f:
config = tomllib.load(f)
# initialize database before switching directories
initialize(config)