Pustalorc.MySqlConnectorWrapper 3.0.2

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 ConnectionStringFormat => "SERVER={0};DATABASE={1};UID={2};PASSWORD={3};PORT={4};";
    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 bool EnableCacheRefreshes => 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.

Connector setup

Once you have a default configuration and you think you are ready to move on, you can then use the connector.

There are 2 ways to access or use a creator, instantiating the base class, or inheriting the base class into another class.

For simplicity's sake, I will cover instantiation only.

Instantiating the wrapper is simple, you simply have to provide the type used for the configuration, in this example it's DatabaseConfig, and a secondary type for the key to be able to identify the queries and their results in cache. For this example we will use string for the second type.

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

public class StorageService
{
    public ConnectorWrapper<DatabaseConfig, string> Database;

    public Database(DatabaseConfig config)
    {
        Database = new ConnectorWrapper<DatabaseConfig, string>(config, StringComparer.OrdinalIgnoreCase);

        if (Database.TestConnection(out var exception))
            CheckCreateSchema();
        else
            Log.Exception(exception);
    }

    private void CheckCreateSchema()
    {
        // Since we dont make the query cacheable, we do not need a unique key, setting it to "" should be enough.
        var output = Database.ExecuteQuery(Database.CreateQuery("", $"SHOW TABLES LIKE '{Configuration.TableName}';", EQueryType.Scalar));

        if (output.Output != null) return;

        Database.ExecuteQuery(Database.CreateQuery("", $"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 a key, 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 (defaults to an empty list).

Once you have an instance of ConnectorWrapper and a CheckCreateSchema(), all that's left for you to do is to write as many methods as you need 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. There's also ExecuteTransaction() and ExecuteTransactionAsync() for running multiple queries, both synchronously and asynchronously respectively. (Note, with a transaction, on any of the callbacks you can make validations to the db and cause a rollback by throwing an exception).

Here's an example of a fully implemented system:

public class StorageService
{
    public ConnectorWrapper<DatabaseConfig, string> Database;

    public Database(DatabaseConfig config)
    {
        Database = new ConnectorWrapper<DatabaseConfig, string>(config, StringComparer.OrdinalIgnoreCase);

        if (Database.TestConnection(out var exception))
            CheckCreateSchema();
        else
            Log.Exception(exception);
    }

    private void CheckCreateSchema()
    {
        // Since we dont make the query cacheable, we do not need a unique key, setting it to "" should be enough.
        var output = Database.ExecuteQuery(Database.CreateQuery("", $"SHOW TABLES LIKE '{Configuration.TableName}';", EQueryType.Scalar));

        if (output.Output != null) return;

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

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

    public int GetRow(int id)
    {
        var output = Database.ExecuteQuery(Database.CreateQuery("", $"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 or needs MySqlParameters, you can insert them with the query and they'll automatically be added before execution.

For example:

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