public void CanExecuteNonQueryThroughTransaction()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
int rowsAffected = db.ExecuteNonQuery(insertionCommand, transaction.Transaction);
int count = Convert.ToInt32(db.ExecuteScalar(countCommand, transaction.Transaction));
Assert.AreEqual(5, count);
Assert.AreEqual(1, rowsAffected);
}
}
}
public void CanExecuteNonQueryThroughTransaction()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
int rowsAffected = db.ExecuteNonQuery(insertionCommand, transaction.Transaction);
int count = Convert.ToInt32(db.ExecuteScalar(countCommand, transaction.Transaction));
Assert.AreEqual(5, count);
Assert.AreEqual(1, rowsAffected);
}
}
}
public void TransactionActuallyRollsBack()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
db.ExecuteNonQuery(insertionCommand, transaction.Transaction);
}
}
DbCommand wrapper = db.GetSqlStringCommand(countQuery);
int count = Convert.ToInt32(db.ExecuteScalar(wrapper));
Assert.AreEqual(4, count);
}
public void CanExecuteQueryThroughDataReaderUsingTransaction()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
using (IDataReader reader = db.ExecuteReader(insertCommand, transaction.Transaction))
{
Assert.AreEqual(1, reader.RecordsAffected);
}
}
Assert.AreEqual(ConnectionState.Open, connection.State);
}
}
public void CanExecuteQueryThroughDataReaderUsingTransaction()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
using (IDataReader reader = db.ExecuteReader(insertCommand, transaction.Transaction))
{
Assert.AreEqual(1, reader.RecordsAffected);
}
}
Assert.AreEqual(ConnectionState.Open, connection.State);
}
}
public void CanExecuteScalarDoAnInsertion()
{
string insertCommand = "Insert into Region values (99, 'Midwest')";
DbCommand command = db.GetSqlStringCommand(insertCommand);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
db.ExecuteScalar(command, transaction.Transaction);
DbCommand rowCountCommand = db.GetSqlStringCommand("select count(*) from Region");
int count = Convert.ToInt32(db.ExecuteScalar(rowCountCommand, transaction.Transaction));
Assert.AreEqual(5, count);
}
}
}
public void CanExecuteScalarDoAnInsertion()
{
string insertCommand = "Insert into Region values (99, 'Midwest')";
DbCommand command = db.GetSqlStringCommand(insertCommand);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
db.ExecuteScalar(command, transaction.Transaction);
DbCommand rowCountCommand = db.GetSqlStringCommand("select count(*) from Region");
int count = Convert.ToInt32(db.ExecuteScalar(rowCountCommand, transaction.Transaction));
Assert.AreEqual(5, count);
}
}
}
public void CanExecuteQueryThroughDataReaderUsingTransaction()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (DbCommand command = db.GetSqlStringCommand(insertString))
{
using (var transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
using (SqlCeResultSet reader = db.ExecuteResultSet(command, transaction.Transaction))
{
Assert.AreEqual(1, reader.RecordsAffected);
reader.Close();
}
}
Assert.AreEqual(ConnectionState.Open, connection.State);
command.Connection.Close();
}
}
}
public void TransactionActuallyRollsBack()
{
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
db.ExecuteNonQuery(insertionCommand, transaction.Transaction);
}
}
DbCommand wrapper = db.GetSqlStringCommand(countQuery);
int count = Convert.ToInt32(db.ExecuteScalar(wrapper));
Assert.AreEqual(4, count);
}
public void CanExecuteXmlQueryThroughTransaction()
{
string knownGoodOutputAfterChange =
"<Region RegionID=\"1\" RegionDescription=\"Eastern \" />" +
"<Region RegionID=\"2\" RegionDescription=\"Western \" />" +
"<Region RegionID=\"3\" RegionDescription=\"Northern \" />" +
"<Region RegionID=\"4\" RegionDescription=\"Southern \" />" +
"<Region RegionID=\"99\" RegionDescription=\"Midwest \" />";
string knownGoodOutputAfterRollback =
"<Region RegionID=\"1\" RegionDescription=\"Eastern \" />" +
"<Region RegionID=\"2\" RegionDescription=\"Western \" />" +
"<Region RegionID=\"3\" RegionDescription=\"Northern \" />" +
"<Region RegionID=\"4\" RegionDescription=\"Southern \" />";
string insertString = "insert into region values (99, 'Midwest')";
DbCommand insertCommand = sqlDatabase.GetSqlStringCommand(insertString);
string queryString = "Select * from Region for xml auto";
SqlCommand sqlCommand = sqlDatabase.GetSqlStringCommand(queryString) as SqlCommand;
string actualOutput = "";
using (DbConnection connection = sqlDatabase.CreateConnection())
{
connection.Open();
using (RollbackTransactionWrapper transaction = new RollbackTransactionWrapper(connection.BeginTransaction()))
{
sqlDatabase.ExecuteNonQuery(insertCommand, transaction.Transaction);
XmlReader results = sqlDatabase.ExecuteXmlReader(sqlCommand, transaction.Transaction);
results.MoveToContent();
for (string value = results.ReadOuterXml(); value != null && value.Length != 0; value = results.ReadOuterXml())
{
actualOutput += value;
}
results.Close();
}
}
Assert.AreEqual(actualOutput, knownGoodOutputAfterChange);
string confirmationString = "Select * from Region for xml auto";
SqlCommand confirmationCommand = sqlDatabase.GetSqlStringCommand(confirmationString) as SqlCommand;
string rollbackResults = RetrieveXmlFromDatabase(confirmationCommand);
Assert.AreEqual(knownGoodOutputAfterRollback, rollbackResults);
}
