Does the C# MySQL library have an equivalent to the SQL SqlBulkCopy class?-CodePudding

I know there is MySqlBulkLoader, however it's not an equivalent as it isn't able to send data from memory and requires a file.

This is a big problem for me because I want to bulk insert a huge amount of data into a MySQL database from a program that is already doing a lot of I/O.
I can't afford to also write millions of rows into a file and then have the MySqlBulkLoader read them back again, when I already have them in memory, it makes no sense.

Why isn't there an option to do it directly from memory instead of using a file on disk?

CodePudding user response：

I suspect you use Oracle's Connector/NET. That library has several issues, mainly around async/await and Entity Framework support but one of them is the limited bulk load support. MySQL allows bulk loading from the standard console.

Instead of Oracle's driver use the MySqlConnector package. That package is used by the most popular EF provider, Pomelo.EntityFrameworkCore.MySql with 22M downloads compared to Oracle's 900K downloads. MySqlConnector by itself has 35M downloads compared to Oracle's 38M.

MySqlConnector allows bulk imports through its MySqlBulkCopy class which works similarly to SqlBulkCopy and accepts both DataTable and DataReader inputs.

var dataTable = GetDataTableFromExternalSource();

// open the connection
using var connection = new MySqlConnection("...;AllowLoadLocalInfile=True");
await connection.OpenAsync();

// bulk copy the data
var bulkCopy = new MySqlBulkCopy(connection);
bulkCopy.DestinationTableName = "some_table_name";
var result = await bulkCopy.WriteToServerAsync(dataTable);

MySqlConnector's core API is the same as Connector/NET. There's a migration guide as well that explains the differences. This guide includes the Connector/NET bugs that are fixed in MySqlConnector