public void ProcedureCache() { // open a new connection using a procedure cache string connStr = Connection.ConnectionString; 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++) { ExecuteSQL("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 BatchUpdatesAndDeletes() { st.execSQL("CREATE TABLE test (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20))"); st.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 = st.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.Equal(1, listener.Find("Query Opened: UPDATE")); }
public void ConnectionStringExpiry() { executeSQL("CREATE TABLE test3 (id INT, name VARCHAR(20), name2 VARCHAR(20))"); executeSQL("INSERT INTO test3 VALUES (1, 'boo', 'hoo'), (2, 'first', 'last'), (3, 'fred', 'flintstone')"); #if !NETCOREAPP1_1 MySqlTrace.Listeners.Clear(); MySqlTrace.Switch.Level = SourceLevels.All; GenericListener listener = new GenericListener(); MySqlTrace.Listeners.Add(listener); string connStr = Connection.ConnectionString + ";logging=true;table cache=true;default table cache age=1"; #else string connStr = Connection.ConnectionString + ";table cache=true;default table cache age=1"; #endif 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); } #if !NETCOREAPP1_1 Assert.Equal(2, listener.Find("Resultset Opened: field(s) = 3")); #endif }
public void SettingAgeOnCommand() { _fixture.execSQL("CREATE TABLE test2 (id INT, name VARCHAR(20), name2 VARCHAR(20))"); _fixture.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 = _fixture.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.Equal(1, listener.Find("Resultset Opened: field(s) = 3")); }
public void SimpleTableCaching() { _fixture.execSQL("CREATE TABLE test (id INT, name VARCHAR(20), name2 VARCHAR(20))"); _fixture.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 = _fixture.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.Equal(1, listener.Find("Resultset Opened: field(s) = 3")); }
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')"); 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("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")); }
public void NotReadingEveryRow() { execSQL("INSERT INTO Test VALUES (1, 'Test1')"); execSQL("INSERT INTO Test VALUES (2, 'Test2')"); execSQL("INSERT INTO Test VALUES (3, 'Test3')"); execSQL("INSERT INTO Test VALUES (4, 'Test4')"); Trace.Listeners.Clear(); GenericListener listener = new GenericListener(); Trace.Listeners.Add(listener); MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn); MySqlDataReader reader = null; try { reader = cmd.ExecuteReader(); reader.Read(); reader.Read(); listener.Clear(); Assert.IsTrue(reader.NextResult()); Assert.IsTrue(listener.Find("Reason: Not all rows in resultset were read.") > 0); reader.Read(); reader.Read(); listener.Clear(); Assert.IsFalse(reader.NextResult()); Assert.IsTrue(listener.Find("Reason: Not all rows in resultset were read.") > 0); } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (reader != null) { reader.Close(); } } }
public void NotReadingEveryField() { execSQL("INSERT INTO Test VALUES (1, 'Test1')"); execSQL("INSERT INTO Test VALUES (2, 'Test2')"); execSQL("INSERT INTO Test VALUES (3, 'Test3')"); execSQL("INSERT INTO Test VALUES (4, 'Test4')"); Trace.Listeners.Clear(); GenericListener listener = new GenericListener(); Trace.Listeners.Add(listener); MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn); MySqlDataReader reader = null; try { reader = cmd.ExecuteReader(); reader.Read(); reader.GetInt32(0); // access the first field reader.Read(); Assert.IsTrue(reader.NextResult()); Assert.IsTrue(listener.Find("Fields not accessed: name") != 0); reader.Read(); listener.Clear(); Assert.AreEqual("Test3", reader.GetString(1)); Assert.IsFalse(reader.NextResult()); Assert.IsTrue(listener.Find("Fields not accessed: id") > 0); } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (reader != null) { reader.Close(); } } }
public void NotReadingEveryField() { execSQL("INSERT INTO Test VALUES (1, 'Test1')"); execSQL("INSERT INTO Test VALUES (2, 'Test2')"); execSQL("INSERT INTO Test VALUES (3, 'Test3')"); execSQL("INSERT INTO Test VALUES (4, 'Test4')"); Trace.Listeners.Clear(); GenericListener listener = new GenericListener(); Trace.Listeners.Add(listener); MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn); MySqlDataReader reader = null; try { reader = cmd.ExecuteReader(); reader.Read(); reader.GetInt32(0); // access the first field reader.Read(); Assert.IsTrue(reader.NextResult()); Assert.IsTrue(listener.Find("Fields not accessed: name") != 0); reader.Read(); listener.Clear(); Assert.AreEqual("Test3", reader.GetString(1)); Assert.IsFalse(reader.NextResult()); Assert.IsTrue(listener.Find("Fields not accessed: id") > 0); } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (reader != null) reader.Close(); } }
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 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 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 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() { // open a new connection using a procedure cache string connStr = GetConnectionString(true); connStr += ";procedure cache size=25;logging=true"; MySqlConnection c = new MySqlConnection(connStr); try { 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("DROP PROCEDURE IF EXISTS spTest" + 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")); } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (c != null) c.Close(); for (int x = 0; x < 10; x++) execSQL("DROP PROCEDURE IF EXISTS spTest" + x); } }
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 NotReadingEveryRow() { execSQL("INSERT INTO Test VALUES (1, 'Test1')"); execSQL("INSERT INTO Test VALUES (2, 'Test2')"); execSQL("INSERT INTO Test VALUES (3, 'Test3')"); execSQL("INSERT INTO Test VALUES (4, 'Test4')"); Trace.Listeners.Clear(); GenericListener listener = new GenericListener(); Trace.Listeners.Add(listener); MySqlCommand cmd = new MySqlCommand("SELECT * FROM Test; SELECT * FROM Test WHERE id > 2", conn); MySqlDataReader reader = null; try { reader = cmd.ExecuteReader(); reader.Read(); reader.Read(); listener.Clear(); Assert.IsTrue(reader.NextResult()); Assert.IsTrue(listener.Find("Reason: Not all rows in resultset were read.") > 0); reader.Read(); reader.Read(); listener.Clear(); Assert.IsFalse(reader.NextResult()); Assert.IsTrue(listener.Find("Reason: Not all rows in resultset were read.") > 0); } catch (Exception ex) { Assert.Fail(ex.Message); } finally { if (reader != null) reader.Close(); } }