After we learned how to create the database in Part 2: Create the database, it is now time to modify its data.

The Why

Apart from the changes made by the actual application we are testing, we of course need to change the contents of the database between the tests methods in order to get rid of changes made by previous tests or to seed the data for the upcoming test.

This might seem trivial but I have already seen overcomplicated solutions for such tasks (like building a tree of all the tables in the database in order to delete them without failing on violated foreign key constraints – using 150 lines of code), therefore I would like to show simpler alternatives (like using 3 lines of code to achieve the same result).

Also, in part 2 of this series we learned how to create or update the database, but I also mentioned that the procedure doesn’t delete the data and we need to do that ourselves and do so it this article.

The How

Let’s start with deleting the data. We will use a stored procedure sp_MSForEachTable.

If you try to google it, you won’t find any official documentation for it, as it is an undocumented procedure and this Microsoft article says that you shouldn’t use undocumented procedures in your production setup. That sounds scary, but shouldn’t be that bad – the procedure has been around at least since 2004.

To sum up, I’m willing to use the procedure, but there exist other approaches if you aren’t comfortable with this one (like selecting all tables and then looping through them).

So, the three ‘magic’ lines are:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
EXEC sp_msforeachtable 'DELETE ?'
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'

Now we need to execute them against the database. When saved to an .sql file, it is just an ordinary SQL script that could have arbitrary contents. This also allows us to run seed scripts.

Therefore we need a method to run custom SQL:

public void RunCustomSql(string sql)
{
	using var dbConnection = GetSqlConnection();
    using IDbCommand command = dbConnection.CreateCommand();
    
    if (dbConnection.State != ConnectionState.Open)
		dbConnection.Open();
		
	command.CommandText = sql;
    command.ExecuteNonQuery();
}

Then we need a method which runs custom SQL from a file, and let’s also assume that:

we created a folder to save all our SQL scripts to,
we stored its name in dbScriptsFolderPath variable,
we don’t want to run SQL scripts that are larger than the available memory.

public void RunDbScript(string scriptName)
{
	var sql = File.ReadAllText(dbScriptsFolderPath + scriptName);
	RunCustomSql(sql);
}

And then we create a special method for database cleanup, assuming that we saved the delete script to DataCleanup.sql file:

public void CleanData()
{
	RunDbScript("DataCleanup.sql");
}

Finally, we call this method at the end of the DeployDatabase() method from part 2 of the series.

Source Code

You can view the complete source code on Github.

What’s next

Read the next posts in the series:

The previous ones are:

Last modified on 2020-10-23