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));
}
