public void BulkLoadSimple()
{
executeSQL("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(new FileStream(path, FileMode.Create));
for (int i = 0; i < 200; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
int count = loader.Load();
Assert.Equal(200, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.Equal(200, dt.Rows.Count);
Assert.Equal("'Test'", dt.Rows[0][1].ToString().Trim());
}
public void CommentsInSQL()
{
executeSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
string sql = "INSERT INTO Test /* my table */ VALUES (1 /* this is the id */, 'Test' );" +
"/* These next inserts are just for testing \r\n" +
" comments */\r\n" +
"INSERT INTO \r\n" +
" # This table is bogus\r\n" +
"Test VALUES (2, 'Test2')";
MySqlCommand cmd = new MySqlCommand(sql, Connection);
cmd.ExecuteNonQuery();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test", Connection);
TestDataTable table = new TestDataTable();
da.Fill(table);
Assert.Equal(1, table.Rows[0]["id"]);
Assert.Equal("Test", table.Rows[0]["name"]);
Assert.Equal(2, table.Rows.Count);
Assert.Equal(2, table.Rows[1]["id"]);
Assert.Equal("Test2", table.Rows[1]["name"]);
}
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 BulkLoadEscaping()
{
ExecuteSQL("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(new FileStream(path, FileMode.Create));
for (int i = 0; i < 200; i++)
{
sw.WriteLine(i + ",col1\tstill col1,col2");
}
sw.Flush();
sw.Dispose();
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.EscapeCharacter = '\t';
loader.FieldTerminator = ",";
loader.Local = true;
int count = loader.Load();
Assert.AreEqual(200, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("col1still col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2].ToString().Trim());
}
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 ShowProcessList()
{
MySqlConnectionStringBuilder cb = new MySqlConnectionStringBuilder(Connection.ConnectionString);
cb.RespectBinaryFlags = false;
MySqlConnection c = new MySqlConnection(cb.ConnectionString);
using (c)
{
c.Open();
MySqlCommand cmd = new MySqlCommand("show processlist", c);
TestDataTable dt = new TestDataTable();
using (MySqlDataReader rdr = cmd.ExecuteReader())
{
dt.Load(rdr);
}
DataRow row = dt.Rows[0];
Assert.True(row["User"].GetType().Name == "String");
Assert.True(row["Host"].GetType().Name == "String");
Assert.True(row["Command"].GetType().Name == "String");
}
}
public void TokenizerBatching()
{
executeSQL("CREATE TABLE Test (id INT, expr INT,name VARCHAR(20), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test",
Connection);
MySqlCommand ins = new MySqlCommand(
"INSERT INTO test (id, expr, name) VALUES(?p1, (?p2 * 2) + 3, ?p3)",
Connection);
da.InsertCommand = ins;
ins.UpdatedRowSource = UpdateRowSource.None;
ins.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
ins.Parameters.Add("?p2", MySqlDbType.Int32).SourceColumn = "expr";
ins.Parameters.Add("?p3", MySqlDbType.VarChar, 20).SourceColumn = "name";
TestDataTable dt = new TestDataTable();
da.Fill(dt);
for (int i = 1; i <= 100; i++)
{
DataRow row = dt.NewRow();
row["id"] = i;
row["expr"] = i;
row["name"] = "name " + i;
dt.Rows.Add(row);
}
da.UpdateBatchSize = 10;
da.Update(dt);
}
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 ShowCreateTable()
{
executeSQL("CREATE TABLE Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))");
TestDataTable dt = Utils.FillTable("SHOW CREATE TABLE Test", Connection);
Assert.Equal(1, dt.Rows.Count);
Assert.Equal(2, dt.Columns.Count);
}
public static TestDataTable FillTable(string sql, MySqlConnection conn)
{
MySqlDataAdapter da = new MySqlDataAdapter(sql, conn);
TestDataTable dt = new TestDataTable();
da.Fill(dt);
return(dt);
}
public void NullAsAType()
{
TestDataTable dt = Utils.FillTable(@"SELECT 'localhost' as SERVER_NAME,
null as CATALOG_NAME, database() as SCHEMA_NAME", Connection);
Assert.True(dt.Rows[0][0].GetType() == typeof(string));
Assert.Equal(DBNull.Value, dt.Rows[0][1]);
Assert.True(dt.Rows[0][2].GetType() == typeof(string));
}
public void CommentSymbolInTableName()
{
executeSQL("CREATE TABLE Test (`PO#` int(11) NOT NULL auto_increment, " +
"`PODate` date default NULL, PRIMARY KEY (`PO#`))");
executeSQL("INSERT INTO Test ( `PO#`, `PODate` ) " +
"VALUES ( NULL, '2006-01-01' )");
string sql = "SELECT `PO#` AS PurchaseOrderNumber, " +
"`PODate` AS OrderDate FROM Test";
TestDataTable dt = Utils.FillTable(sql, Connection);
Assert.Equal(1, dt.Rows.Count);
}
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 BulkLoadReadOnlyFileAsync()
{
executeSQL("CREATE TABLE BulkLoadReadOnlyFileAsyncTest (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();
FileInfo fi = new FileInfo(path);
FileAttributes oldAttr = fi.Attributes;
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
try
{
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "BulkLoadReadOnlyFileAsyncTest";
loader.FileName = path;
loader.Timeout = 0;
loader.LoadAsync().ContinueWith(loadResult =>
{
int dataLoaded = loadResult.Result;
TestDataTable dt = Utils.FillTable("SELECT * FROM BulkLoadReadOnlyFileAsyncTest", Connection);
Assert.Equal(dataLoaded, dt.Rows.Count);
Assert.Equal("'Test'", dt.Rows[0][1].ToString().Trim());
}).Wait();
}
finally
{
fi.Attributes = oldAttr;
fi.Delete();
}
}
public void BulkLoadColumnOrder()
{
ExecuteSQL(@"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(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 = "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");
loader.Local = true;
int count = loader.Load();
Assert.AreEqual(20, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.AreEqual(20, dt.Rows.Count);
Assert.AreEqual("col1", dt.Rows[0][1]);
Assert.AreEqual("col2", dt.Rows[0][2]);
Assert.AreEqual("col3", dt.Rows[0][3].ToString().Trim());
}
public void BulkLoadReadOnlyFile()
{
ExecuteSQL("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(new FileStream(path, FileMode.Create));
for (int i = 0; i < 200; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
FileInfo fi = new FileInfo(path);
FileAttributes oldAttr = fi.Attributes;
fi.Attributes = fi.Attributes | FileAttributes.ReadOnly;
try
{
MySqlBulkLoader loader = new MySqlBulkLoader(Connection);
loader.TableName = "Test";
loader.FileName = path;
loader.Timeout = 0;
loader.Local = true;
int count = loader.Load();
Assert.AreEqual(200, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("'Test'", dt.Rows[0][1].ToString().Trim());
}
finally
{
fi.Attributes = oldAttr;
fi.Delete();
}
}
public void TokenizerBatching()
{
#if NETCOREAPP3_1 || NET5_0
if (!System.Runtime.InteropServices.RuntimeInformation.IsOSPlatform(System.Runtime.InteropServices.OSPlatform.Windows))
{
Assert.Ignore();
}
#endif
ExecuteSQL("CREATE TABLE Test (id INT, expr INT,name VARCHAR(20), PRIMARY KEY(id))");
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM Test",
Connection);
MySqlCommand ins = new MySqlCommand(
"INSERT INTO test (id, expr, name) VALUES(?p1, (?p2 * 2) + 3, ?p3)",
Connection);
da.InsertCommand = ins;
ins.UpdatedRowSource = UpdateRowSource.None;
ins.Parameters.Add("?p1", MySqlDbType.Int32).SourceColumn = "id";
ins.Parameters.Add("?p2", MySqlDbType.Int32).SourceColumn = "expr";
ins.Parameters.Add("?p3", MySqlDbType.VarChar, 20).SourceColumn = "name";
TestDataTable dt = new TestDataTable();
da.Fill(dt);
for (int i = 1; i <= 100; i++)
{
DataRow row = dt.NewRow();
row["id"] = i;
row["expr"] = i;
row["name"] = "name " + i;
dt.Rows.Add(row);
}
da.UpdateBatchSize = 10;
da.Update(dt);
}
[TestCase(false, "c:/SymLink/", false)] // symbolic link
public void BulkLoadUsingSafePath(bool allowLoadLocalInfile, string allowLoadLocalInfileInPath, bool shouldPass)
{
DirectoryInfo info;
bool isSymLink = false;
if (!string.IsNullOrWhiteSpace(allowLoadLocalInfileInPath))
{
info = new DirectoryInfo(allowLoadLocalInfileInPath);
isSymLink = info.Attributes.HasFlag(FileAttributes.ReparsePoint);
}
Connection.Settings.AllowLoadLocalInfile = allowLoadLocalInfile;
Connection.Settings.AllowLoadLocalInfileInPath = allowLoadLocalInfileInPath;
ExecuteSQL(string.Format("CREATE TABLE `{0}`.Test (id INT NOT NULL, name VARCHAR(250), PRIMARY KEY(id))", Connection.Database), true);
// create the external file to be uploaded
string path = Path.GetTempFileName();
StreamWriter sw = new StreamWriter(new FileStream(path, FileMode.Create));
for (int i = 0; i < 200; i++)
{
sw.WriteLine(i + "\t'Test'");
}
sw.Flush();
sw.Dispose();
// create another path to test against unsafe directory
Directory.CreateDirectory("otherPath");
// copy the external file to our safe path
Directory.CreateDirectory("tmp/data");
if (!File.Exists("tmp/data/file.tmp"))
{
File.Copy(path, "tmp/data/file.tmp");
}
string filePath = allowLoadLocalInfile ? path : Path.GetFullPath("tmp/data/file.tmp");
MySqlBulkLoader loader = new MySqlBulkLoader(Connection)
{
TableName = "Test",
FileName = filePath,
Timeout = 0,
Local = true
};
if (shouldPass)
{
int count = loader.Load();
Assert.AreEqual(200, count);
TestDataTable dt = Utils.FillTable("SELECT * FROM Test", Connection);
Assert.AreEqual(200, dt.Rows.Count);
Assert.AreEqual("'Test'", dt.Rows[0][1].ToString().Trim());
}
else if (isSymLink && !Directory.Exists(allowLoadLocalInfileInPath))
{
Assert.Ignore("For the symbolic link test to run, it should be manually created before executing it.");
}
else
{
var ex = Assert.Throws <MySqlException>(() => loader.Load());
if (allowLoadLocalInfileInPath == " " || allowLoadLocalInfileInPath is null)
{
if (Version > new Version(8, 0))
{
Assert.AreEqual("Loading local data is disabled; this must be enabled on both the client and server sides", ex.Message);
}
else
{
Assert.AreEqual("The used command is not allowed with this MySQL version", ex.Message);
}
}
else
{
StringAssert.Contains("allowloadlocalinfileinpath", ex.Message);
}
}
File.Delete(path);
Directory.Delete("tmp", true);
Directory.Delete("otherPath");
}