public void SettingAgeOnCommand()
{
execSQL("CREATE TABLE test2 (id INT, name VARCHAR(20), name2 VARCHAR(20))");
execSQL("INSERT INTO test2 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = GetConnectionString(true) + ";logging=true;table cache=true;default table cache age=1";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("test2", c);
cmd.CommandType = CommandType.TableDirect;
cmd.CacheAge = 20;
ConsumeReader(cmd);
Thread.Sleep(1000);
// now run the query again but this time it shouldn't generate a call to the database
// since we have overriden the connection string cache age of 1 second and set it
// to 20 seconds on our command
ConsumeReader(cmd);
}
Assert.AreEqual(1, listener.Find("Resultset Opened: field(s) = 3"));
}
public void SimpleTableCaching()
{
execSQL("CREATE TABLE test (id INT, name VARCHAR(20), name2 VARCHAR(20))");
execSQL("INSERT INTO test VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = GetConnectionString(true) + ";logging=true;table cache=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("test", c);
cmd.CommandType = CommandType.TableDirect;
ConsumeReader(cmd);
// now run the query again but this time it shouldn't generate a call to the database
ConsumeReader(cmd);
}
Assert.AreEqual(1, listener.Find("Resultset Opened: field(s) = 3"));
}
public void BatchUpdatesAndDeletes()
{
execSQL("CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))");
execSQL("INSERT INTO test VALUES (1, 'boo'), (2, 'boo'), (3, 'boo')");
MySqlTrace.Listeners.Clear();
MySqlTrace.Switch.Level = SourceLevels.All;
GenericListener listener = new GenericListener();
MySqlTrace.Listeners.Add(listener);
string connStr = GetConnectionString(true) + ";logging=true;allow batch=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", c);
MySqlCommandBuilder cb = new MySqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
da.UpdateBatchSize = 100;
DataTable dt = new DataTable();
da.Fill(dt);
dt.Rows[0]["name"] = "boo2";
dt.Rows[1]["name"] = "boo2";
dt.Rows[2]["name"] = "boo2";
da.Update(dt);
}
Assert.AreEqual(1, listener.Find("Query Opened: UPDATE"));
}
public void ProcedureCache()
{
if (Version < new Version(5, 0))
{
return;
}
// open a new connection using a procedure cache
string connStr = GetConnectionString(true);
connStr += ";procedure cache size=25;logging=true";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
// install our custom trace listener
GenericListener myListener = new GenericListener();
System.Diagnostics.Trace.Listeners.Add(myListener);
for (int x = 0; x < 10; x++)
{
execSQL("CREATE PROCEDURE spTest" + x + "() BEGIN SELECT 1; END");
MySqlCommand cmd = new MySqlCommand("spTest" + x, c);
cmd.CommandType = CommandType.StoredProcedure;
for (int y = 0; y < 20; y++)
{
cmd.ExecuteNonQuery();
}
}
// remove our custom trace listener
System.Diagnostics.Trace.Listeners.Remove(myListener);
// now see how many times our listener recorded a cache hit
Assert.AreEqual(190, myListener.Find("from procedure cache"));
Assert.AreEqual(10, myListener.Find("from server"));
}
}
public void UseOldSyntaxGivesWarning()
{
Trace.Listeners.Clear();
GenericListener listener = new GenericListener();
Trace.Listeners.Add(listener);
string connStr = conn.ConnectionString + ";old syntax=yes;pooling=false";
MySqlConnection conn2 = new MySqlConnection(connStr);
conn2.Open();
Assert.IsTrue(listener.Find("Use Old Syntax is now obsolete") != 0);
conn2.Close();
Trace.Listeners.Clear();
}
public void ConnectionStringExpiry()
{
execSQL("CREATE TABLE test3 (id INT, name VARCHAR(20), name2 VARCHAR(20))");
execSQL("INSERT INTO test3 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')");
GenericListener listener = new GenericListener();
string connStr = GetConnectionString(true) + ";logging=true;table cache=true;default table cache age=1";
using (MySqlConnection c = new MySqlConnection(connStr))
{
c.Open();
MySqlCommand cmd = new MySqlCommand("test3", c);
cmd.CommandType = CommandType.TableDirect;
ConsumeReader(cmd);
Thread.Sleep(1500);
// now run the query again but this time it should generate a call to the database
// since our next query is past the cache age of 1 second
ConsumeReader(cmd);
}
Assert.AreEqual(2, listener.Find("Resultset Opened: field(s) = 3"));
}
