Raw Database Queries - Revision history

Luke: /* Getting one result */

2023-08-03T19:01:14Z

Getting one result

← Older revision		Revision as of 19:01, 3 August 2023
Line 42:		Line 42:

	...		...
	var query = Query.Select(typeof(~~User~~), "site_user"); // Give a hint of which main table you are using.		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.		query.SetRawQuery("SELECT MyStoredFunction() as Count, Id from site_user where Id=@id"); // Set the query.

Luke: /* Getting one or more results */

2023-08-03T19:00:57Z

Getting one or more results

← Older revision		Revision as of 19:00, 3 August 2023
Line 73:		Line 73:

	...		...
	var query = Query.Select(typeof(~~User~~), "site_user"); // Give a hint of which main table you are using.		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.		query.SetRawQuery("SELECT count(*) as Count, Id from site_user"); // Set the query.

Luke: /* Getting one result */

2023-08-03T18:51:36Z

Getting one result

← Older revision		Revision as of 18:51, 3 August 2023
Line 51:		Line 51:
	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.		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.		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:		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:

Luke: /* Getting one result */

2023-08-03T18:51:14Z

Getting one result

← Older revision		Revision as of 18:51, 3 August 2023
Line 49:		Line 49:
	</syntaxhighlight>		</syntaxhighlight>

	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.		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 ~~args~~, it is suggested to use MySqlCommand directly instead.		If you need additional parameters, it is suggested to use MySqlCommand directly instead.

	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:		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:

Luke at 18:50, 3 August 2023

2023-08-03T18:50:19Z

← Older revision		Revision as of 18:50, 3 August 2023
Line 32:		Line 32:

	These are sometimes used from [[Automations]] to do bulk database maintenance on a schedule.		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:

			<syntaxhighlight lang="csharp">

			using Api.Database;

			...
			var query = Query.Select(typeof(User), "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);
			</syntaxhighlight>

			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 args, it is suggested to use MySqlCommand directly instead.

			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:

			<syntaxhighlight lang="csharp">

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

			</syntaxhighlight>

	== Getting one or more results ==		== Getting one or more results ==

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

	<syntaxhighlight lang="csharp">		<syntaxhighlight lang="csharp">
Line 59:		Line 90:

	</syntaxhighlight>		</syntaxhighlight>

			== 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:

			<syntaxhighlight lang="csharp">
			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!
			}

			</syntaxhighlight>

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

Luke: Created page with "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..."

2023-08-03T18:34:46Z

Created page with "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..."

New page

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 [https://dev.mysql.com/doc/connector-net/en/connector-net-installation-binary-nuget.html 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 [[Services|any other service]] which is to say you can either use dependency injection or Services.Get like so:

<syntaxhighlight lang="csharp">
using Api.Database;

...

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

= 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:

<syntaxhighlight lang="csharp">
using Api.Database;

...

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

var succeeded = await database.Run("UPDATE ...");
</syntaxhighlight>

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:

<syntaxhighlight lang="csharp">

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));
</syntaxhighlight>

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:

<syntaxhighlight lang="csharp">

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

</syntaxhighlight>