MySqlDataAdapter.UpdateCommand Support For Batch Updates

[mrogunlana]

Does MySqlConnector currently support batch update operations similar to how it handles batch inserts performance wise (re: https://github.com/mysql-net/MySqlConnector/issues/1124)?

For example, the code below could be used to generate a batch of update statements and execute the batch statements at once on the database:

var connectionString = "Server=localhost;User ID=root;Password=xxx;Database=mysqltest;SSL Mode=None;";

//batch insert new records
using (var connection = new MySqlConnector.MySqlConnection(connectionString))
{
	connection.Open();
	connection.Execute("drop table if exists data_adapter; create table data_adapter(id integer not null primary key, bytes tinyblob, number integer);");
	
	using (var adapter = new MySqlConnector.MySqlDataAdapter("SELECT id, bytes, number from data_adapter", connection))
	using (var insertCommand = new MySqlConnector.MySqlCommand("INSERT INTO data_adapter(id, bytes, number) VALUES(@id, @bytes, @number);", connection))
	{
		insertCommand.UpdatedRowSource = UpdateRowSource.None;
		insertCommand.Parameters.Add(new MySqlConnector.MySqlParameter("@id", MySqlConnector.MySqlDbType.Int32) { SourceColumn = "id" });
		insertCommand.Parameters.Add(new MySqlConnector.MySqlParameter("@bytes", MySqlConnector.MySqlDbType.TinyBlob) { SourceColumn = "bytes" });
		insertCommand.Parameters.Add(new MySqlConnector.MySqlParameter("@number", MySqlConnector.MySqlDbType.Int32) { SourceColumn = "number" });
		
		adapter.InsertCommand = insertCommand;
		adapter.UpdateBatchSize = 100;
		
		var sw = Stopwatch.StartNew();
		adapter.Update(MakeDataTable());
		sw.Stop();
		sw.Elapsed.Dump();
	}
	connection.Query<long>("select count(*) from data_adapter;").Single().Dump();
}

//update batch records 
using (var connection = new MySqlConnector.MySqlConnection(connectionString))
{
	connection.Open();
	
	using (var adapter = new MySqlConnector.MySqlDataAdapter("SELECT id, bytes, number from data_adapter", connection))
	using (var updateCommand = new MySqlConnector.MySqlCommand("UPDATE data_adapter SET number=@number WHERE id=@id;", connection))
	{
		updateCommand.UpdatedRowSource = UpdateRowSource.None;
		updateCommand.Parameters.Add(new MySqlConnector.MySqlParameter("@id", MySqlConnector.MySqlDbType.Int32) { SourceColumn = "id" });
		updateCommand.Parameters.Add(new MySqlConnector.MySqlParameter("@number", MySqlConnector.MySqlDbType.Int32) { SourceColumn = "number" });
		
		adapter.UpdateCommand = updateCommand;
		adapter.UpdateBatchSize = 100;
		
		var sw = Stopwatch.StartNew();

                //this won't actually update records in the database b/c this
                //is a new datatable with new ID's, but the main goal is to see 
                //if this generates the batch of update statements to execute
                //on the database at once and how it performs with say 100K 
                //update statements in one batch...
		adapter.Update(MakeDataTable());

		sw.Stop();
		sw.Elapsed.Dump();
	}
}

DataTable MakeDataTable()
{
	var dataTable = new DataTable()
	{
		Columns =
	{
		new DataColumn("id", typeof(int)),
		new DataColumn("bytes", typeof(byte[])),
		new DataColumn("number", typeof(int)),
	},
	};

	var random = new Random(1);
	for (var i = 0; i < 2_000; i++)
	{
		var bytes = new byte[6];
		random.NextBytes(bytes);
		dataTable.Rows.Add(i, bytes, random.Next());
	}
	return dataTable;
}

[bgrainger]

MySqlConnector does not currently support this (but neither does MySql.Data AFAICT).

Since MySQL has no built-in way to run a batch UPDATE SQL statement, it looks like the command would need to be rewritten as ON DUPLICATE KEY UPDATE or perhaps with CASE: https://stackoverflow.com/a/35727615/23633. (ON DUPLICATE KEY UPDATE could only be used when the column in the WHERE clause is a primary key column, which may be unsafe to assume.)