private async ValueTask WriteToServerAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) #endif { var tableName = DestinationTableName ?? throw new InvalidOperationException("DestinationTableName must be set before calling WriteToServer"); var bulkLoader = new MySqlBulkLoader(m_connection) { CharacterSet = "utf8mb4", EscapeCharacter = '\\', FieldQuotationCharacter = '\0', FieldTerminator = "\t", LinePrefix = null, LineTerminator = "\n", Local = true, NumberOfLinesToSkip = 0, Source = this, TableName = QuoteIdentifier(tableName), Timeout = BulkCopyTimeout, }; var closeConnection = false; if (m_connection.State != ConnectionState.Open) { m_connection.Open(); closeConnection = true; } // if no user-supplied column mappings, compute them from the destination schema if (ColumnMappings.Count == 0) { using var cmd = new MySqlCommand("select * from " + QuoteIdentifier(tableName) + ";", m_connection, m_transaction); using var reader = (MySqlDataReader) await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly, ioBehavior, cancellationToken).ConfigureAwait(false); var schema = reader.GetColumnSchema(); for (var i = 0; i < schema.Count; i++) { if (schema[i].DataTypeName == "BIT") { ColumnMappings.Add(new MySqlBulkCopyColumnMapping(i, $"@col{i}", $"`{reader.GetName(i)}` = CAST(@col{i} AS UNSIGNED)")); } else if (schema[i].DataTypeName == "YEAR") { // the current code can't distinguish between 0 = 0000 and 0 = 2000 throw new NotSupportedException("'YEAR' columns are not supported by MySqlBulkLoader."); } else { var type = schema[i].DataType; if (type == typeof(byte[]) || (type == typeof(Guid) && (m_connection.GuidFormat == MySqlGuidFormat.Binary16 || m_connection.GuidFormat == MySqlGuidFormat.LittleEndianBinary16 || m_connection.GuidFormat == MySqlGuidFormat.TimeSwapBinary16))) { ColumnMappings.Add(new MySqlBulkCopyColumnMapping(i, $"@col{i}", $"`{reader.GetName(i)}` = UNHEX(@col{i})")); } else { ColumnMappings.Add(new MySqlBulkCopyColumnMapping(i, reader.GetName(i))); } } } } // set columns and expressions from the column mappings for (var i = 0; i < m_valuesEnumerator !.FieldCount; i++) { var columnMapping = ColumnMappings.FirstOrDefault(x => x.SourceOrdinal == i); if (columnMapping is null) { bulkLoader.Columns.Add("@`\uE002\bignore`"); } else { if (columnMapping.DestinationColumn.Length == 0) { throw new InvalidOperationException("MySqlBulkCopyColumnMapping.DestinationName is not set."); } if (columnMapping.DestinationColumn[0] == '@') { bulkLoader.Columns.Add(columnMapping.DestinationColumn); } else { bulkLoader.Columns.Add(QuoteIdentifier(columnMapping.DestinationColumn)); } if (columnMapping.Expression is object) { bulkLoader.Expressions.Add(columnMapping.Expression); } } } await bulkLoader.LoadAsync(ioBehavior, cancellationToken).ConfigureAwait(false); if (closeConnection) { m_connection.Close(); } #if !NETSTANDARD2_1 && !NETCOREAPP3_0 return(default);
private async ValueTask WriteToServerAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) #endif { var tableName = DestinationTableName ?? throw new InvalidOperationException("DestinationTableName must be set before calling WriteToServer"); m_wasAborted = false; Log.Info("Starting bulk copy to {0}", tableName); var bulkLoader = new MySqlBulkLoader(m_connection) { CharacterSet = "utf8mb4", EscapeCharacter = '\\', FieldQuotationCharacter = '\0', FieldTerminator = "\t", LinePrefix = null, LineTerminator = "\n", Local = true, NumberOfLinesToSkip = 0, Source = this, TableName = tableName, Timeout = BulkCopyTimeout, }; var closeConnection = false; if (m_connection.State != ConnectionState.Open) { m_connection.Open(); closeConnection = true; } // merge column mappings with the destination schema var columnMappings = new List <MySqlBulkCopyColumnMapping>(ColumnMappings); var addDefaultMappings = columnMappings.Count == 0; using (var cmd = new MySqlCommand("select * from " + tableName + ";", m_connection, m_transaction)) using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly, ioBehavior, cancellationToken).ConfigureAwait(false)) { var schema = reader.GetColumnSchema(); for (var i = 0; i < Math.Min(m_valuesEnumerator !.FieldCount, schema.Count); i++) { var destinationColumn = reader.GetName(i); if (schema[i].DataTypeName == "BIT") { AddColumnMapping(columnMappings, addDefaultMappings, i, destinationColumn, $"@`\uE002\bcol{i}`", $"%COL% = CAST(%VAR% AS UNSIGNED)"); } else if (schema[i].DataTypeName == "YEAR") { // the current code can't distinguish between 0 = 0000 and 0 = 2000 throw new NotSupportedException("'YEAR' columns are not supported by MySqlBulkLoader."); } else { var type = schema[i].DataType; if (type == typeof(byte[]) || (type == typeof(Guid) && (m_connection.GuidFormat == MySqlGuidFormat.Binary16 || m_connection.GuidFormat == MySqlGuidFormat.LittleEndianBinary16 || m_connection.GuidFormat == MySqlGuidFormat.TimeSwapBinary16))) { AddColumnMapping(columnMappings, addDefaultMappings, i, destinationColumn, $"@`\uE002\bcol{i}`", $"%COL% = UNHEX(%VAR%)"); } else if (addDefaultMappings) { Log.Debug("Adding default column mapping from SourceOrdinal {0} to DestinationColumn {1}", i, destinationColumn); columnMappings.Add(new MySqlBulkCopyColumnMapping(i, destinationColumn)); } } } } // set columns and expressions from the column mappings for (var i = 0; i < m_valuesEnumerator.FieldCount; i++) { var columnMapping = columnMappings.FirstOrDefault(x => x.SourceOrdinal == i); if (columnMapping is null) { Log.Debug("Ignoring column with SourceOrdinal {0}", i); bulkLoader.Columns.Add("@`\uE002\bignore`"); } else { if (columnMapping.DestinationColumn.Length == 0) { throw new InvalidOperationException("MySqlBulkCopyColumnMapping.DestinationName is not set for SourceOrdinal {0}".FormatInvariant(columnMapping.SourceOrdinal)); } if (columnMapping.DestinationColumn[0] == '@') { bulkLoader.Columns.Add(columnMapping.DestinationColumn); } else { bulkLoader.Columns.Add(QuoteIdentifier(columnMapping.DestinationColumn)); } if (columnMapping.Expression is object) { bulkLoader.Expressions.Add(columnMapping.Expression); } } } foreach (var columnMapping in columnMappings) { if (columnMapping.SourceOrdinal < 0 || columnMapping.SourceOrdinal >= m_valuesEnumerator.FieldCount) { throw new InvalidOperationException("SourceOrdinal {0} is an invalid value".FormatInvariant(columnMapping.SourceOrdinal)); } } var rowsInserted = await bulkLoader.LoadAsync(ioBehavior, cancellationToken).ConfigureAwait(false); if (closeConnection) { m_connection.Close(); } Log.Info("Finished bulk copy to {0}", tableName); if (!m_wasAborted && rowsInserted != RowsCopied) { Log.Error("Bulk copy to DestinationTableName={0} failed; RowsCopied={1}; RowsInserted={2}", tableName, RowsCopied, rowsInserted); throw new MySqlException(MySqlErrorCode.BulkCopyFailed, "{0} rows were copied to {1} but only {2} were inserted.".FormatInvariant(RowsCopied, tableName, rowsInserted)); } #if !NETSTANDARD2_1 && !NETCOREAPP3_0 return(default);
private async ValueTask WriteToServerAsync(IOBehavior ioBehavior, CancellationToken cancellationToken) #endif { var tableName = DestinationTableName ?? throw new InvalidOperationException("DestinationTableName must be set before calling WriteToServer"); var bulkLoader = new MySqlBulkLoader(m_connection) { CharacterSet = "utf8mb4", EscapeCharacter = '\\', FieldQuotationCharacter = '\0', FieldTerminator = "\t", LinePrefix = null, LineTerminator = "\n", Local = true, NumberOfLinesToSkip = 0, Source = this, TableName = tableName, Timeout = BulkCopyTimeout, }; var closeConnection = false; if (m_connection.State != ConnectionState.Open) { m_connection.Open(); closeConnection = true; } using (var cmd = new MySqlCommand("select * from " + QuoteIdentifier(tableName) + ";", m_connection, m_transaction)) using (var reader = (MySqlDataReader)await cmd.ExecuteReaderAsync(CommandBehavior.SchemaOnly, ioBehavior, cancellationToken).ConfigureAwait(false)) { var schema = reader.GetColumnSchema(); for (var i = 0; i < schema.Count; i++) { if (schema[i].DataTypeName == "BIT") { bulkLoader.Columns.Add($"@col{i}"); bulkLoader.Expressions.Add($"`{reader.GetName(i)}` = CAST(@col{i} AS UNSIGNED)"); } else if (schema[i].DataTypeName == "YEAR") { // the current code can't distinguish between 0 = 0000 and 0 = 2000 throw new NotSupportedException("'YEAR' columns are not supported by MySqlBulkLoader."); } else { var type = schema[i].DataType; if (type == typeof(byte[]) || (type == typeof(Guid) && (m_connection.GuidFormat == MySqlGuidFormat.Binary16 || m_connection.GuidFormat == MySqlGuidFormat.LittleEndianBinary16 || m_connection.GuidFormat == MySqlGuidFormat.TimeSwapBinary16))) { bulkLoader.Columns.Add($"@col{i}"); bulkLoader.Expressions.Add($"`{reader.GetName(i)}` = UNHEX(@col{i})"); } else { bulkLoader.Columns.Add(QuoteIdentifier(reader.GetName(i))); } } } } await bulkLoader.LoadAsync(ioBehavior, cancellationToken).ConfigureAwait(false); if (closeConnection) { m_connection.Close(); } #if !NETSTANDARD2_1 && !NETCOREAPP3_0 return(default);