Working on a C# Server with SQL Server and PetaPoco, this is the approach we took to clean-up data in Unit Tests.
A typical unit test would have Setup and Teardown as follows:
[TestFixture]
internal class PlatformDataObjectTests
{
private IDatabaseConfiguration _dbConfig;
private Database _pocoDatabase;
private PlatformDataObject _platformDto;
[SetUp]
public void Setup()
{
_dbConfig = new CommonTestsAppConfig().GetDatabaseConfiguration();
_pocoDatabase = new Database(_dbConfig.ConnectionString, SqlClientFactory.Instance);
_platformDto = new PlatformDataObject(_pocoDatabase);
_platformDto.BeginTransaction();
}
[TearDown]
public void TearDown()
{
Console.WriteLine("Last Sql: {0}", _pocoDatabase.LastCommand);
_platformDto.RollbackTransaction();
_platformDto.Dispose();
}
// ...
}
Where PlatformDataObject is a class responsible for communicating with the database e.g. performing Select Insert Update Deletes. All the *DataObject types inherit ServerDataObject - the base class has methods for aborting, rolling back or committing the transaction.
/// <summary>
/// A Data-Transfer Object which allows creation and querying of Platform types from the database
/// </summary>
[ExportType(typeof(IPlatformDataObject))]
public class PlatformDataObject : ServerDataObject, IPlatformDataObject
{
private static readonly ILog Log = LogManager.GetLogger(typeof (ProductDataObject));
private const string PlatformTable = "t_Platform";
public PlatformDataObject(IPocoDatabase pocoDatabase) : base(pocoDatabase)
{
}
...
}
/// <summary>
/// A base Data-Transfer Object type
/// </summary>
public abstract class ServerDataObject : IServerDataObject
{
protected const string Star = "*";
private readonly IPocoDatabase _pocoDatabase;
public ServerDataObject(IPocoDatabase pocoDatabase)
{
_pocoDatabase = pocoDatabase;
}
public string LastCommand
{
get { return PocoDatabase.LastCommand; }
}
public IPocoDatabase PocoDatabase
{
get { return _pocoDatabase; }
}
public int TransactionDepth
{
get { return _pocoDatabase.TransactionDepth; }
}
public bool TransactionAborted { get; private set; }
public void BeginTransaction()
{
_pocoDatabase.BeginTransaction();
}
public void AbortTransaction()
{
_pocoDatabase.AbortTransaction();
}
public void RollbackTransaction()
{
TransactionAborted = true;
}
public virtual void Dispose()
{
if (TransactionAborted)
_pocoDatabase.AbortTransaction();
else
_pocoDatabase.CompleteTransaction();
}
}
All of the unit tests would call RollbackTransaction(), would would ultimately call IDbTransaction.Rollback().
In tests we found it routine to create a new instance of a *DataObject, create some rows using Insert statements, perform tests on them (Selects, Updates etc...) and then roll-back.
We may setup a set of test data before all tests are run using a SetUpFixture - a class run once before all tests are run, and delete / rollback the data in teardown after all tests are run.