Deleting all records in all tables in the entire database using NHibernate

This is a great little code snippet I thought I'd share.

Before each one of my NUnit or NBehave tests, I clear out all the records from the database using NHibernate.  Unfortunately, NHibernate's HQL doesn't seem to do cascade deletes, so you have to delete your tables in the right order.  Otherwise you'll get referential integrity errors such as "The DELETE statement conflicted with the FOREIGN KEY constraint" and the query will fail.  I didn't want to have to explicitly delete each table in order either--I just wanted to loop through all my NHibernate mapped domain objects and wipe them out.

Fortunately there is a system stored procedure built in to sql server 2008 (and presumably 2005) as well that will disable all constraints in the whole database.  Then you can loop through all the tables and wipe out all the records, then re-enable all the constraints.

Below is the final code:

protected virtual void DeleteAllObjects()
{
    var types =
        typeof(DomainObject).Assembly.GetTypes().Where(
            type => type.BaseType == typeof(DomainObject) &&
            !type.IsAbstract)
            .ToArray();

    GetSession().Clear();
   
    using (ISession session = GetSession())
    {
       

        this.RunSqlNonQuery("EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' ");
        foreach (Type type in types)
        {
            session.Delete("from " + type.Name + " o");
        }
        session.Flush();
        this.RunSqlNonQuery("EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'");
    }
}

protected void RunSqlNonQuery(string sql)
{
    var session = GetSession();
    var cmd = session.Connection.CreateCommand();
    cmd.CommandText = sql;
    cmd.CommandTimeout = 120;
    cmd.CommandType = CommandType.Text;
    cmd.ExecuteNonQuery();

    session.Flush();
}

The first method uses reflection and linq to find all classes that inherit from "DomainBase" which is my base class for all NHibernate persisted domain objects.  If you have an interface or attribute something that should work as well, although I like having a base class to put in "global" domain logic. 

The second method is a utility method I use to run straight sql commands against NHibernate.  I"m not sure if it could be refactored to use NHibernate's built in ISession.CreateSqlQuery() method because this doesn't return any results.

So that should be all you need.  Just turn off constraints, loop through and delete all the types using HQL and turn the constraints on again.

I know its cliche' but happy coding.