/// <summary>
/// handy functions for inquiring mysql
/// </summary>
public static class MySQLCommonHelper
{
    /// <summary>
    /// bulk insert data into database
    /// </summary>
    /// <typeparam name="T">data type</typeparam>
    /// <param name="data">the data to be inserted into database</param>
    /// <param name="tableName">destination table name</param>
    /// <param name="connectionString">connection string of MySQL</param>
    /// <returns></returns>
    /// <exception cref="ArgumentNullException"></exception>
    public static bool BulkInsert<T>(IEnumerable<T> data, string tableName, string connectionString)
    {
        if (CommonUtility.IsNullOrEmptyList(data))
            return false;// throw new ArgumentNullException(nameof(dt));
        if (string.IsNullOrWhiteSpace(tableName))
            throw new ArgumentNullException(nameof(tableName));
        if (string.IsNullOrEmpty(connectionString))
            throw new ArgumentNullException(nameof(connectionString));

        // init the DataTable for bulk inserting
        var table = new DataTable(tableName);
        var props = typeof(T).GetProperties();
        foreach (var prop in props)
        {
            table.Columns.Add(new DataColumn(prop.Name, prop.PropertyType));
        }
        foreach(var d in data)
        {
            var row = table.NewRow();
            foreach(var prop in props)
            {
                row[prop.Name] = prop.GetValue(d, null);
            }
            table.Rows.Add(row);
        }

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();
            using (MySqlCommand cmd = connection.CreateCommand())
            {
                cmd.CommandText = $"SELECT * FROM {tableName} LIMIT 0";
                using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
                {
                    adapter.UpdateBatchSize = 8192;
                    using (MySqlCommandBuilder cb = new MySqlCommandBuilder(adapter))
                    {
                        adapter.InsertCommand = cb.GetInsertCommand();
                        adapter.Fill(table);
                        // Without the MySqlCommandBuilder this line would fail
                        var rows = adapter.Update(table);
                        return rows > 0;
                    }
                };
            }
        }
    }
}