public
Last active — forked from /DatabaseDeleter.cs

  • Download Gist
DatabaseDeleter.cs
C#
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 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();
}
}

I like it, though since this deletes everything from every table, have you considered using TRUNCATE instead of DELETE to make it fast for large datasets?

I'm also curious why you use the clause "WHERE s.name = 'dbo'" instead of "WHERE t.type = 'U'"
FWIW, this is how I query the user table list:
select object_id, name from sys.tables where type='U' and name <> 'sysdiagrams' order by name

Please sign in to comment on this gist.

Something went wrong with that request. Please try again.