Sphere10.Framework.Data.MySQL 3.1.2

💾 Sphere10.Framework.Data.MySQL

MySQL data access through the shared Sphere10 IDAC abstraction. The package supplies a MySqlConnector-backed DAC, connection-string and database administration helpers, and a database manager. SQL generation comes from MySQLBuilder in the shared data package.

📦 Installation

dotnet add package Sphere10.Framework.Data.MySQL

Targets .NET 10 (net10.0). The package brings in MySqlConnector, Sphere10.Framework.Data and the core framework. An existing database and credentials are required to execute the examples; server features depend on the server and provider versions in use.

⚡ Open a scope and query

Set MYSQL_CONNECTION_STRING to your application's connection string. The remaining examples reuse dac and these imports.

using System;
using System.Data;
using Sphere10.Framework;
using Sphere10.Framework.Data;

var connectionString = Environment.GetEnvironmentVariable("MYSQL_CONNECTION_STRING");
Guard.ArgumentNotNullOrEmpty(connectionString, nameof(connectionString));
var dac = new MySQLDAC(connectionString);

using (dac.BeginScope()) {
	var serverVersion = dac.ExecuteScalar<string>("SELECT VERSION()");
	Console.WriteLine(serverVersion);
}

Constructing a DAC, including through Tools.MySQL.Open(...), only prepares its configuration. BeginScope() opens the connection by default. The scope owns the connection; the DAC itself is not disposable.

🧩 Public API

Types are in Sphere10.Framework.Data; the static facade is in the global Tools namespace.

🔧 Connection configuration

For separate configuration fields, use the facade:

var configuredDac = Tools.MySQL.Open(
	server: "localhost", database: "appdb", username: "appuser",
	password: Environment.GetEnvironmentVariable("MYSQL_PASSWORD"),
	port: 3306, pooling: true, maxPoolSize: 50,
	connectionTimeout: TimeSpan.FromSeconds(15), applicationName: "ExampleApp"
);

CreateConnectionString accepts the same connection options. Omitted options retain provider defaults.

For certificate-verification modes and other provider options, build a full connection string with MySqlConnectionStringBuilder and pass it to new MySQLDAC(connectionString). The Boolean TLS option exposes only the two modes listed above.

The shared DAC sets CommandTimeout = 0 for ExecuteNonQuery, ExecuteReader and ExecuteBatch. ExecuteScalar keeps the provider command default. To enforce a per-command timeout, create a command explicitly as below.

🔎 Bound parameters

The DAC's string-based execution methods execute SQL text. ExecuteQuery(sql, params object[] args) formats SQL through the builder; it does not bind ADO.NET parameters. Create a command for bound values:

using (var scope = dac.BeginScope()) {
	using var command = scope.Connection.CreateCommand();
	command.CommandText = "SELECT @value";
	command.CommandTimeout = 30;
	if (scope.Transaction is RestrictedTransaction transaction)
		command.Transaction = transaction.DangerousInternalTransaction;

	var parameter = command.CreateParameter();
	parameter.ParameterName = "@value";
	parameter.DbType = DbType.Int32;
	parameter.Value = 42;
	command.Parameters.Add(parameter);

	var echoedValue = Tools.Object.ChangeType<int>(command.ExecuteScalar());
	Console.WriteLine(echoedValue); // 42
}

The transaction assignment connects a manually created command to any local transaction inherited by the scope. DAC execution methods perform this assignment internally. Dispose commands and readers before their scope ends; do not close or dispose scope.Connection directly. Keep an outer scope alive while consuming ExecuteReader results, or use ExecuteQuery to materialize the result before returning.

🔄 Transactions and lifecycle

The following example assumes an existing app_counters table with numeric id and counter_value columns and rows 1 and 2:

using (var scope = dac.BeginScope()) {
	scope.BeginTransaction(IsolationLevel.ReadCommitted);
	dac.ExecuteNonQuery("UPDATE app_counters SET counter_value = counter_value + 1 WHERE id = 1");
	dac.ExecuteNonQuery("UPDATE app_counters SET counter_value = counter_value + 1 WHERE id = 2");
	scope.Commit();
}

DefaultIsolationLevel is ReadCommitted. Call scope.Rollback() when explicitly abandoning a transaction. Disposal releases an owned transaction and connection; it does not commit them.

With the default UseScopeOsmosis = true, nested scopes with the same connection string share the connection and current transaction. Setting it to false separates different DAC instances; scopes on the same instance can still nest. A nested Commit does not commit the database transaction independently. An explicit nested Rollback votes for rollback when the owning scope commits. These are shared transactions, not savepoints.

Scopes also enlist newly owned connections in an ambient System.Transactions.TransactionScope through the provider. In that case, let the ambient scope control completion; mixing its transaction with DACScope.BeginTransaction, Commit or Rollback is prohibited. Provider and platform support govern ambient/distributed transaction availability.

🗂️ Schema and SQL dialect

GetSchema() queries information_schema for the currently selected DATABASE(). It produces the shared DBSchema representation of tables, columns, keys and trigger names. It does not enumerate every database on the server. Auto-increment detection uses the column's EXTRA metadata; sequence fields are not populated.

var schema = dac.GetSchema();
foreach (var table in schema.Tables)
	Console.WriteLine(table.Name);

MySQLBuilder is implemented in the shared data project:

The paging row describes the emitted SQL fragment. The inherited SQLBuilderBase.Select currently inserts that fragment before the selected columns, so Select(limit: ...) does not produce valid paging SQL for this provider. Use an explicit SQL query with the paging clause in its correct position.

Insert returns the affected-row count, not a generated identity. Builder methods generate SQL text; they do not create connections or bind command parameters.

🛠️ Database administration

MySQLDatabaseManager implements the common DatabaseManagerBase contract. Its GenerateConnectionString delegates to Tools.MySQL; DatabaseExists, CreateEmptyDatabase and DropDatabase delegate to the corresponding administration helpers.

Creation defaults to AlreadyExistsPolicy.Error; Skip preserves an existing database and Overwrite drops it before recreation. These calls require appropriate database administration privileges. Database names are interpolated into administration SQL, so supply trusted, validated configuration identifiers.

The manager reconstructs maintenance connections from server, database, username, password and port; additional settings in its input connection string are not forwarded. CreateApplicationDatabase throws NotSupportedException: application tables, migrations and seed data are the application's responsibility.

🚧 Implementation boundaries

• BulkInsert throws NotImplementedException; the shared BulkInsertAsync wrapper reaches the same unimplemented method.
• Shared IDACAsyncExtensions use Task.Run around synchronous DAC methods. They do not expose the provider's native asynchronous I/O API.
• SQL dialect and metadata support are limited to the implementations described above. This package does not supply an ORM, an application schema generator or a migration system.

🔗 Related projects and source

• Sphere10.Framework.Data — shared DAC contracts, scopes, SQL builders and schema types.
• Sphere10.Framework — common utilities, guards, logging and transaction scopes.
• Sphere10.Framework.Windows.Forms.MySQL — Windows Forms connection UI for this provider.
• MySQLDAC, Tools.MySQL, MySQLDatabaseManager — provider implementation.
• MySQLBuilder — dialect implementation.

From the repository root:

dotnet build src/Sphere10.Framework.Data.MySQL/Sphere10.Framework.Data.MySQL.csproj -c Release

📄 License

Distributed under the MIT license. See the repository LICENSE.