Ensure integration tests on a database are isolated

PersistenenceSpecification.cs

namespace FastTrack.Model.Specifications
{
    using HibernatingRhinos.Profiler.Appender.NHibernate;
    using NHibernate;
    using SMS.Foundation.Specifications;
    using StructureMap;

    public abstract class PersistenceSpecification : Specification
    {
        static PersistenceSpecification()
        {
            ObjectFactory.Initialize(i => i.AddRegistry<MsSQLPersistenceRegistry>());
            SessionFactory = ObjectFactory.GetInstance<ISessionFactory>();
        }

        protected ISession Session;
        protected static readonly ISessionFactory SessionFactory;

        protected override void WithContext()
        {
            Session = SessionFactory.OpenSession();
            var cmd = Session.Connection.CreateCommand();
            cmd.CommandText = @"
SET ROWCOUNT 0

-- disable all constraints
EXEC sp_msforeachtable 
  @command1 = ""ALTER TABLE ? NOCHECK CONSTRAINT all"", 
  @whereand = ""and o.uid = 1"" 

-- delete data in all tables
EXEC sp_MSForEachTable 
  @command1 = ""DELETE FROM ?"",
  @whereand = ""and o.uid = 1"" 

-- enable all constraints
exec sp_msforeachtable 
  @command1 = ""ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"",
  @whereand = ""and o.uid = 1"" 

insert into hibernate_unique_key values (1)
";
            cmd.ExecuteNonQuery();
            NHibernateProfiler.Initialize();
        }

        protected override void TidyUp()
        {
            if (!Session.IsOpen) return;

            Session.Flush();
            Session.Close();
        }
    }
}