当通过C#存在外部依赖项时，如何截断表

出于测试目的，我有一个方法，它接受一个SqlConnection或一个连接字符串和一个字符串。该方法根据一组"危险"字符串检查SqlConnection或DbContext.Database.Connection.ConnectionString，如果连接字符串有任何"危险"的字符串，则不执行其查询。

我基本上需要知道如何执行（通过C#）截断/删除表中的所有数据，但问题是外部依赖关系。我正在尝试以下方法。删除所有依赖项，删除表，然后恢复所有依赖项。但是，我的恢复所有依赖关系代码有问题。我如何通过C#实现这一点？

应删除表的主方法。

public int DeleteFromDatabase(SqlConnection sqlConnection, string tableName)
{
        int success = 0;
        string sqlTrunc = "Delete from " + tableName;
        if (isSafeSqlConnection(sqlConnection))
        {
            DropAllConstraints();
            using (sqlConnection)
            {
                SqlCommand cmd = new SqlCommand(sqlTrunc, sqlConnection);
                sqlConnection.Open();
                success = cmd.ExecuteNonQuery();
                sqlConnection.Close();
            }
            ReinstateAllConstraints(); //<=error happens here.
        }
        return success;
}

这将删除所有限制：

public void DropAllConstraints()
{
    string[] queries = File.ReadAllLines(@"Utility'UnsafeStrings'dropallcontraint.txt");
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        foreach (var item in queries)
        {
            var command = new SqlCommand(item, connection);
            command.ExecuteNonQuery();
        }
        connection.Close();
    }
}

这会检查传入的连接是否不是活动服务器：

private bool isSafeSqlConnection(SqlConnection connection)
{
    string pathToUNsafeStrings = @"Utility'UnsafeStrings'UnsafeStrings.txt";
    string[] unsafeStrings = File.ReadAllLines(pathToUNsafeStrings);
    foreach (var item in unsafeStrings)
    {
        if (connection.ConnectionString.Contains(item))
            return false;
    }
    return true;
}

这个方法基本上执行从这个查询返回的每个条目：

select
    'ALTER TABLE dbo.' + object_name(fk.parent_object_id) + 
    ' ADD CONSTRAINT ' + fk.name +
    ' FOREIGN KEY(' + c1.name + ') REFERENCES dbo.' + 
    object_name(fk.referenced_object_id) + '(' + c2.name + ')' as col1
from 
    sys.foreign_keys fk
inner join
    sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
inner join
    sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
inner join
    sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id
public void ReinstateAllConstraints()
{
    string[] reinstateAllConstraints = File.ReadAllLines(@"Utility'UnsafeStrings'reisntateconstraint.txt");
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        foreach (var item in reinstateAllConstraints)
        {
            var command = new SqlCommand(item, connection);
            command.ExecuteNonQuery();
        }
        connection.Close();
    }
}

public bool TruncateTable(string tableName)
{
    string sqlTrunc = $"Delete from {tableName}";
    if (isSafeSqlConnection(sqlConnection))
    {
        DisableAllForeignKeys();
        using (sqlConnection)
        {
            DeleteAllDependencies(tableName, sqlConnection);
            SqlCommand cmd = new SqlCommand(sqlTrunc, sqlConnection);
            sqlConnection.Open();
            success = cmd.ExecuteNonQuery();
            sqlConnection.Close();
        }
        EnableAllForeignKeys();
    }
    return success;
}

public void DisableAllForeignKeys(SqlConnection sqlConnection)
{
    using(var command = new SqlCommand($"EXEC sp_msforeachtable '"ALTER TABLE ? NOCHECK CONSTRAINT all'"", sqlConnection))
        command.ExecuteNonQuery();
}
public void EnableAllForeignKeys(SqlConnection sqlConnection)
{
    using(var command = new SqlCommand($"EXEC sp_msforeachtable '"ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'"", sqlConnection))
        command.ExecuteNonQuery();
}
private static void DeleteAllDependencies(string tableName, SqlConnection sqlConnection)
{
    var sql =
        $@"SELECT t.name  AS 'Table that contains FK', 
fk.name AS 'FK Name',
t1.Name AS 'Table that is being referenced'
FROM   sys.foreign_key_columns fkc 
INNER JOIN sys.tables t ON t.object_id = fkc.parent_object_id 
INNER JOIN sys.tables t1 ON t1.object_id = fkc.referenced_object_id
INNER JOIN sys.columns c1 ON c1.object_id = fkc.parent_object_id AND c1.column_id = fkc.parent_column_id 
INNER JOIN sys.foreign_keys fk ON fk.object_id = fkc.constraint_object_id 
INNER JOIN sys.schemas sc ON t.schema_id = sc.schema_id
WHERE  (sc.name + '.' +t1.name) = 'dbo.{
            tableName}';";
    var command = sqlConnection.CreateCommand();
    command.CommandText = sql;
    List<Tuple<string, string, string>> tuples;
    using (var dataReader = command.ExecuteReader())
    {
        var enumerator = dataReader.GetEnumerator();
        tuples = Enumerable.Range(1, int.MaxValue)
                           .TakeWhile(i => enumerator.MoveNext())
                           .Select(i => (IDataRecord)enumerator.Current)
                           .Select(dr => Tuple.Create(dr.GetString(0), dr.GetString(1), dr.GetString(2)))
                           .ToList();
    }

    foreach (var tuple in tuples)
    {
        using (var sqlCommand = sqlConnection.CreateCommand())
        {
            sqlCommand.CommandText = $"DELETE FROM {tuple.Item1}";
            sqlCommand.ExecuteNonQuery();
        }
    }
}