public void TestExecuteSqlTransaction() { //Clean all data from table before starting Console.WriteLine("deleting from testtableread"); string sql = "DELETE from TestTableRead"; IDatabaseConnection databaseConnection = DatabaseConnection.CurrentConnection; databaseConnection.ExecuteRawSql(sql); //Create transaction with Error Console.WriteLine("beginning transaction"); IDbConnection dbConnection = databaseConnection.GetConnection(); dbConnection.Open(); IDbTransaction dbTransaction = dbConnection.BeginTransaction(databaseConnection.IsolationLevel); //insert first record bool rolledBack = false; int statementsExecutedPriorToRollBack = 0; try { Console.WriteLine("doing first insert."); sql = "Insert into TestTableRead (TestTableReadData) VALUES ('Test')"; databaseConnection.ExecuteRawSql(sql, dbTransaction); statementsExecutedPriorToRollBack++; //insert second record Console.WriteLine("doing second insert."); sql = "Insert into TestTableRead (TestTableReadData) VALUES ('Test')"; databaseConnection.ExecuteRawSql(sql, dbTransaction); statementsExecutedPriorToRollBack++; Console.WriteLine("committing."); dbTransaction.Commit(); dbConnection.Close(); statementsExecutedPriorToRollBack++; } catch { Console.WriteLine("error. rolling back. "); dbTransaction.Rollback(); dbConnection.Close(); rolledBack = true; } Console.WriteLine("creating second datareader."); IDataReader dr2 = databaseConnection.LoadDataReader( new SqlStatement(databaseConnection, "SELECT * FROM TestTableRead Order By TestTableReadData")); Assert.IsTrue(rolledBack); Console.WriteLine("reading from second datareader."); Assert.IsFalse(dr2.Read()); Assert.IsTrue(statementsExecutedPriorToRollBack == 1); //Create transaction without Error dbConnection = databaseConnection.GetConnection(); dbConnection.Open(); dbTransaction = dbConnection.BeginTransaction(databaseConnection.IsolationLevel); //insert first record try { sql = "Insert into TestTableRead (TestTableReadData) VALUES ('Test')"; databaseConnection.ExecuteRawSql(sql, dbTransaction); //insert second record sql = "Insert into TestTableRead (TestTableReadData) VALUES ('Test2')"; databaseConnection.ExecuteRawSql(sql, dbTransaction); dbTransaction.Commit(); dbConnection.Close(); } catch { dbTransaction.Rollback(); dbConnection.Close(); } dr2.Close(); dr2 = databaseConnection.LoadDataReader( new SqlStatement(databaseConnection, "SELECT * FROM TestTableRead Order By TestTableReadData")); Assert.IsTrue(dr2.Read()); Assert.IsTrue(dr2.Read()); Assert.IsFalse(dr2.Read()); dr2.Close(); }
public void ClearDatabase() { string database = _databaseConnection.GetConnection().Database; _databaseConnection.ExecuteRawSql(String.Format("DROP DATABASE IF EXISTS {0};CREATE DATABASE {0};", database)); }