sqlbulkcopy mysql_c# – 将DataTable批量复制到MySQL(类似于System.Data.SqlClient.SqlBulkCopy)...

Because I think there would be bad performance I don’t want to write the DataTable to a CSV file and do the insert from there with the MySqlBulkLoader class.

不排除基于毫无根据的假设的可能解决方案.我刚刚在Transaction中使用标准的MySqlDataAdapter #Update()测试了从System.Data.DataTable插入100,000行到MySQL表中.它一直需要大约30秒才能运行：

using (MySqlTransaction tran = conn.BeginTransaction(System.Data.IsolationLevel.Serializable))

{

using (MySqlCommand cmd = new MySqlCommand())

{

cmd.Connection = conn;

cmd.Transaction = tran;

cmd.CommandText = "SELECT * FROM testtable";

using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))

{

da.UpdateBatchSize = 1000;

using (MySqlCommandBuilder cb = new MySqlCommandBuilder(da))

{

da.Update(rawData);

tran.Commit();

}

}

}

}

(我为UpdateBatchSize尝试了几个不同的值,但它们似乎没有对经过的时间产生重大影响.)

相比之下,使用MySqlBulkLoader的以下代码只需要5或6秒就可以运行…

string tempCsvFileSpec = @"C:\Users\Gord\Desktop\dump.csv";

using (StreamWriter writer = new StreamWriter(tempCsvFileSpec))

{

Rfc4180Writer.WriteDataTable(rawData, writer, false);

}

var msbl = new MySqlBulkLoader(conn);

msbl.TableName = "testtable";

msbl.FileName = tempCsvFileSpec;

msbl.FieldTerminator = ",";

msbl.FieldQuotationCharacter = '"';

msbl.Load();

System.IO.File.Delete(tempCsvFileSpec);

…包括从DataTable转储100,000行到临时CSV文件(使用类似于this的代码)的时间,从该文件批量加载,以及之后删除文件.