MySqlConnector


Best Practices

Store bool as TINYINT(1)

In MySQL Server, BOOL is an alias for TINYINT(1). The MySQL ADO.NET connector understands this convention and will marshal TINYINT(1) back to managed code as the C# bool type (System.Boolean).

Use the BOOL alias when defining columns in your SQL statements. Do not use BIT(1) (which gets mapped as a ulong) to represent a Boolean value.

Avoid TINYINT(1)

As a corollary to the above, avoid explicitly using TINYINT(1). If you need a one-byte integer, use TINYINT (or TINYINT UNSIGNED). The (1) suffix simply indicates the “display width” (which is typically ignored by .NET programs), not the number of bytes used for storage. (And for a bool C# value, use BOOL in SQL.)

Asynchronous Operation

MySqlConnector is fully asynchronous, supporting the async ADO.NET methods added in .NET 4.5 without blocking or using Task.Run to run synchronous methods on a background thread. Programmers implementing MySqlConnector should be familiar with Async/Await - Best Practices in Asynchronous Programming.

Always Use Async when possible

ADO.NET ClassAsynchronous Method
(always use when possible)
Synchronous Method
(avoid when possible)
DbConnectionOpenAsyncOpen
*MySqlConnection.BeginTransactionAsyncBeginTransaction
DbCommandExecuteNonQueryAsyncExecuteNonQuery
ExecuteReaderAsyncExecuteReader
ExecuteScalarAsyncExecuteScalar
DbDataReaderNextResultAsyncNextResult
ReadAsyncRead
DbTransaction*MySqlTransaction.CommitAsyncCommit
*MySqlTransaction.RollbackAsyncRollback

*Async Transaction methods are not part of ADO.NET, they are provided by MySqlConnector to allow database code to remain fully asynchronous.

Exceptions: DbDataReader.GetFieldValueAsync and IsDBNullAsync

Once DbDataReader.ReadAsync (or DbDataReader.Read) has returned true, the full contents of the current row will be in memory. Calling GetFieldValue<T> will return the value immediately (without blocking on I/O). It will have higher performance than GetFieldValueAsync<T> because it doesn’t have to allocate a Task<T> to store the result. There is no performance benefit to using the DbDataReader.GetFieldValueAsync<T> method.

Similarly, prefer to call IsDBNull instead of IsDBNullAsync; the information is already available and IsDBNull can return it immediately. (The async performance penalty isn’t quite as bad because IsDBNullAsync uses cached Task<bool> objects for its true and false return values.)

Example Console Application

In order to get the full benefit of asynchronous operation, every method in the call stack that eventually calls MySqlConnector should be implemented as an async method.

Example assumes a configured AppDb object in the MySqlConnector.Examples namespace.

using System.Threading.Tasks;
using System.Collections.Generic;

namespace MySqlConnector.Examples
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            var tasks = new List<Task>();
            for (var i=0; i<100; i++)
            {
                tasks.Add(Controllers.SleepOne());
            }
            // these 100 queries should all complete in around
            // 1 second if "Max Pool Size=100" (the default)
            await Task.WhenAll(tasks);
        }
    }

    public class Controllers
    {
        public static async Task SleepOne()
        {
            using (var db = new AppDb())
            {
                await db.Connection.OpenAsync();
                using (var cmd = db.Connection.CreateCommand())
                {
                    cmd.CommandText = @"SELECT SLEEP(1)";
                    await cmd.ExecuteNonQueryAsync();
                }
            }
        }
    }
}

Synchronous Operation

Using Synchronous Methods can have adverse effects on the managed thread pool and cause slowdowns or lock-ups if not properly tuned. The recommended approach is to use all Asynchronous Methods.

If you must use synchronous methods, ensure that your thread pool is at least the size of the number of concurrent connections you plan to support. For example, if you are creating a web server using synchronous methods that needs to support serving 500 Requests Per Second, set the minimum thread pool size to 500.

Example csproj configuration:

<PropertyGroup>
  <ServerGarbageCollection>true</ServerGarbageCollection>
  <ConcurrentGarbageCollection>true</ConcurrentGarbageCollection>
  <ThreadPoolMinThreads>500</ThreadPoolMinThreads>
</PropertyGroup>