Pustalorc.MySqlConnectorWrapper 2.3.0

Before you begin, please make sure that your current solution has installed the nuget package of this library.

Configuration Setup

Firstly, you need a configuration for the connector to use. Without a configuration, the connector will not know what server to connect to, which database to use, which user to use, etc.

You will want to create a class and have it inherit the Interface IConnectorConfiguration. For example:

public class DatabaseConfig : IConnectorConfiguration
{
    public string DatabaseAddress => "localhost";
    public ushort DatabasePort => 3306;
    public string DatabaseUsername => "root";
    public string DatabasePassword => "myPassword";
    public string DatabaseName => "database";
    public string ConnectionStringExtras => "";
    public bool UseCache => true;
    public double CacheRefreshRequestInterval => 1250;
    public ulong CacheSize => 15;
    public string TableName => "test";
}

Note: These are { get; } only properties, but you can make them { get; set; } if you wish to be able to modify them mid run-time, or if you wish to serialise them into a configuration file.

Example configuration #2.

Connector setup

Once you have a default configuration and you think you are ready to move on, you can then start creating your own Database class.

This class will serve as another layer of wrapping, one that will check data against anything stored, or simply just request query executions.

public class Database : ConnectorWrapper<DatabaseConfig>
{
    public Database(DatabaseConfig config) : base(config)
    {
    }
}

It should be noted that since the wrapper doesn't know what tables should be in the database, in your Database class you will need a CheckCreateSchema() method in order to check and create the necessary tables with the correct structure.

Here's an example on how that should look like and work:

public class Database : ConnectorWrapper<DatabaseConfig>
{
    public Database(DatabaseConfig config) : base(config)
    {
        CheckCreateSchema();
    }

    private void CheckCreateSchema()
    {
        var output = ExecuteQuery(new Query($"SHOW TABLES LIKE '{Configuration.TableName}';", EQueryType.Scalar));
        if (output.Output != null) return;

        ExecuteQuery(new Query($"CREATE TABLE `{Configuration.TableName}` (`Id` INT NOT NULL, PRIMARY KEY (`Id`));", EQueryType.NonQuery));
    }
}

As you noticed, we created new instances of a class called Query. This class holds the basic information of a query, so you can store it if you need to. It holds an identifier (which can be nullable, but if so will default to the query string), a query string, the query type, if the query should be cached (defaults to false), the parameters of the query (defaults to an empty list), and the callbacks for that query.

Once you have a class that inherits from ConnectorWrapper and a CheckCreateSchema(), all that's left to do is for you to write as many methods as you need to use to add data, retrieve data or similar.

Each one of those methods should call ExecuteQuery() if it will fully be synchronous, or ExecuteQueryAsync() if it will run asynchronously.

public class Database : ConnectorWrapper<DatabaseConfig>
{
    public Database(DatabaseConfig config) : base(config)
    {
        CheckCreateSchema();
    }

    private void CheckCreateSchema()
    {
        var output = ExecuteQuery(new Query($"SHOW TABLES LIKE '{Configuration.TableName}';", EQueryType.Scalar));
        if (output.Output != null) return;

        ExecuteQuery(new Query($"CREATE TABLE `{Configuration.TableName}` (`Id` INT NOT NULL, PRIMARY KEY (`Id`));", EQueryType.NonQuery));
    }

    public void AddNewRow()
    {
        ExecuteQuery(new Query($"INSERT INTO `{Configuration.TableName}` (`Id`) VALUES(0);", EQueryType.NonQuery));
    }

    public int GetRow(int id)
    {
        var output = ExecuteQuery(new Query($"SELECT * FROM `{Configuration.TableName}` WHERE `id`=0;", EQueryType.Scalar)).Output;
        if (output == null) return -1;
        return int.TryParse(output.ToString(), out var result) ? result : -1;
    }
}

Finally, if your query uses MySqlParameters, you can insert them with the query and they'll automatically be added before execution.

For example:

ExecuteQuery(new Query($"INSERT INTO `{Configuration.TableName}` (`Id`) VALUES(@id);", EQueryType.NonQuery, new MySqlParameter("@id", 0)));