Skip to content

Instantly share code, notes, and snippets.

/DatabaseDeleter.cs

Created June 18, 2013 14:26
Show Gist options
  • Save anonymous/5805777 to your computer and use it in GitHub Desktop.
Save anonymous/5805777 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @invalid-email-address Anonymous created this gist Jun 18, 2013.
    119 changes: 119 additions & 0 deletions DatabaseDeleter.cs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,119 @@
    public class DatabaseDeleter
    {
    private readonly ISessionFactory _configuration;
    private static readonly string[] _ignoredTables = new[] { "sysdiagrams", /* DB Migration tables too */ };
    private static string[] _tablesToDelete;
    private static string _deleteSql;
    private static object _lockObj = new object();
    private static bool _initialized;

    public DatabaseDeleter(ISessionFactory sessionSource)
    {
    _configuration = sessionSource;

    BuildDeleteTables();
    }

    private class Relationship
    {
    public string PrimaryKeyTable { get; private set; }
    public string ForeignKeyTable { get; private set; }
    }

    public virtual void DeleteAllData()
    {
    ISession session = _configuration.OpenSession();

    using (IDbCommand command = session.Connection.CreateCommand())
    {
    command.CommandText = _deleteSql;
    command.ExecuteNonQuery();
    }
    }

    public static string[] GetTables()
    {
    return _tablesToDelete;
    }

    private void BuildDeleteTables()
    {
    if (!_initialized)
    {
    lock (_lockObj)
    {
    if (!_initialized)
    {
    ISession session = _configuration.OpenSession();

    var allTables = GetAllTables(session);

    var allRelationships = GetRelationships(session);

    _tablesToDelete = BuildTableList(allTables, allRelationships);

    _deleteSql = BuildTableSql(_tablesToDelete);

    _initialized = true;
    }
    }
    }
    }

    private static string BuildTableSql(IEnumerable<string> tablesToDelete)
    {
    string completeQuery = "";
    foreach (var tableName in tablesToDelete)
    {
    completeQuery += String.Format("delete from [{0}];", tableName);
    }
    return completeQuery;
    }

    private static string[] BuildTableList(ICollection<string> allTables, ICollection<Relationship> allRelationships)
    {
    var tablesToDelete = new List<string>();

    while (allTables.Any())
    {
    var leafTables = allTables.Except(allRelationships.Select(rel => rel.PrimaryKeyTable)).ToArray();

    tablesToDelete.AddRange(leafTables);

    foreach (var leafTable in leafTables)
    {
    allTables.Remove(leafTable);
    var relToRemove = allRelationships.Where(rel => rel.ForeignKeyTable == leafTable).ToArray();
    foreach (var rel in relToRemove)
    {
    allRelationships.Remove(rel);
    }
    }
    }

    return tablesToDelete.ToArray();
    }

    private static IList<Relationship> GetRelationships(ISession session)
    {
    var otherquery = session.CreateSQLQuery(@"select
    so_pk.name as PrimaryKeyTable
    , so_fk.name as ForeignKeyTable
    from
    sysforeignkeys sfk
    inner join sysobjects so_pk on sfk.rkeyid = so_pk.id
    inner join sysobjects so_fk on sfk.fkeyid = so_fk.id
    order by
    so_pk.name
    , so_fk.name");

    return otherquery.SetResultTransformer(Transformers.AliasToBean<Relationship>()).List<Relationship>();
    }

    private static IList<string> GetAllTables(ISession session)
    {
    var query = session.CreateSQLQuery("select t.name from sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id WHERE s.name = 'dbo'");

    return query.List<string>().Except(_ignoredTables).ToList();
    }
    }