public async Task BulkLoadMissingTableName()
{
using var connection = new MySqlConnection(GetConnectionString());
await connection.OpenAsync();
MySqlBulkLoader bl = new MySqlBulkLoader(connection);
bl.FileName = AppConfig.MySqlBulkLoaderLocalCsvFile;
bl.Columns.AddRange(new string[] { "one", "two", "three", "four", "five" });
bl.NumberOfLinesToSkip = 1;
bl.FieldTerminator = ",";
bl.FieldQuotationCharacter = '"';
bl.FieldQuotationOptional = true;
bl.Expressions.Add("five = UNHEX(five)");
bl.Local = false;
#if BASELINE
await Assert.ThrowsAsync <MySqlException>(async() =>
{
int rowCount = await bl.LoadAsync();
});
#else
await Assert.ThrowsAsync <System.InvalidOperationException>(async() =>
{
int rowCount = await bl.LoadAsync();
});
#endif
}
public void BulkLoadColumnOrderAsync()
{
executeSQL(@"CREATE TABLE BulkLoadColumnOrderAsyncTest (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250), n3 VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col3,col2,col1");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadColumnOrderAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
loader.LoadAsync().ContinueWith(loadResult => {
int dataLoaded = loadResult.Result;
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadColumnOrderAsyncTest", Connection);
Assert.Equal(20, dt.Rows.Count);
Assert.Equal("col1", dt.Rows[0][1]);
Assert.Equal("col2", dt.Rows[0][2]);
Assert.Equal("col3", dt.Rows[0][3].ToString().Trim());
}).Wait();
}
public void BulkLoadEscapingAsync()
{
_fixture.execSQL("CREATE TABLE BulkLoadEscapingAsyncTest (id INT NOT NULL, name VARCHAR(250), name2 VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + ",col1\tstill col1,col2");
}
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(_fixture.conn);
loader.TableName = "BulkLoadEscapingAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.EscapeCharacter = '\t';
loader.FieldTerminator = ",";
loader.LoadAsync().ContinueWith(loadResult => {
int dataLoaded = loadResult.Result;
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM BulkLoadEscapingAsyncTest", _fixture.conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.Equal(dataLoaded, dt.Rows.Count);
Assert.Equal("col1still col1", dt.Rows[0][1]);
Assert.Equal("col2", dt.Rows[0][2].ToString().Trim());
}).Wait();
}
private void MySqlBulkInsert <T>(IList <T> entities, string destinationTableName) where T : class
{
var tmpDir = Path.Combine(AppContext.BaseDirectory, "Temp");
if (!Directory.Exists(tmpDir))
{
Directory.CreateDirectory(tmpDir);
}
var csvFileName = Path.Combine(tmpDir, $"{DateTime.Now:yyyyMMddHHmmssfff}.csv");
if (!File.Exists(csvFileName))
{
File.Create(csvFileName);
}
var separator = ",";
entities.SaveToCsv(csvFileName, separator);
using (var conn = Database.GetDbConnection() as MySqlConnection ?? new MySqlConnection(Option.ConnectionString))
{
conn.Open();
var bulk = new MySqlBulkLoader(conn)
{
NumberOfLinesToSkip = 0,
TableName = destinationTableName,
FieldTerminator = separator,
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n"
};
bulk.LoadAsync();
conn.Close();
}
File.Delete(csvFileName);
}
public async Task BulkLoadAsync()
{
st.execSQL("CREATE TABLE BulkLoadTest (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = System.IO.Path.GetTempFileName();
System.IO.StreamWriter sw = new System.IO.StreamWriter(path);
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(st.conn);
loader.TableName = "BulkLoadTest";
loader.FileName = path;
loader.Timeout = 0;
var result = await loader.LoadAsync();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM BulkLoadTest", st.conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.Equal(500, dt.Rows.Count);
Assert.Equal("'Test'", dt.Rows[0][1].ToString().Trim());
}
public async Task BulkLoadCsvFileNotFound()
{
var secureFilePath = m_database.Connection.Query <string>(@"select @@global.secure_file_priv;").FirstOrDefault() ?? "";
MySqlBulkLoader bl = new MySqlBulkLoader(m_database.Connection);
bl.FileName = Path.Combine(secureFilePath, AppConfig.MySqlBulkLoaderCsvFile + "-junk");
bl.TableName = m_testTable;
bl.CharacterSet = "UTF8";
bl.Columns.AddRange(new string[] { "one", "two", "three", "four", "five" });
bl.NumberOfLinesToSkip = 1;
bl.FieldTerminator = ",";
bl.FieldQuotationCharacter = '"';
bl.FieldQuotationOptional = true;
bl.Expressions.Add("five = UNHEX(five)");
bl.Local = false;
try
{
int rowCount = await bl.LoadAsync();
}
catch (Exception exception)
{
while (exception.InnerException != null)
{
exception = exception.InnerException;
}
if (!(exception is FileNotFoundException))
{
Assert.Contains("Errcode: 2 ", exception.Message);
Assert.Contains("No such file or directory", exception.Message);
}
}
}
public void BulkLoadEscapingAsync()
{
executeSQL("CREATE TABLE BulkLoadEscapingAsyncTest (id INT NOT NULL, name VARCHAR(250), name2 VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + ",col1\tstill col1,col2");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadEscapingAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.EscapeCharacter = '\t';
loader.FieldTerminator = ",";
loader.LoadAsync().ContinueWith(loadResult => {
int dataLoaded = loadResult.Result;
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadEscapingAsyncTest", Connection);
Assert.Equal(dataLoaded, dt.Rows.Count);
Assert.Equal("col1still col1", dt.Rows[0][1]);
Assert.Equal("col2", dt.Rows[0][2].ToString().Trim());
}).Wait();
}
public void BulkLoadSimpleAsync()
{
ExecuteSQL("CREATE TABLE BulkLoadSimpleAsyncTest (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadSimpleAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.Local = true;
loader.LoadAsync().ContinueWith(loadResult =>
{
int dataLoaded = loadResult.Result;
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadSimpleAsyncTest", Connection);
Assert.AreEqual(dataLoaded, dt.Rows.Count);
Assert.AreEqual("'Test'", dt.Rows[0][1].ToString().Trim());
}).Wait();
}
public void BulkLoadSimpleAsync()
{
st.execSQL("CREATE TABLE BulkLoadSimpleAsyncTest (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(st.conn);
loader.TableName = "BulkLoadSimpleAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.LoadAsync().ContinueWith(loadResult => {
int dataLoaded = loadResult.Result;
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM BulkLoadSimpleAsyncTest", st.conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.Equal(dataLoaded, dt.Rows.Count);
Assert.Equal("'Test'", dt.Rows[0][1].ToString().Trim());
}).Wait();
}
public async Task BulkLoadLocalCsvFileInTransactionWithRollback()
{
using var connection = new MySqlConnection(GetLocalConnectionString());
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
var bulkLoader = new MySqlBulkLoader(connection)
{
FileName = AppConfig.MySqlBulkLoaderLocalCsvFile,
TableName = m_testTable,
CharacterSet = "UTF8",
NumberOfLinesToSkip = 1,
FieldTerminator = ",",
FieldQuotationCharacter = '"',
FieldQuotationOptional = true,
Local = true,
};
bulkLoader.Expressions.Add("five = UNHEX(five)");
bulkLoader.Columns.AddRange(new[] { "one", "two", "three", "four", "five" });
var rowCount = await bulkLoader.LoadAsync();
Assert.Equal(20, rowCount);
transaction.Rollback();
}
Assert.Equal(0, await connection.ExecuteScalarAsync <int>($@"select count(*) from {m_testTable};"));
}
async Task IUnitOfWork.BulkInsert <T>(ICollection <T> entitys)
{
var dataItems = entitys.ToList();
var entityObject = EntityCache.Register(typeof(T));
using var ms = new MemoryStream();
using var writer = new StreamWriter(ms, Encoding.UTF8);
using var csv = new CsvWriter(writer, CultureInfo.InvariantCulture);
csv.Configuration.HasHeaderRecord = true;
csv.Configuration.RegisterClassMap <AotuMapper <T> >();
if (dataItems != null && dataItems.Count > 0)
{
csv.WriteRecords(dataItems);
}
writer.Flush();
ms.Position = 0;
//var mybyte = ms.ToArray();
var fieldPairs = entityObject.FieldPairs;
//File.WriteAllBytes("C:/Users/Public/Desktop/test.csv", mybyte);
var bulkLoader = new MySqlBulkLoader(_mySqlConnection)
{
TableName = entityObject.Name,
CharacterSet = "UTF8",
NumberOfLinesToSkip = 1,
FieldTerminator = ",",
FieldQuotationCharacter = '"',
FieldQuotationOptional = true,
Local = true,
SourceStream = ms
};
var bitPropertys = entityObject.Properties.Where(s => s.PropertyType == typeof(bool)).ToList();
//int i = 1;
//foreach (var item in bitPropertys)
//{
// fieldPairs[item.Name] = $"@var{i}";
// bulkLoader.Expressions.Add($"{item.Name} = CAST(CONV(@var{i}, 2, 10) AS UNSIGNED)");
// i++;
//}
int i = 1;
foreach (var item in bitPropertys)
{
fieldPairs[item.Name] = $"@var{i}";
bulkLoader.Expressions.Add($"{item.Name} = CAST(CONV(@var{i}, 2, 10) AS UNSIGNED)");
i++;
}
var datetimePropertyType = entityObject.Properties.Where(s => s.PropertyType == typeof(DateTime?)).ToList();
foreach (var item in datetimePropertyType)
{
fieldPairs[item.Name] = $"@var{i}";
bulkLoader.Expressions.Add($"{item.Name} = if(LENGTH(@var{i})=0,null,@var{i})");
i++;
}
bulkLoader.Columns.AddRange(fieldPairs.Values);
//bulkLoader.Expressions.Add("IsUsed = 0");
//bulkLoader.Expressions.Add("Type = 2");
await bulkLoader.LoadAsync();
}
public override async Task InsertAsync()
{
await DbSession.OpenAsync();
var conn = DbSession.Connection as MySqlConnection;
MySqlBulkLoader bulkLoader = GetBulkLoader(conn);
await bulkLoader.LoadAsync();
}
public void BulkLoadConflictOptionIgnoreAsync()
{
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col1");
}
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(st.conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
Console.WriteLine("Calling Asynchronous version of MySqlBulkLoader.Load (LoadAsync)");
loader.LoadAsync();
Console.WriteLine("Wait 1 seconds to give a chance to Asynchronous method to finish.");
System.Threading.Thread.Sleep(1000);
path = Path.GetTempFileName();
sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col2");
}
sw.Flush();
sw.Close();
loader = new MySqlBulkLoader(st.conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.ConflictOption = MySqlBulkLoaderConflictOption.Ignore;
Console.WriteLine("Calling Asynchronous version of MySqlBulkLoader.Load (LoadAsync) with duplicated keys.");
loader.LoadAsync();
Console.WriteLine("Wait 1 seconds to give a chance to Asynchronous method to finish.");
System.Threading.Thread.Sleep(1000);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", st.conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.Equal(20, dt.Rows.Count);
Assert.Equal("col1", dt.Rows[0][1].ToString().Trim());
}
public async Task <int> ExecuteBulkCopyAsync(DataTable dt)
{
var dllPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "failFiles");
DirectoryInfo dir = new DirectoryInfo(dllPath);
if (!dir.Exists)
{
dir.Create();
}
var fileName = Path.Combine(dllPath, Guid.NewGuid().ToString() + ".csv");
var dataTableToCsv = new MySqlBlukCopy <object>(this.Context.Context, null, null).DataTableToCsvString(dt);
File.WriteAllText(fileName, dataTableToCsv, new UTF8Encoding(false));
MySqlConnection conn = this.Context.Ado.Connection as MySqlConnection;
int result = 0;
try
{
this.Context.Ado.Open();
// IsolationLevel.Parse
MySqlBulkLoader bulk = new MySqlBulkLoader(conn)
{
CharacterSet = "UTF8",
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = Environment.NewLine,
FileName = fileName,
NumberOfLinesToSkip = 0,
TableName = dt.TableName,
Local = true,
};
if (this.CharacterSet.HasValue())
{
bulk.CharacterSet = this.CharacterSet;
}
bulk.Columns.AddRange(dt.Columns.Cast <DataColumn>().Select(colum => new MySqlBuilder().GetTranslationColumnName(colum.ColumnName)).Distinct().ToArray());
result = await bulk.LoadAsync();
//执行成功才删除文件
if (File.Exists(fileName))
{
File.Delete(fileName);
}
}
catch (MySqlException ex)
{
throw ex;
}
finally
{
CloseDb();
}
return(result);
}
public void BulkLoadReadOnlyFileAsync()
{
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Close();
FileInfo fi = new FileInfo(path);
FileAttributes oldAttr = fi.Attributes;
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
try
{
MySqlBulkLoader loader = new MySqlBulkLoader(st.conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
Console.WriteLine("Calling Asynchronous version of MySqlBulkLoader.Load (LoadAsync)");
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
timer.Start();
loader.LoadAsync();
Console.WriteLine("Wait 1 seconds to give a chance to Asynchronous method to finish.");
System.Threading.Thread.Sleep(1000);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", st.conn);
DataTable dt = new DataTable();
da.Fill(dt);
while (dt.Rows.Count < 500)
{
Console.WriteLine(string.Format("Asynchronous task is still running, processed records at this time:{0}", dt.Rows.Count));
da.Fill(dt);
}
timer.Stop();
Console.WriteLine(string.Format("Asynchronous task finished in:{0}", GetElapsedTime(timer)));
Assert.Equal(500, dt.Rows.Count);
Assert.Equal("'Test'", dt.Rows[0][1].ToString().Trim());
}
finally
{
fi.Attributes = oldAttr;
fi.Delete();
}
}
public override async Task InsertAsync(IDatabase database, DataTable Table)
{
//database.EnsureOpenConnection();
//var conn = database.Connection as MySqlConnection;
var conn = CreateNewConnection(database) as MySqlConnection;
await conn.OpenAsync();
MySqlBulkLoader bulkLoader = GetBulkLoader(conn, Table);
await bulkLoader.LoadAsync();
}
public override async Task InsertAsync()
{
var dataSource = DataSourceFilter.Elect(new RequestContext {
DataSourceChoice = DataSourceChoice.Write
});
var dbSession = SessionStore.GetOrAddDbSession(dataSource);
await dbSession.OpenConnectionAsync();
var conn = dbSession.Connection as MySqlConnection;
MySqlBulkLoader bulkLoader = GetBulkLoader(conn);
await bulkLoader.LoadAsync();
}
/// <summary>
/// Implementation the specified operation.
/// </summary>
/// <param name="connection">The connection.</param>
/// <param name="transaction">The transaction.</param>
/// <param name="cancellationToken">The cancellation token.</param>
/// <returns>Task<System.Nullable<System.Int32>>.</returns>
protected override async Task <int?> ImplementationAsync(MySqlConnection connection, MySqlTransaction?transaction, CancellationToken cancellationToken)
{
var bl = new MySqlBulkLoader(connection);
var mappedColumns = SetupBulkCopy(bl);
var totalCount = 0;
var rowCount = 0;
var output = new StringBuilder();
while (m_Source.Read())
{
rowCount += 1;
WriteRow(mappedColumns, output);
if (rowCount == m_BatchSize)
{
using (var ms = CreateMemoryStream(output))
{
bl.FileName = null;
bl.SourceStream = ms;
totalCount += await bl.LoadAsync(cancellationToken).ConfigureAwait(false);
output.Clear();
}
rowCount = 0;
}
}
if (rowCount > 0) //final batch
{
using (var ms = CreateMemoryStream(output))
{
bl.FileName = null;
bl.SourceStream = ms;
totalCount += await bl.LoadAsync(cancellationToken).ConfigureAwait(false);
}
}
return(totalCount);
}
/// <summary>
/// 异步批量插入(目前只支持MSSQLServer)
/// </summary>
/// <param name="table">table</param>
/// <param name="tableName">表名字</param>
/// <returns>Task</returns>
public Task BulkInsertAsync(DataTable table, string tableName)
{
return(SqlMonitorUtil.MonitorAsync(async() =>
{
if (table != null && DatabaseTyoe == DatabaseType.MSSQLServer)
{
Open();
using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(Connection as SqlConnection, SqlBulkCopyOptions.Default, Tran as SqlTransaction))
{
sqlbulkcopy.BatchSize = table.Rows.Count;
sqlbulkcopy.DestinationTableName = tableName;
for (int i = 0; i < table.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(table.Columns[i].ColumnName, table.Columns[i].ColumnName);
}
await sqlbulkcopy.WriteToServerAsync(table.CreateDataReader());
}
}
else if (table != null && DatabaseTyoe == DatabaseType.MySql)
{
string tmpPath = System.IO.Path.GetTempFileName();
try
{
string csv = table.ToCsv();
System.IO.File.WriteAllText(tmpPath, csv);
Open();
MySqlBulkLoader bulk = new MySqlBulkLoader(Connection as MySqlConnection)
{
FieldTerminator = ",",
FieldQuotationCharacter = '"',
EscapeCharacter = '"',
LineTerminator = "\r\n",
FileName = tmpPath,
NumberOfLinesToSkip = 0,
TableName = tableName,
};
bulk.Columns.AddRange(table.Columns.Cast <DataColumn>().Select(colum => colum.ColumnName).ToList());
int insertCount = await bulk.LoadAsync();
}
finally
{
FileUtil.DeleteFile(tmpPath);
}
}
else
{
throw new NotSupportedException(DatabaseTyoe.ToString());
}
}, dbType: DatabaseTyoe.ToString(), memberName: "BaseDataAccess-BulkInsert"));
}
public async Task BulkLoadCsvFileNotFound()
{
using (var connection = new MySqlConnection(GetConnectionString()))
{
await connection.OpenAsync();
var secureFilePath = await connection.ExecuteScalarAsync <string>(@"select @@global.secure_file_priv;");
if (string.IsNullOrEmpty(secureFilePath) || secureFilePath == "NULL")
{
return;
}
MySqlBulkLoader bl = new MySqlBulkLoader(connection);
bl.FileName = Path.Combine(secureFilePath, AppConfig.MySqlBulkLoaderCsvFile + "-junk");
bl.TableName = m_testTable;
bl.CharacterSet = "UTF8";
bl.Columns.AddRange(new string[] { "one", "two", "three", "four", "five" });
bl.NumberOfLinesToSkip = 1;
bl.FieldTerminator = ",";
bl.FieldQuotationCharacter = '"';
bl.FieldQuotationOptional = true;
bl.Expressions.Add("five = UNHEX(five)");
bl.Local = false;
try
{
int rowCount = await bl.LoadAsync();
}
catch (Exception exception)
{
while (exception.InnerException != null)
{
exception = exception.InnerException;
}
if (!(exception is FileNotFoundException))
{
try
{
Assert.Contains("Errcode: 2 ", exception.Message, StringComparison.OrdinalIgnoreCase);
}
catch (ContainsException)
{
Assert.Contains("OS errno 2 ", exception.Message, StringComparison.OrdinalIgnoreCase);
}
Assert.Contains("No such file or directory", exception.Message);
}
}
}
}
public async Task BulkLoadLocalCsvFileNotFound()
{
using (var connection = new MySqlConnection(GetLocalConnectionString()))
{
await connection.OpenAsync();
MySqlBulkLoader bl = new MySqlBulkLoader(connection);
bl.Timeout = 3; //Set a short timeout for this test because the file not found exception takes a long time otherwise, the timeout does not change the result
bl.FileName = AppConfig.MySqlBulkLoaderLocalCsvFile + "-junk";
bl.TableName = m_testTable;
bl.CharacterSet = "UTF8";
bl.Columns.AddRange(new string[] { "one", "two", "three", "four", "five" });
bl.NumberOfLinesToSkip = 1;
bl.FieldTerminator = ",";
bl.FieldQuotationCharacter = '"';
bl.FieldQuotationOptional = true;
bl.Expressions.Add("five = UNHEX(five)");
bl.Local = true;
try
{
int rowCount = await bl.LoadAsync();
}
catch (MySqlException mySqlException)
{
while (mySqlException.InnerException != null)
{
if (mySqlException.InnerException is MySqlException innerException)
{
mySqlException = innerException;
}
else
{
Assert.IsType <System.IO.FileNotFoundException>(mySqlException.InnerException);
break;
}
}
if (mySqlException.InnerException is null)
{
Assert.IsType <System.IO.FileNotFoundException>(mySqlException);
}
}
catch (Exception exception)
{
//We know that the exception is not a MySqlException, just use the assertion to fail the test
Assert.IsType <MySqlException>(exception);
}
;
}
}
public async Task BulkLoadLocalTsvFile()
{
using var connection = new MySqlConnection(GetLocalConnectionString());
MySqlBulkLoader bl = new MySqlBulkLoader(connection);
bl.FileName = AppConfig.MySqlBulkLoaderLocalTsvFile;
bl.TableName = m_testTable;
bl.Columns.AddRange(new string[] { "one", "two", "three", "four", "five" });
bl.NumberOfLinesToSkip = 1;
bl.Expressions.Add("five = UNHEX(five)");
bl.Local = true;
int rowCount = await bl.LoadAsync();
Assert.Equal(20, rowCount);
}
public async Task BulkLoadMissingFileName()
{
MySqlBulkLoader bl = new MySqlBulkLoader(m_database.Connection);
bl.TableName = m_testTable;
bl.Columns.AddRange(new string[] { "one", "two", "three", "four", "five" });
bl.NumberOfLinesToSkip = 1;
bl.FieldTerminator = ",";
bl.FieldQuotationCharacter = '"';
bl.FieldQuotationOptional = true;
bl.Expressions.Add("five = UNHEX(five)");
bl.Local = false;
#if BASELINE
await Assert.ThrowsAsync <System.NullReferenceException>(async() =>
{
int rowCount = await bl.LoadAsync();
});
#else
await Assert.ThrowsAsync <System.InvalidOperationException>(async() =>
{
int rowCount = await bl.LoadAsync();
});
#endif
}
public void BulkLoadConflictOptionReplaceAsync()
{
ExecuteSQL("CREATE TABLE BulkLoadConflictOptionReplaceAsyncTest (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col1");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadConflictOptionReplaceAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.Local = true;
loader.LoadAsync().Wait();
path = Path.GetTempFileName();
sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col2");
}
sw.Flush();
sw.Dispose();
loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadConflictOptionReplaceAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.ConflictOption = MySqlBulkLoaderConflictOption.Replace;
loader.Local = true;
loader.LoadAsync().Wait();
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadConflictOptionReplaceAsyncTest", Connection);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col2", dt.Rows[0][1].ToString().Trim());
}
public void BulkLoadConflictOptionReplaceAsync()
{
_fixture.execSQL("CREATE TABLE BulkLoadConflictOptionReplaceAsyncTest (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col1");
}
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(_fixture.conn);
loader.TableName = "BulkLoadConflictOptionReplaceAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LoadAsync().Wait();
path = Path.GetTempFileName();
sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col2");
}
sw.Flush();
sw.Close();
loader = new MySqlBulkLoader(_fixture.conn);
loader.TableName = "BulkLoadConflictOptionReplaceAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.ConflictOption = MySqlBulkLoaderConflictOption.Replace;
loader.LoadAsync().Wait();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM BulkLoadConflictOptionReplaceAsyncTest", _fixture.conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.Equal(20, dt.Rows.Count);
Assert.Equal("col2", dt.Rows[0][1].ToString().Trim());
}
public async Task ExportToMySql(MySqlConnection connection, string filename)
{
var loader = new MySqlBulkLoader(connection)
{
TableName = _tableName,
FileName = filename,
NumberOfLinesToSkip = 1,
FieldTerminator = ",",
LineTerminator = "\n",
CharacterSet = "utf8",
EscapeCharacter = '\b', // use an impossible value
};
await loader.LoadAsync();
File.Delete(filename);
}
public void BulkLoadFieldQuotingAsync()
{
st.execSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), name2 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 500; i++)
{
sw.WriteLine(i + "\t`col1`\tcol2");
}
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(st.conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldQuotationCharacter = '`';
loader.FieldQuotationOptional = true;
Console.WriteLine("Calling Asynchronous version of MySqlBulkLoader.Load (LoadAsync)");
System.Diagnostics.Stopwatch timer = new System.Diagnostics.Stopwatch();
timer.Start();
loader.LoadAsync();
Console.WriteLine("Wait 1 seconds to give a chance to Asynchronous method to finish.");
System.Threading.Thread.Sleep(1000);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", st.conn);
DataTable dt = new DataTable();
da.Fill(dt);
while (dt.Rows.Count < 500)
{
Console.WriteLine(string.Format("Asynchronous task is still running, processed records at this time:{0}", dt.Rows.Count));
da.Fill(dt);
}
timer.Stop();
Console.WriteLine(string.Format("Asynchronous task finished in:{0}", GetElapsedTime(timer)));
Assert.Equal(500, dt.Rows.Count);
Assert.Equal("col1", dt.Rows[0][1]);
Assert.Equal("col2", dt.Rows[0][2].ToString().Trim());
}
public async Task BulkLoadLocalTsvFileDoubleEscapedTerminators()
{
using (MySqlConnection connection = new MySqlConnection(AppConfig.ConnectionString))
{
MySqlBulkLoader bl = new MySqlBulkLoader(connection);
bl.FileName = AppConfig.MySqlBulkLoaderLocalTsvFile;
bl.TableName = m_testTable;
bl.Columns.AddRange(new string[] { "one", "two", "three", "four", "five" });
bl.NumberOfLinesToSkip = 1;
bl.Expressions.Add("five = UNHEX(five)");
bl.LineTerminator = "\\n";
bl.FieldTerminator = "\\t";
bl.Local = true;
int rowCount = await bl.LoadAsync();
Assert.Equal(20, rowCount);
}
}
public async Task BulkLoadLocalCsvFile()
{
MySqlBulkLoader bl = new MySqlBulkLoader(m_database.Connection);
bl.FileName = AppConfig.MySqlBulkLoaderLocalCsvFile;
bl.TableName = m_testTable;
bl.CharacterSet = "UTF8";
bl.Columns.AddRange(new string[] { "one", "two", "three", "four", "five" });
bl.NumberOfLinesToSkip = 1;
bl.FieldTerminator = ",";
bl.FieldQuotationCharacter = '"';
bl.FieldQuotationOptional = true;
bl.Expressions.Add("five = UNHEX(five)");
bl.Local = true;
int rowCount = await bl.LoadAsync();
Assert.Equal(20, rowCount);
}
public void BulkLoadColumnOrderAsync()
{
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL(@"CREATE TABLE Test (id INT NOT NULL, n1 VARCHAR(250), n2 VARCHAR(250),
n3 VARCHAR(250), PRIMARY KEY(id))");
// first create the external file
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(path);
for (int i = 0; i < 20; i++)
{
sw.WriteLine(i + ",col3,col2,col1");
}
sw.Flush();
sw.Close();
MySqlBulkLoader loader = new MySqlBulkLoader(st.conn);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.FieldTerminator = ",";
loader.LineTerminator = Environment.NewLine;
loader.Columns.Add("id");
loader.Columns.Add("n3");
loader.Columns.Add("n2");
loader.Columns.Add("n1");
Console.WriteLine("Calling Asynchronous version of MySqlBulkLoader.Load (LoadAsync)");
loader.LoadAsync();
Console.WriteLine("Wait 1 seconds to give a chance to Asynchronous method to finish.");
System.Threading.Thread.Sleep(1000);
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", st.conn);
DataTable dt = new DataTable();
da.Fill(dt);
Assert.Equal(20, dt.Rows.Count);
Assert.Equal("col1", dt.Rows[0][1]);
Assert.Equal("col2", dt.Rows[0][2]);
Assert.Equal("col3", dt.Rows[0][3].ToString().Trim());
}
