CODEWITHSUNDEEP
entity-frameworksql-server-ceintegration-testing
trailmax
trailmax

Reputation: 35126

SQL Server CE. Delete data from all tables for integration tests

We are using SQL Server CE for our integration tests. At the moment before every test, we delete all data from all columns, then re-seed test data. And we drop the database file when the structure changes.

For deletion of data we need to go through every table in correct order and issue Delete from table blah and that is error-prone. Many times I simply forget to add delete statement when I add new entities. So it would be good if we can automate data-deletion from the tables.

I have seen Jimmy Bogard's goodness for deletion of data in the correct order. I have implemented that for Entity Frameworks and that works in full-blown SQL Server. But when I try to use that in SQL CE for testing, I get exception, saying 

System.Data.SqlServerCe.SqlCeException : The specified table does not exist. [ @@sys.tables ]

SQL CE does not have supporting system tables that hold required information.

Is there a script that works with SQL CE version that can delete all data from all tables?

Upvotes: 0

Views: 505

Answers (1)

ErikEJ
ErikEJ

Reputation: 41819

SQL Server Compact does in fact have system tables listing all tables. In my SQL Server Compact scripting API, I have code to list the tables in the "correct" order, not a trivial task! I use QuickGraph, it has an extension method for sorting a DataSet. You should be able to reuse some of that in your test code: 33

public void SortTables()
{
    var _tableNames = _repository.GetAllTableNames();
    try
    {
        var sortedTables = new List<string>();
        var g = FillSchemaDataSet(_tableNames).ToGraph();
        foreach (var table in g.TopologicalSort())
        {
            sortedTables.Add(table.TableName);
        }
        _tableNames = sortedTables;
        //Now iterate _tableNames and issue DELETE statement for each
    }
    catch (QuickGraph.NonAcyclicGraphException)
    {
        _sbScript.AppendLine("-- Warning - circular reference preventing proper sorting of tables");
    }
}

You must add the QuickGraph DLL files (from Codeplex or NuGet) and you can find the implementation of GetAllTableNames and FillSchemaDataSet here http://exportsqlce.codeplex.com/SourceControl/list/changesets (in Generator.cs and DbRepository.cs)

Upvotes: 1

Related Questions