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