Raw Database Queries

SocialStack like many platforms suggests using its general purpose API's instead of going under the hood to the database level to execute queries. This is because SocialStack isn't strictly tied to any particular database engine (MySQL being just the default one) and as a result for code to be easier to share the general use API's are always recommended. However though there are certainly times when you need an escape hatch and being able to just run a query can be useful.

MySQL Environment

SocialStack installs the Oracle MySQL C# library via nuget which you can directly use to run MySqlCommand objects at the rawest level with its API. SocialStack also provides some abstractions to help catch common cases too which are available via the MySQLDatabaseService. You can access this service the same way as any other service which is to say you can either use dependency injection or Services.Get like so:

    using Api.Database;
   
    ...

    var database = Services.Get<MySQLDatabaseService>();

Running a raw query

As SocialStack can't guess what kind of result fields will be in your query results, there are a few different convenience methods for running queries on the database.

Executing a command with no results

If you have no results and only want a true/ false if the query succeeded, use database.Run like so:

    using Api.Database;
   
    ...

    var database = Services.Get<MySQLDatabaseService>();
    
    var succeeded = await database.Run("UPDATE ...");

These are sometimes used from Automations to do bulk database maintenance on a schedule.

Getting one or more results

A list of one or more results can be obtained using database.List:

    using Api.Database;
   
    ...
    var query = Query.Select(typeof(User), "site_user"); // Give a hint of which main table you are using.
    query.SetRawQuery("SELECT count(*) as Count, Id from site_user"); // Set the query.
    
    // Get the result(s):
    var userCounts = await database.List<UserCountResult>(context, query, typeof(UserCountResult));

Elsewhere you will need a class which describes the result row. In the example above, it is the class called UserCountResult, and it has at least 2 fields in the select statement so it would be at least like this:

public class UserCountResult{
    public int Id;
    public int Count;
}