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 result

If you'd like just one result row, use database.Select:

    using Api.Database;
   
    ...
    var query = Query.Select(typeof(UserCountResult), "site_user"); // Give a hint of which main table you are using.
    query.SetRawQuery("SELECT MyStoredFunction() as Count, Id from site_user where Id=@id"); // Set the query.
    
    // Get the result for user with ID 14:
    var userCounts = await database.Select<UserCountResult, uint>(context, query, typeof(UserCountResult), 14);

The Select method supports one parameter called @id and its value is of the type you specify (uint in the example above). It is used with @id in your raw query and can also be ignored - you don't have to use it if you don't need it, in which case, just pass a 0 as its value.

If you need additional parameters, it is suggested to use MySqlCommand directly instead. See below for more details on that.

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;
}

Getting one or more results

A list of multiple results can be obtained using database.List:

    using Api.Database;
   
    ...
    var query = Query.Select(typeof(UserCountResult), "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;
}

Using MySqlCommand directly

The main thing you'll want in order to use the underlying Oracle MySqlCommand object is a database connection instance. You can get this connection via MySQLDatabaseService's GetConnection() method:

using var connection = db.GetConnection();
await connection.OpenAsync();
var cmd = new MySqlCommand("SELECT * ..", connection);

// cmd.Parameters.Add - see Oracle's documentation for more on those.

if(await cmd.ExecuteNonQueryAsync() > 0) {
    // Success!
}

This of course gives you more options but you'll need to map any results to an object yourself.