QuerySql.cs

// auto-generated by sqlc - do not edit
// ReSharper disable UseObjectOrCollectionInitializer
// ReSharper disable UseAwaitUsing
// ReSharper disable ConvertToUsingDeclaration
// ReSharper disable NotAccessedPositionalProperty.Global
// ReSharper disable UnusedAutoPropertyAccessor.Global
namespace MySqlConnectorDapperLegacyExampleGen
{
    using CsvHelper;
    using CsvHelper.Configuration;
    using CsvHelper.TypeConversion;
    using Dapper;
    using MySqlConnector;
    using NodaTime;
    using NodaTime.Extensions;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.Common;
    using System.Globalization;
    using System.IO;
    using System.Text;
    using System.Text.Json;
    using System.Threading;
    using System.Threading.Tasks;

    public class QuerySql : IDisposable
    {
        public QuerySql()
        {
            Utils.ConfigureSqlMapper();
            DefaultTypeMap.MatchNamesWithUnderscores = true;
        }

        public QuerySql(string connectionString) : this()
        {
            this.ConnectionString = connectionString;
            _dataSource = new Lazy<MySqlDataSource>(() =>
            {
                var builder = new MySqlConnectionStringBuilder(connectionString);
                builder.ConnectionReset = true;
                // Pre-warm connection pool with minimum connections
                if (builder.MinimumPoolSize == 0)
                    builder.MinimumPoolSize = 1;
                return new MySqlDataSourceBuilder(builder.ConnectionString).Build();
            }, LazyThreadSafetyMode.ExecutionAndPublication);
        }

        private QuerySql(MySqlTransaction transaction) : this()
        {
            this.Transaction = transaction;
        }

        public static QuerySql WithTransaction(MySqlTransaction transaction)
        {
            return new QuerySql(transaction);
        }

        private MySqlTransaction Transaction { get; }
        private string ConnectionString { get; }

        private readonly Lazy<MySqlDataSource> _dataSource;
        private MySqlDataSource GetDataSource()
        {
            if (_dataSource == null)
                throw new InvalidOperationException("ConnectionString is required when not using a transaction");
            return _dataSource.Value;
        }

        public void Dispose()
        {
            GC.SuppressFinalize(this);
            if (_dataSource?.IsValueCreated == true)
                _dataSource.Value.Dispose();
        }

        private const string GetAuthorSql = "SELECT id, name, bio FROM authors WHERE name = @name LIMIT 1";
        public class GetAuthorRow
        {
            public long Id { get; set; }
            public string Name { get; set; }
            public string Bio { get; set; }
        };
        public class GetAuthorArgs
        {
            public string Name { get; set; }
        };
        public async Task<GetAuthorRow> GetAuthorAsync(GetAuthorArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("name", args.Name);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetAuthorRow>(GetAuthorSql, queryParams);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetAuthorRow>(GetAuthorSql, queryParams, transaction: this.Transaction);
        }

        private const string ListAuthorsSql = @"SELECT id, name, bio 
                                                FROM authors
                                                ORDER BY name
                                                LIMIT @limit OFFSET @offset";
        public class ListAuthorsRow
        {
            public long Id { get; set; }
            public string Name { get; set; }
            public string Bio { get; set; }
        };
        public class ListAuthorsArgs
        {
            public int Limit { get; set; }
            public int Offset { get; set; }
        };
        public async Task<List<ListAuthorsRow>> ListAuthorsAsync(ListAuthorsArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("limit", args.Limit);
            queryParams.Add("offset", args.Offset);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryAsync<ListAuthorsRow>(ListAuthorsSql, queryParams);
                    return result.AsList();
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return (await this.Transaction.Connection.QueryAsync<ListAuthorsRow>(ListAuthorsSql, queryParams, transaction: this.Transaction)).AsList();
        }

        private const string CreateAuthorSql = "INSERT INTO authors (id, name, bio) VALUES (@id, @name, @bio)";
        public class CreateAuthorArgs
        {
            public long Id { get; set; }
            public string Name { get; set; }
            public string Bio { get; set; }
        };
        public async Task CreateAuthorAsync(CreateAuthorArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("id", args.Id);
            queryParams.Add("name", args.Name);
            queryParams.Add("bio", args.Bio);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(CreateAuthorSql, queryParams);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(CreateAuthorSql, queryParams, transaction: this.Transaction);
        }

        private const string CreateAuthorReturnIdSql = "INSERT INTO authors (name, bio) VALUES (@name, @bio); SELECT LAST_INSERT_ID()";
        public class CreateAuthorReturnIdArgs
        {
            public string Name { get; set; }
            public string Bio { get; set; }
        };
        public async Task<long> CreateAuthorReturnIdAsync(CreateAuthorReturnIdArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("name", args.Name);
            queryParams.Add("bio", args.Bio);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    return await connection.QuerySingleAsync<long>(CreateAuthorReturnIdSql, queryParams);
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QuerySingleAsync<long>(CreateAuthorReturnIdSql, queryParams, transaction: this.Transaction);
        }

        private const string GetAuthorByIdSql = "SELECT id, name, bio FROM authors WHERE id = @id LIMIT 1";
        public class GetAuthorByIdRow
        {
            public long Id { get; set; }
            public string Name { get; set; }
            public string Bio { get; set; }
        };
        public class GetAuthorByIdArgs
        {
            public long Id { get; set; }
        };
        public async Task<GetAuthorByIdRow> GetAuthorByIdAsync(GetAuthorByIdArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("id", args.Id);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetAuthorByIdRow>(GetAuthorByIdSql, queryParams);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetAuthorByIdRow>(GetAuthorByIdSql, queryParams, transaction: this.Transaction);
        }

        private const string GetAuthorByNamePatternSql = @"SELECT id, name, bio FROM authors
                                                           WHERE name LIKE COALESCE(@name_pattern, '%')";
        public class GetAuthorByNamePatternRow
        {
            public long Id { get; set; }
            public string Name { get; set; }
            public string Bio { get; set; }
        };
        public class GetAuthorByNamePatternArgs
        {
            public string NamePattern { get; set; }
        };
        public async Task<List<GetAuthorByNamePatternRow>> GetAuthorByNamePatternAsync(GetAuthorByNamePatternArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("name_pattern", args.NamePattern);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryAsync<GetAuthorByNamePatternRow>(GetAuthorByNamePatternSql, queryParams);
                    return result.AsList();
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return (await this.Transaction.Connection.QueryAsync<GetAuthorByNamePatternRow>(GetAuthorByNamePatternSql, queryParams, transaction: this.Transaction)).AsList();
        }

        private const string DeleteAuthorSql = @"DELETE FROM authors
                                                 WHERE name = @name";
        public class DeleteAuthorArgs
        {
            public string Name { get; set; }
        };
        public async Task DeleteAuthorAsync(DeleteAuthorArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("name", args.Name);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(DeleteAuthorSql, queryParams);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(DeleteAuthorSql, queryParams, transaction: this.Transaction);
        }

        private const string DeleteAllAuthorsSql = "DELETE FROM authors";
        public async Task DeleteAllAuthorsAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(DeleteAllAuthorsSql);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(DeleteAllAuthorsSql, transaction: this.Transaction);
        }

        private const string UpdateAuthorsSql = @"UPDATE authors
                                                  SET bio = @bio
                                                  WHERE bio IS NOT NULL";
        public class UpdateAuthorsArgs
        {
            public string Bio { get; set; }
        };
        public async Task<long> UpdateAuthorsAsync(UpdateAuthorsArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("bio", args.Bio);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    return await connection.ExecuteAsync(UpdateAuthorsSql, queryParams);
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.ExecuteAsync(UpdateAuthorsSql, queryParams, transaction: this.Transaction);
        }

        private const string GetAuthorsByIdsSql = "SELECT id, name, bio FROM authors WHERE id IN (/*SLICE:ids*/@ids)";
        public class GetAuthorsByIdsRow
        {
            public long Id { get; set; }
            public string Name { get; set; }
            public string Bio { get; set; }
        };
        public class GetAuthorsByIdsArgs
        {
            public long[] Ids { get; set; }
        };
        public async Task<List<GetAuthorsByIdsRow>> GetAuthorsByIdsAsync(GetAuthorsByIdsArgs args)
        {
            var transformedSql = GetAuthorsByIdsSql;
            transformedSql = Utils.TransformQueryForSliceArgs(transformedSql, args.Ids.Length, "ids");
            var queryParams = new Dictionary<string, object>();
            for (int i = 0; i < args.Ids.Length; i++)
                queryParams.Add($"@idsArg{i}", args.Ids[i]);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryAsync<GetAuthorsByIdsRow>(transformedSql, queryParams);
                    return result.AsList();
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return (await this.Transaction.Connection.QueryAsync<GetAuthorsByIdsRow>(transformedSql, queryParams, transaction: this.Transaction)).AsList();
        }

        private const string GetAuthorsByIdsAndNamesSql = "SELECT id, name, bio FROM authors WHERE id IN (/*SLICE:ids*/@ids) AND name IN (/*SLICE:names*/@names)";
        public class GetAuthorsByIdsAndNamesRow
        {
            public long Id { get; set; }
            public string Name { get; set; }
            public string Bio { get; set; }
        };
        public class GetAuthorsByIdsAndNamesArgs
        {
            public long[] Ids { get; set; }
            public string[] Names { get; set; }
        };
        public async Task<List<GetAuthorsByIdsAndNamesRow>> GetAuthorsByIdsAndNamesAsync(GetAuthorsByIdsAndNamesArgs args)
        {
            var transformedSql = GetAuthorsByIdsAndNamesSql;
            transformedSql = Utils.TransformQueryForSliceArgs(transformedSql, args.Ids.Length, "ids");
            transformedSql = Utils.TransformQueryForSliceArgs(transformedSql, args.Names.Length, "names");
            var queryParams = new Dictionary<string, object>();
            for (int i = 0; i < args.Ids.Length; i++)
                queryParams.Add($"@idsArg{i}", args.Ids[i]);
            for (int i = 0; i < args.Names.Length; i++)
                queryParams.Add($"@namesArg{i}", args.Names[i]);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryAsync<GetAuthorsByIdsAndNamesRow>(transformedSql, queryParams);
                    return result.AsList();
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return (await this.Transaction.Connection.QueryAsync<GetAuthorsByIdsAndNamesRow>(transformedSql, queryParams, transaction: this.Transaction)).AsList();
        }

        private const string CreateBookSql = "INSERT INTO books (name, author_id) VALUES (@name, @author_id); SELECT LAST_INSERT_ID()";
        public class CreateBookArgs
        {
            public string Name { get; set; }
            public long AuthorId { get; set; }
        };
        public async Task<long> CreateBookAsync(CreateBookArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("name", args.Name);
            queryParams.Add("author_id", args.AuthorId);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    return await connection.QuerySingleAsync<long>(CreateBookSql, queryParams);
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QuerySingleAsync<long>(CreateBookSql, queryParams, transaction: this.Transaction);
        }

        private const string ListAllAuthorsBooksSql = @"SELECT authors.id, authors.name, authors.bio, books.id, books.name, books.author_id, books.description 
                                                        FROM authors JOIN books ON authors.id = books.author_id 
                                                        ORDER BY authors.name";
        public class ListAllAuthorsBooksRow
        {
            public Author Author { get; set; }
            public Book Book { get; set; }
        };
        public async Task<List<ListAllAuthorsBooksRow>> ListAllAuthorsBooksAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = ListAllAuthorsBooksSql;
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            var result = new List<ListAllAuthorsBooksRow>();
                            while (await reader.ReadAsync())
                                result.Add(new ListAllAuthorsBooksRow { Author = new Author { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2) }, Book = new Book { Id = reader.GetInt64(3), Name = reader.GetString(4), AuthorId = reader.GetInt64(5), Description = reader.IsDBNull(6) ? null : reader.GetString(6) } });
                            return result;
                        }
                    }
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            using (var command = this.Transaction.Connection.CreateCommand())
            {
                command.CommandText = ListAllAuthorsBooksSql;
                command.Transaction = this.Transaction;
                using (var reader = await command.ExecuteReaderAsync())
                {
                    var result = new List<ListAllAuthorsBooksRow>();
                    while (await reader.ReadAsync())
                        result.Add(new ListAllAuthorsBooksRow { Author = new Author { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2) }, Book = new Book { Id = reader.GetInt64(3), Name = reader.GetString(4), AuthorId = reader.GetInt64(5), Description = reader.IsDBNull(6) ? null : reader.GetString(6) } });
                    return result;
                }
            }
        }

        private const string GetDuplicateAuthorsSql = @"SELECT authors1.id, authors1.name, authors1.bio, authors2.id, authors2.name, authors2.bio
                                                        FROM authors authors1 JOIN authors authors2 ON authors1.name = authors2.name
                                                        WHERE authors1.id < authors2.id";
        public class GetDuplicateAuthorsRow
        {
            public Author Author { get; set; }
            public Author Author2 { get; set; }
        };
        public async Task<List<GetDuplicateAuthorsRow>> GetDuplicateAuthorsAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = GetDuplicateAuthorsSql;
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            var result = new List<GetDuplicateAuthorsRow>();
                            while (await reader.ReadAsync())
                                result.Add(new GetDuplicateAuthorsRow { Author = new Author { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2) }, Author2 = new Author { Id = reader.GetInt64(3), Name = reader.GetString(4), Bio = reader.IsDBNull(5) ? null : reader.GetString(5) } });
                            return result;
                        }
                    }
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            using (var command = this.Transaction.Connection.CreateCommand())
            {
                command.CommandText = GetDuplicateAuthorsSql;
                command.Transaction = this.Transaction;
                using (var reader = await command.ExecuteReaderAsync())
                {
                    var result = new List<GetDuplicateAuthorsRow>();
                    while (await reader.ReadAsync())
                        result.Add(new GetDuplicateAuthorsRow { Author = new Author { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2) }, Author2 = new Author { Id = reader.GetInt64(3), Name = reader.GetString(4), Bio = reader.IsDBNull(5) ? null : reader.GetString(5) } });
                    return result;
                }
            }
        }

        private const string GetAuthorsByBookNameSql = @"SELECT authors.id, authors.name, authors.bio, books.id, books.name, books.author_id, books.description
                                                         FROM authors JOIN books ON authors.id = books.author_id
                                                         WHERE books.name = @name";
        public class GetAuthorsByBookNameRow
        {
            public long Id { get; set; }
            public string Name { get; set; }
            public string Bio { get; set; }
            public Book Book { get; set; }
        };
        public class GetAuthorsByBookNameArgs
        {
            public string Name { get; set; }
        };
        public async Task<List<GetAuthorsByBookNameRow>> GetAuthorsByBookNameAsync(GetAuthorsByBookNameArgs args)
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    using (var command = connection.CreateCommand())
                    {
                        command.CommandText = GetAuthorsByBookNameSql;
                        command.Parameters.AddWithValue("@name", args.Name);
                        using (var reader = await command.ExecuteReaderAsync())
                        {
                            var result = new List<GetAuthorsByBookNameRow>();
                            while (await reader.ReadAsync())
                                result.Add(new GetAuthorsByBookNameRow { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2), Book = new Book { Id = reader.GetInt64(3), Name = reader.GetString(4), AuthorId = reader.GetInt64(5), Description = reader.IsDBNull(6) ? null : reader.GetString(6) } });
                            return result;
                        }
                    }
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            using (var command = this.Transaction.Connection.CreateCommand())
            {
                command.CommandText = GetAuthorsByBookNameSql;
                command.Transaction = this.Transaction;
                command.Parameters.AddWithValue("@name", args.Name);
                using (var reader = await command.ExecuteReaderAsync())
                {
                    var result = new List<GetAuthorsByBookNameRow>();
                    while (await reader.ReadAsync())
                        result.Add(new GetAuthorsByBookNameRow { Id = reader.GetInt64(0), Name = reader.GetString(1), Bio = reader.IsDBNull(2) ? null : reader.GetString(2), Book = new Book { Id = reader.GetInt64(3), Name = reader.GetString(4), AuthorId = reader.GetInt64(5), Description = reader.IsDBNull(6) ? null : reader.GetString(6) } });
                    return result;
                }
            }
        }

        private const string CreateExtendedBioSql = "INSERT INTO extended.bios (author_name, name, bio_type, author_type) VALUES (@author_name, @name, @bio_type, @author_type)";
        public class CreateExtendedBioArgs
        {
            public string AuthorName { get; set; }
            public string Name { get; set; }
            public BiosBioType? BioType { get; set; }
            public HashSet<BiosAuthorType> AuthorType { get; set; }
        };
        public async Task CreateExtendedBioAsync(CreateExtendedBioArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("author_name", args.AuthorName);
            queryParams.Add("name", args.Name);
            queryParams.Add("bio_type", args.BioType);
            queryParams.Add("author_type", args.AuthorType != null ? string.Join(",", args.AuthorType) : null);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(CreateExtendedBioSql, queryParams);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(CreateExtendedBioSql, queryParams, transaction: this.Transaction);
        }

        private const string GetFirstExtendedBioByTypeSql = "SELECT author_name, name, bio_type, author_type FROM extended.bios WHERE bio_type = @bio_type LIMIT 1";
        public class GetFirstExtendedBioByTypeRow
        {
            public string AuthorName { get; set; }
            public string Name { get; set; }
            public BiosBioType? BioType { get; set; }
            public HashSet<BiosAuthorType> AuthorType { get; set; }
        };
        public class GetFirstExtendedBioByTypeArgs
        {
            public BiosBioType? BioType { get; set; }
        };
        public async Task<GetFirstExtendedBioByTypeRow> GetFirstExtendedBioByTypeAsync(GetFirstExtendedBioByTypeArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("bio_type", args.BioType);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetFirstExtendedBioByTypeRow>(GetFirstExtendedBioByTypeSql, queryParams);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetFirstExtendedBioByTypeRow>(GetFirstExtendedBioByTypeSql, queryParams, transaction: this.Transaction);
        }

        private const string TruncateExtendedBiosSql = "TRUNCATE TABLE extended.bios";
        public async Task TruncateExtendedBiosAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(TruncateExtendedBiosSql);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(TruncateExtendedBiosSql, transaction: this.Transaction);
        }

        private const string InsertMysqlNumericTypesSql = @"
                                                            INSERT INTO mysql_numeric_types 
                                                            (
                                                                c_bool,
                                                                c_boolean,
                                                                c_tinyint,
                                                                c_smallint,
                                                                c_mediumint,
                                                                c_int,
                                                                c_integer,
                                                                c_bigint, 
                                                                c_decimal, 
                                                                c_dec, 
                                                                c_numeric, 
                                                                c_fixed, 
                                                                c_float, 
                                                                c_double, 
                                                                c_double_precision
                                                            ) 
                                                            VALUES (@c_bool, @c_boolean, @c_tinyint, @c_smallint, @c_mediumint, @c_int, @c_integer, @c_bigint, @c_decimal, @c_dec, @c_numeric, @c_fixed, @c_float, @c_double, @c_double_precision)";
        public class InsertMysqlNumericTypesArgs
        {
            public bool? CBool { get; set; }
            public bool? CBoolean { get; set; }
            public short? CTinyint { get; set; }
            public short? CSmallint { get; set; }
            public int? CMediumint { get; set; }
            public int? CInt { get; set; }
            public int? CInteger { get; set; }
            public long? CBigint { get; set; }
            public decimal? CDecimal { get; set; }
            public decimal? CDec { get; set; }
            public decimal? CNumeric { get; set; }
            public decimal? CFixed { get; set; }
            public double? CFloat { get; set; }
            public double? CDouble { get; set; }
            public double? CDoublePrecision { get; set; }
        };
        public async Task InsertMysqlNumericTypesAsync(InsertMysqlNumericTypesArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("c_bool", args.CBool);
            queryParams.Add("c_boolean", args.CBoolean);
            queryParams.Add("c_tinyint", args.CTinyint);
            queryParams.Add("c_smallint", args.CSmallint);
            queryParams.Add("c_mediumint", args.CMediumint);
            queryParams.Add("c_int", args.CInt);
            queryParams.Add("c_integer", args.CInteger);
            queryParams.Add("c_bigint", args.CBigint);
            queryParams.Add("c_decimal", args.CDecimal);
            queryParams.Add("c_dec", args.CDec);
            queryParams.Add("c_numeric", args.CNumeric);
            queryParams.Add("c_fixed", args.CFixed);
            queryParams.Add("c_float", args.CFloat);
            queryParams.Add("c_double", args.CDouble);
            queryParams.Add("c_double_precision", args.CDoublePrecision);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(InsertMysqlNumericTypesSql, queryParams);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(InsertMysqlNumericTypesSql, queryParams, transaction: this.Transaction);
        }

        public class InsertMysqlNumericTypesBatchArgs
        {
            public bool? CBool { get; set; }
            public bool? CBoolean { get; set; }
            public short? CTinyint { get; set; }
            public short? CSmallint { get; set; }
            public int? CMediumint { get; set; }
            public int? CInt { get; set; }
            public int? CInteger { get; set; }
            public long? CBigint { get; set; }
            public double? CFloat { get; set; }
            public decimal? CNumeric { get; set; }
            public decimal? CDecimal { get; set; }
            public decimal? CDec { get; set; }
            public decimal? CFixed { get; set; }
            public double? CDouble { get; set; }
            public double? CDoublePrecision { get; set; }
        };
        public async Task InsertMysqlNumericTypesBatchAsync(List<InsertMysqlNumericTypesBatchArgs> args)
        {
            const string supportedDateTimeFormat = "yyyy-MM-dd H:mm:ss";
            var config = new CsvConfiguration(CultureInfo.CurrentCulture)
            {
                Delimiter = ",",
                NewLine = "\n"
            };
            var nullConverterFn = new Utils.NullToStringCsvConverter();
            using (var writer = new StreamWriter("input.csv", false, new UTF8Encoding(false)))
            using (var csvWriter = new CsvWriter(writer, config))
            {
                var options = new TypeConverterOptions
                {
                    Formats = new[]
                    {
                        supportedDateTimeFormat
                    }
                };
                csvWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime>(options);
                csvWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime?>(options);
                csvWriter.Context.TypeConverterCache.AddConverter<bool>(new Utils.BoolToBitCsvConverter());
                csvWriter.Context.TypeConverterCache.AddConverter<bool?>(new Utils.BoolToBitCsvConverter());
                csvWriter.Context.TypeConverterCache.AddConverter<short?>(nullConverterFn);
                csvWriter.Context.TypeConverterCache.AddConverter<int?>(nullConverterFn);
                csvWriter.Context.TypeConverterCache.AddConverter<long?>(nullConverterFn);
                csvWriter.Context.TypeConverterCache.AddConverter<double?>(nullConverterFn);
                csvWriter.Context.TypeConverterCache.AddConverter<decimal?>(nullConverterFn);
                await csvWriter.WriteRecordsAsync(args);
            }

            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var loader = new MySqlBulkLoader(connection)
                {
                    Local = true,
                    TableName = "mysql_numeric_types",
                    FileName = "input.csv",
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    FieldQuotationOptional = true,
                    NumberOfLinesToSkip = 1,
                    LineTerminator = "\n"
                };
                loader.Columns.AddRange(new List<string> { "c_bool", "c_boolean", "c_tinyint", "c_smallint", "c_mediumint", "c_int", "c_integer", "c_bigint", "c_float", "c_numeric", "c_decimal", "c_dec", "c_fixed", "c_double", "c_double_precision" });
                await loader.LoadAsync();
            }
        }

        private const string GetMysqlNumericTypesSql = "SELECT c_bool, c_boolean, c_tinyint, c_smallint, c_mediumint, c_int, c_integer, c_bigint, c_float, c_decimal, c_dec, c_numeric, c_fixed, c_double, c_double_precision FROM mysql_numeric_types LIMIT 1";
        public class GetMysqlNumericTypesRow
        {
            public bool? CBool { get; set; }
            public bool? CBoolean { get; set; }
            public short? CTinyint { get; set; }
            public short? CSmallint { get; set; }
            public int? CMediumint { get; set; }
            public int? CInt { get; set; }
            public int? CInteger { get; set; }
            public long? CBigint { get; set; }
            public double? CFloat { get; set; }
            public decimal? CDecimal { get; set; }
            public decimal? CDec { get; set; }
            public decimal? CNumeric { get; set; }
            public decimal? CFixed { get; set; }
            public double? CDouble { get; set; }
            public double? CDoublePrecision { get; set; }
        };
        public async Task<GetMysqlNumericTypesRow> GetMysqlNumericTypesAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlNumericTypesRow>(GetMysqlNumericTypesSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlNumericTypesRow>(GetMysqlNumericTypesSql, transaction: this.Transaction);
        }

        private const string GetMysqlNumericTypesCntSql = @"SELECT
                                                                COUNT(*) AS cnt,
                                                                c_bool,
                                                                c_boolean,
                                                                c_tinyint,
                                                                c_smallint,
                                                                c_mediumint,
                                                                c_int,
                                                                c_integer,
                                                                c_bigint,
                                                                c_float,
                                                                c_numeric,
                                                                c_decimal,
                                                                c_dec,
                                                                c_fixed,
                                                                c_double,
                                                                c_double_precision
                                                            FROM mysql_numeric_types
                                                            GROUP BY
                                                                c_bool,
                                                                c_boolean,
                                                                c_tinyint,
                                                                c_smallint,
                                                                c_mediumint,
                                                                c_int,
                                                                c_integer,
                                                                c_bigint,
                                                                c_float,
                                                                c_numeric,
                                                                c_decimal,
                                                                c_dec,
                                                                c_fixed,
                                                                c_double,
                                                                c_double_precision
                                                            LIMIT 1";
        public class GetMysqlNumericTypesCntRow
        {
            public long Cnt { get; set; }
            public bool? CBool { get; set; }
            public bool? CBoolean { get; set; }
            public short? CTinyint { get; set; }
            public short? CSmallint { get; set; }
            public int? CMediumint { get; set; }
            public int? CInt { get; set; }
            public int? CInteger { get; set; }
            public long? CBigint { get; set; }
            public double? CFloat { get; set; }
            public decimal? CNumeric { get; set; }
            public decimal? CDecimal { get; set; }
            public decimal? CDec { get; set; }
            public decimal? CFixed { get; set; }
            public double? CDouble { get; set; }
            public double? CDoublePrecision { get; set; }
        };
        public async Task<GetMysqlNumericTypesCntRow> GetMysqlNumericTypesCntAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlNumericTypesCntRow>(GetMysqlNumericTypesCntSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlNumericTypesCntRow>(GetMysqlNumericTypesCntSql, transaction: this.Transaction);
        }

        private const string TruncateMysqlNumericTypesSql = "TRUNCATE TABLE mysql_numeric_types";
        public async Task TruncateMysqlNumericTypesAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(TruncateMysqlNumericTypesSql);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(TruncateMysqlNumericTypesSql, transaction: this.Transaction);
        }

        private const string InsertMysqlStringTypesSql = @"
                                                           INSERT INTO mysql_string_types 
                                                           (
                                                               c_char,
                                                               c_nchar,
                                                               c_national_char,
                                                               c_varchar,
                                                               c_tinytext,
                                                               c_mediumtext,
                                                               c_text,
                                                               c_longtext, 
                                                               c_json,
                                                               c_json_string_override,
                                                               c_enum,
                                                               c_set
                                                           ) 
                                                           VALUES (@c_char, @c_nchar, @c_national_char, @c_varchar, @c_tinytext, @c_mediumtext, @c_text, @c_longtext, @c_json, @c_json_string_override, @c_enum, @c_set)";
        public class InsertMysqlStringTypesArgs
        {
            public string CChar { get; set; }
            public string CNchar { get; set; }
            public string CNationalChar { get; set; }
            public string CVarchar { get; set; }
            public string CTinytext { get; set; }
            public string CMediumtext { get; set; }
            public string CText { get; set; }
            public string CLongtext { get; set; }
            public JsonElement? CJson { get; set; }
            public string CJsonStringOverride { get; set; }
            public MysqlStringTypesCEnum? CEnum { get; set; }
            public HashSet<MysqlStringTypesCSet> CSet { get; set; }
        };
        public async Task InsertMysqlStringTypesAsync(InsertMysqlStringTypesArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("c_char", args.CChar);
            queryParams.Add("c_nchar", args.CNchar);
            queryParams.Add("c_national_char", args.CNationalChar);
            queryParams.Add("c_varchar", args.CVarchar);
            queryParams.Add("c_tinytext", args.CTinytext);
            queryParams.Add("c_mediumtext", args.CMediumtext);
            queryParams.Add("c_text", args.CText);
            queryParams.Add("c_longtext", args.CLongtext);
            queryParams.Add("c_json", args.CJson?.GetRawText() ?? null);
            queryParams.Add("c_json_string_override", args.CJsonStringOverride);
            queryParams.Add("c_enum", args.CEnum);
            queryParams.Add("c_set", args.CSet != null ? string.Join(",", args.CSet) : null);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(InsertMysqlStringTypesSql, queryParams);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(InsertMysqlStringTypesSql, queryParams, transaction: this.Transaction);
        }

        public class InsertMysqlStringTypesBatchArgs
        {
            public string CChar { get; set; }
            public string CNchar { get; set; }
            public string CNationalChar { get; set; }
            public string CVarchar { get; set; }
            public string CTinytext { get; set; }
            public string CMediumtext { get; set; }
            public string CText { get; set; }
            public string CLongtext { get; set; }
            public JsonElement? CJson { get; set; }
            public string CJsonStringOverride { get; set; }
            public MysqlStringTypesCEnum? CEnum { get; set; }
            public HashSet<MysqlStringTypesCSet> CSet { get; set; }
        };
        public async Task InsertMysqlStringTypesBatchAsync(List<InsertMysqlStringTypesBatchArgs> args)
        {
            const string supportedDateTimeFormat = "yyyy-MM-dd H:mm:ss";
            var config = new CsvConfiguration(CultureInfo.CurrentCulture)
            {
                Delimiter = ",",
                NewLine = "\n"
            };
            var nullConverterFn = new Utils.NullToStringCsvConverter();
            using (var writer = new StreamWriter("input.csv", false, new UTF8Encoding(false)))
            using (var csvWriter = new CsvWriter(writer, config))
            {
                var options = new TypeConverterOptions
                {
                    Formats = new[]
                    {
                        supportedDateTimeFormat
                    }
                };
                csvWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime>(options);
                csvWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime?>(options);
                csvWriter.Context.TypeConverterCache.AddConverter<HashSet<MysqlStringTypesCSet>>(new Utils.MysqlStringTypesCSetCsvConverter());
                csvWriter.Context.TypeConverterCache.AddConverter<string>(nullConverterFn);
                csvWriter.Context.TypeConverterCache.AddConverter<JsonElement?>(nullConverterFn);
                csvWriter.Context.TypeConverterCache.AddConverter<MysqlStringTypesCEnum?>(nullConverterFn);
                await csvWriter.WriteRecordsAsync(args);
            }

            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var loader = new MySqlBulkLoader(connection)
                {
                    Local = true,
                    TableName = "mysql_string_types",
                    FileName = "input.csv",
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    FieldQuotationOptional = true,
                    NumberOfLinesToSkip = 1,
                    LineTerminator = "\n"
                };
                loader.Columns.AddRange(new List<string> { "c_char", "c_nchar", "c_national_char", "c_varchar", "c_tinytext", "c_mediumtext", "c_text", "c_longtext", "c_json", "c_json_string_override", "c_enum", "c_set" });
                await loader.LoadAsync();
            }
        }

        private const string GetMysqlStringTypesSql = "SELECT c_char, c_nchar, c_national_char, c_varchar, c_tinytext, c_mediumtext, c_text, c_longtext, c_json, c_json_string_override, c_enum, c_set FROM mysql_string_types LIMIT 1";
        public class GetMysqlStringTypesRow
        {
            public string CChar { get; set; }
            public string CNchar { get; set; }
            public string CNationalChar { get; set; }
            public string CVarchar { get; set; }
            public string CTinytext { get; set; }
            public string CMediumtext { get; set; }
            public string CText { get; set; }
            public string CLongtext { get; set; }
            public JsonElement? CJson { get; set; }
            public string CJsonStringOverride { get; set; }
            public MysqlStringTypesCEnum? CEnum { get; set; }
            public HashSet<MysqlStringTypesCSet> CSet { get; set; }
        };
        public async Task<GetMysqlStringTypesRow> GetMysqlStringTypesAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlStringTypesRow>(GetMysqlStringTypesSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlStringTypesRow>(GetMysqlStringTypesSql, transaction: this.Transaction);
        }

        private const string GetMysqlStringTypesCntSql = @"SELECT
                                                               COUNT(*) AS cnt,
                                                               c_char,
                                                               c_nchar,
                                                               c_national_char,
                                                               c_varchar,
                                                               c_tinytext,
                                                               c_mediumtext,
                                                               c_text,
                                                               c_longtext,
                                                               c_json,
                                                               c_json_string_override,
                                                               c_enum,
                                                               c_set
                                                           FROM mysql_string_types
                                                           GROUP BY
                                                               c_char,
                                                               c_nchar,
                                                               c_national_char,
                                                               c_varchar,
                                                               c_tinytext,
                                                               c_mediumtext,
                                                               c_text,
                                                               c_longtext,
                                                               c_json,
                                                               c_json_string_override,
                                                               c_enum,
                                                               c_set
                                                           LIMIT 1";
        public class GetMysqlStringTypesCntRow
        {
            public long Cnt { get; set; }
            public string CChar { get; set; }
            public string CNchar { get; set; }
            public string CNationalChar { get; set; }
            public string CVarchar { get; set; }
            public string CTinytext { get; set; }
            public string CMediumtext { get; set; }
            public string CText { get; set; }
            public string CLongtext { get; set; }
            public JsonElement? CJson { get; set; }
            public string CJsonStringOverride { get; set; }
            public MysqlStringTypesCEnum? CEnum { get; set; }
            public HashSet<MysqlStringTypesCSet> CSet { get; set; }
        };
        public async Task<GetMysqlStringTypesCntRow> GetMysqlStringTypesCntAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlStringTypesCntRow>(GetMysqlStringTypesCntSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlStringTypesCntRow>(GetMysqlStringTypesCntSql, transaction: this.Transaction);
        }

        private const string TruncateMysqlStringTypesSql = "TRUNCATE TABLE mysql_string_types";
        public async Task TruncateMysqlStringTypesAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(TruncateMysqlStringTypesSql);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(TruncateMysqlStringTypesSql, transaction: this.Transaction);
        }

        private const string InsertMysqlDatetimeTypesSql = @"
                                                             INSERT INTO mysql_datetime_types 
                                                             (
                                                                 c_year,
                                                                 c_date,
                                                                 c_datetime,
                                                                 c_timestamp,
                                                                 c_time,
                                                                 c_timestamp_noda_instant_override
                                                             ) 
                                                             VALUES (@c_year, @c_date, @c_datetime, @c_timestamp, @c_time, @c_timestamp_noda_instant_override)";
        public class InsertMysqlDatetimeTypesArgs
        {
            public short? CYear { get; set; }
            public DateTime? CDate { get; set; }
            public DateTime? CDatetime { get; set; }
            public DateTime? CTimestamp { get; set; }
            public TimeSpan? CTime { get; set; }
            public Instant? CTimestampNodaInstantOverride { get; set; }
        };
        public async Task InsertMysqlDatetimeTypesAsync(InsertMysqlDatetimeTypesArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("c_year", args.CYear);
            queryParams.Add("c_date", args.CDate);
            queryParams.Add("c_datetime", args.CDatetime);
            queryParams.Add("c_timestamp", args.CTimestamp);
            queryParams.Add("c_time", args.CTime);
            queryParams.Add("c_timestamp_noda_instant_override", args.CTimestampNodaInstantOverride is null ? null : (DateTime? )DateTime.SpecifyKind(args.CTimestampNodaInstantOverride.Value.ToDateTimeUtc(), DateTimeKind.Unspecified));
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(InsertMysqlDatetimeTypesSql, queryParams);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(InsertMysqlDatetimeTypesSql, queryParams, transaction: this.Transaction);
        }

        public class InsertMysqlDatetimeTypesBatchArgs
        {
            public short? CYear { get; set; }
            public DateTime? CDate { get; set; }
            public DateTime? CDatetime { get; set; }
            public DateTime? CTimestamp { get; set; }
            public TimeSpan? CTime { get; set; }
        };
        public async Task InsertMysqlDatetimeTypesBatchAsync(List<InsertMysqlDatetimeTypesBatchArgs> args)
        {
            const string supportedDateTimeFormat = "yyyy-MM-dd H:mm:ss";
            var config = new CsvConfiguration(CultureInfo.CurrentCulture)
            {
                Delimiter = ",",
                NewLine = "\n"
            };
            var nullConverterFn = new Utils.NullToStringCsvConverter();
            using (var writer = new StreamWriter("input.csv", false, new UTF8Encoding(false)))
            using (var csvWriter = new CsvWriter(writer, config))
            {
                var options = new TypeConverterOptions
                {
                    Formats = new[]
                    {
                        supportedDateTimeFormat
                    }
                };
                csvWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime>(options);
                csvWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime?>(options);
                csvWriter.Context.TypeConverterCache.AddConverter<short?>(nullConverterFn);
                csvWriter.Context.TypeConverterCache.AddConverter<DateTime?>(nullConverterFn);
                csvWriter.Context.TypeConverterCache.AddConverter<TimeSpan?>(nullConverterFn);
                await csvWriter.WriteRecordsAsync(args);
            }

            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var loader = new MySqlBulkLoader(connection)
                {
                    Local = true,
                    TableName = "mysql_datetime_types",
                    FileName = "input.csv",
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    FieldQuotationOptional = true,
                    NumberOfLinesToSkip = 1,
                    LineTerminator = "\n"
                };
                loader.Columns.AddRange(new List<string> { "c_year", "c_date", "c_datetime", "c_timestamp", "c_time" });
                await loader.LoadAsync();
            }
        }

        private const string GetMysqlDatetimeTypesSql = "SELECT c_year, c_date, c_datetime, c_timestamp, c_time, c_timestamp_noda_instant_override FROM mysql_datetime_types LIMIT 1";
        public class GetMysqlDatetimeTypesRow
        {
            public short? CYear { get; set; }
            public DateTime? CDate { get; set; }
            public DateTime? CDatetime { get; set; }
            public DateTime? CTimestamp { get; set; }
            public TimeSpan? CTime { get; set; }
            public Instant? CTimestampNodaInstantOverride { get; set; }
        };
        public async Task<GetMysqlDatetimeTypesRow> GetMysqlDatetimeTypesAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlDatetimeTypesRow>(GetMysqlDatetimeTypesSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlDatetimeTypesRow>(GetMysqlDatetimeTypesSql, transaction: this.Transaction);
        }

        private const string GetMysqlDatetimeTypesCntSql = @"SELECT
                                                                 COUNT(*) AS cnt,
                                                                 c_year,
                                                                 c_date,
                                                                 c_datetime,
                                                                 c_timestamp,
                                                                 c_time
                                                             FROM mysql_datetime_types
                                                             GROUP BY
                                                                 c_year,
                                                                 c_date,
                                                                 c_datetime,
                                                                 c_timestamp,
                                                                 c_time
                                                             LIMIT 1";
        public class GetMysqlDatetimeTypesCntRow
        {
            public long Cnt { get; set; }
            public short? CYear { get; set; }
            public DateTime? CDate { get; set; }
            public DateTime? CDatetime { get; set; }
            public DateTime? CTimestamp { get; set; }
            public TimeSpan? CTime { get; set; }
        };
        public async Task<GetMysqlDatetimeTypesCntRow> GetMysqlDatetimeTypesCntAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlDatetimeTypesCntRow>(GetMysqlDatetimeTypesCntSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlDatetimeTypesCntRow>(GetMysqlDatetimeTypesCntSql, transaction: this.Transaction);
        }

        private const string TruncateMysqlDatetimeTypesSql = "TRUNCATE TABLE mysql_datetime_types";
        public async Task TruncateMysqlDatetimeTypesAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(TruncateMysqlDatetimeTypesSql);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(TruncateMysqlDatetimeTypesSql, transaction: this.Transaction);
        }

        private const string InsertMysqlBinaryTypesSql = @"
                                                           INSERT INTO mysql_binary_types 
                                                           (
                                                               c_bit,
                                                               c_binary, 
                                                               c_varbinary, 
                                                               c_tinyblob, 
                                                               c_blob, 
                                                               c_mediumblob, 
                                                               c_longblob
                                                           ) 
                                                           VALUES (@c_bit, @c_binary, @c_varbinary, @c_tinyblob, @c_blob, @c_mediumblob, @c_longblob)";
        public class InsertMysqlBinaryTypesArgs
        {
            public byte? CBit { get; set; }
            public byte[] CBinary { get; set; }
            public byte[] CVarbinary { get; set; }
            public byte[] CTinyblob { get; set; }
            public byte[] CBlob { get; set; }
            public byte[] CMediumblob { get; set; }
            public byte[] CLongblob { get; set; }
        };
        public async Task InsertMysqlBinaryTypesAsync(InsertMysqlBinaryTypesArgs args)
        {
            var queryParams = new Dictionary<string, object>();
            queryParams.Add("c_bit", args.CBit);
            queryParams.Add("c_binary", args.CBinary);
            queryParams.Add("c_varbinary", args.CVarbinary);
            queryParams.Add("c_tinyblob", args.CTinyblob);
            queryParams.Add("c_blob", args.CBlob);
            queryParams.Add("c_mediumblob", args.CMediumblob);
            queryParams.Add("c_longblob", args.CLongblob);
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(InsertMysqlBinaryTypesSql, queryParams);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(InsertMysqlBinaryTypesSql, queryParams, transaction: this.Transaction);
        }

        public class InsertMysqlBinaryTypesBatchArgs
        {
            public byte? CBit { get; set; }
            public byte[] CBinary { get; set; }
            public byte[] CVarbinary { get; set; }
            public byte[] CTinyblob { get; set; }
            public byte[] CBlob { get; set; }
            public byte[] CMediumblob { get; set; }
            public byte[] CLongblob { get; set; }
        };
        public async Task InsertMysqlBinaryTypesBatchAsync(List<InsertMysqlBinaryTypesBatchArgs> args)
        {
            const string supportedDateTimeFormat = "yyyy-MM-dd H:mm:ss";
            var config = new CsvConfiguration(CultureInfo.CurrentCulture)
            {
                Delimiter = ",",
                NewLine = "\n"
            };
            var nullConverterFn = new Utils.NullToStringCsvConverter();
            using (var writer = new StreamWriter("input.csv", false, new UTF8Encoding(false)))
            using (var csvWriter = new CsvWriter(writer, config))
            {
                var options = new TypeConverterOptions
                {
                    Formats = new[]
                    {
                        supportedDateTimeFormat
                    }
                };
                csvWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime>(options);
                csvWriter.Context.TypeConverterOptionsCache.AddOptions<DateTime?>(options);
                csvWriter.Context.TypeConverterCache.AddConverter<byte>(new Utils.ByteCsvConverter());
                csvWriter.Context.TypeConverterCache.AddConverter<byte?>(new Utils.ByteCsvConverter());
                csvWriter.Context.TypeConverterCache.AddConverter<byte[]>(new Utils.ByteArrayCsvConverter());
                await csvWriter.WriteRecordsAsync(args);
            }

            using (var connection = await GetDataSource().OpenConnectionAsync())
            {
                var loader = new MySqlBulkLoader(connection)
                {
                    Local = true,
                    TableName = "mysql_binary_types",
                    FileName = "input.csv",
                    FieldTerminator = ",",
                    FieldQuotationCharacter = '"',
                    FieldQuotationOptional = true,
                    NumberOfLinesToSkip = 1,
                    LineTerminator = "\n"
                };
                loader.Columns.AddRange(new List<string> { "c_bit", "c_binary", "c_varbinary", "c_tinyblob", "c_blob", "c_mediumblob", "c_longblob" });
                await loader.LoadAsync();
            }
        }

        private const string GetMysqlBinaryTypesSql = "SELECT c_bit, c_binary, c_varbinary, c_tinyblob, c_blob, c_mediumblob, c_longblob FROM mysql_binary_types LIMIT 1";
        public class GetMysqlBinaryTypesRow
        {
            public byte? CBit { get; set; }
            public byte[] CBinary { get; set; }
            public byte[] CVarbinary { get; set; }
            public byte[] CTinyblob { get; set; }
            public byte[] CBlob { get; set; }
            public byte[] CMediumblob { get; set; }
            public byte[] CLongblob { get; set; }
        };
        public async Task<GetMysqlBinaryTypesRow> GetMysqlBinaryTypesAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlBinaryTypesRow>(GetMysqlBinaryTypesSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlBinaryTypesRow>(GetMysqlBinaryTypesSql, transaction: this.Transaction);
        }

        private const string GetMysqlBinaryTypesCntSql = @"SELECT
                                                               COUNT(*) AS cnt,
                                                               c_bit,
                                                               c_binary,
                                                               c_varbinary,
                                                               c_tinyblob,
                                                               c_blob,
                                                               c_mediumblob,
                                                               c_longblob
                                                           FROM mysql_binary_types
                                                           GROUP BY
                                                               c_bit,
                                                               c_binary,
                                                               c_varbinary,
                                                               c_tinyblob,
                                                               c_blob,
                                                               c_mediumblob,
                                                               c_longblob
                                                           LIMIT 1";
        public class GetMysqlBinaryTypesCntRow
        {
            public long Cnt { get; set; }
            public byte? CBit { get; set; }
            public byte[] CBinary { get; set; }
            public byte[] CVarbinary { get; set; }
            public byte[] CTinyblob { get; set; }
            public byte[] CBlob { get; set; }
            public byte[] CMediumblob { get; set; }
            public byte[] CLongblob { get; set; }
        };
        public async Task<GetMysqlBinaryTypesCntRow> GetMysqlBinaryTypesCntAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlBinaryTypesCntRow>(GetMysqlBinaryTypesCntSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlBinaryTypesCntRow>(GetMysqlBinaryTypesCntSql, transaction: this.Transaction);
        }

        private const string TruncateMysqlBinaryTypesSql = "TRUNCATE TABLE mysql_binary_types";
        public async Task TruncateMysqlBinaryTypesAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    await connection.ExecuteAsync(TruncateMysqlBinaryTypesSql);
                    return;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            await this.Transaction.Connection.ExecuteAsync(TruncateMysqlBinaryTypesSql, transaction: this.Transaction);
        }

        private const string GetMysqlFunctionsSql = @"
                                                      SELECT
                                                          MAX(c_int) AS max_int,
                                                          MAX(c_varchar) AS max_varchar,
                                                          MAX(c_timestamp) AS max_timestamp
                                                      FROM mysql_numeric_types
                                                      CROSS JOIN mysql_string_types
                                                      CROSS JOIN mysql_datetime_types";
        public class GetMysqlFunctionsRow
        {
            public int? MaxInt { get; set; }
            public string MaxVarchar { get; set; }
            public DateTime MaxTimestamp { get; set; }
        };
        public async Task<GetMysqlFunctionsRow> GetMysqlFunctionsAsync()
        {
            if (this.Transaction == null)
            {
                using (var connection = await GetDataSource().OpenConnectionAsync())
                {
                    var result = await connection.QueryFirstOrDefaultAsync<GetMysqlFunctionsRow>(GetMysqlFunctionsSql);
                    return result;
                }
            }

            if (this.Transaction?.Connection == null || this.Transaction?.Connection.State != ConnectionState.Open)
                throw new InvalidOperationException("Transaction is provided, but its connection is null.");
            return await this.Transaction.Connection.QueryFirstOrDefaultAsync<GetMysqlFunctionsRow>(GetMysqlFunctionsSql, transaction: this.Transaction);
        }
    }
}