MySqlBulkLoader模式整表导入数据

这个方法很快，但是有坑，string类型如果为空的话，用 DBNull.Value 赋值，row["student"] = DBNull.Value;

数值类型为空会报错。最好设置my.ini文件sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

  private int BulkLoaderData(DataTable table)
        {
            System.Diagnostics.Stopwatch sws = new System.Diagnostics.Stopwatch();
            sws.Start();
            string pConStr = "server=127.0.0.1;port=3306;user=root;password=123456; database=csf;";
            MySqlConnection GetConnection = new MySqlConnection(pConStr);
            if (string.IsNullOrEmpty(table.TableName)) throw new Exception("请给DataTable的TableName属性附上表名称");
            if (table.Rows.Count == 0) return 0;
            int insertCount = 0;
            string tmpPath = Path.GetTempFileName();
            string csv = DataTableToCsv(table);
            StreamWriter sw = new StreamWriter(tmpPath, false, UTF8Encoding.UTF8);  //要与mysql的编码方式对象, 数据库要utf8, 表也一样
            sw.Write(csv);
            sw.Close();
            //  File.WriteAllText(tmpPath, csv);
            using (MySqlConnection conn = GetConnection)
            {
                MySqlTransaction tran = null;
                try
                {
                    conn.Open();
                    tran = conn.BeginTransaction();
                    MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
                    {
                        FieldTerminator = ",",
                        FieldQuotationCharacter = '"',
                        EscapeCharacter = '"',
                        LineTerminator = "\r\n",
                        FileName = tmpPath,
                        NumberOfLinesToSkip = 0,
                        TableName = table.TableName,    //也是mysql内表的名                    
                        CharacterSet = "UTF8",
                    };
                    //  bulk.CharacterSet = "utf-8";
                    bulk.Columns.AddRange(table.Columns.Cast<DataColumn>().Select(colum => colum.ColumnName).ToList());
                    insertCount = bulk.Load();
                    tran.Commit();
                }
                catch (MySqlException ex)
                {
                    if (tran != null) tran.Rollback();
                    throw ex;
                }
            }
            File.Delete(tmpPath);

            sws.Stop();
            TimeSpan ts2 = sws.Elapsed;
            //System.Windows.MessageBox.Show("耗时:" + ts2.TotalMilliseconds.ToString() + "毫秒");
            return insertCount;
        }