Last active
August 29, 2015 13:58
-
-
Save mhinze/9947265 to your computer and use it in GitHub Desktop.
Database deleter (EF)
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Collections.Generic; | |
using System.Data.Entity; | |
using System.Linq; | |
public class DatabaseDeleter | |
{ | |
static readonly string[] _ignoredTables = | |
{ | |
"sysdiagrams", | |
"usd_AppliedDatabaseScript", | |
"__MigrationHistory" | |
}; | |
readonly DbContext _dbContext; | |
readonly Lazy<string> deleteSql; | |
public DatabaseDeleter(DbContext dbContext) | |
{ | |
_dbContext = dbContext; | |
deleteSql = new Lazy<string>(GetDeleteSql); | |
} | |
string GetDeleteSql() | |
{ | |
var allTables = GetAllTables(); | |
var allRelationships = GetRelationships(); | |
var tablesToDelete = BuildTableList(allTables, allRelationships); | |
return BuildTableSql(tablesToDelete); | |
} | |
public virtual void DeleteAllObjects() | |
{ | |
var sql = deleteSql.Value; | |
_dbContext.Database.ExecuteSqlCommand(sql); | |
} | |
static string BuildTableSql(IEnumerable<string> tablesToDelete) | |
{ | |
return tablesToDelete.Aggregate(string.Empty, (current, tableName) => current + string.Format("delete from [{0}];", tableName)); | |
} | |
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)).ToList(); | |
tablesToDelete.AddRange(leafTables); | |
leafTables.ForEach(lt => | |
{ | |
allTables.Remove(lt); | |
var relToRemove = allRelationships.Where(rel => rel.ForeignKeyTable == lt).ToList(); | |
relToRemove.ForEach(toRemove => allRelationships.Remove(toRemove)); | |
}); | |
} | |
return tablesToDelete.ToArray(); | |
} | |
IList<Relationship> GetRelationships() | |
{ | |
const string sql = | |
@"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 _dbContext.Database.SqlQuery<Relationship>(sql).ToList(); | |
} | |
IList<string> GetAllTables() | |
{ | |
return _dbContext.Database.SqlQuery<string>("select name from sys.tables").Except(_ignoredTables).ToList(); | |
} | |
class Relationship | |
{ | |
public string ForeignKeyTable { get; set; } | |
public string PrimaryKeyTable { get; set; } | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment