Introduction

Some time ago, I was asked to configure automatic builds in Team System for a solution, and that build should include as much unit tests as it could. This solution contained a mix of web sites, a wizard installer, OLAP cubes, Reporting Services reports and a set of databases. Because it was in the middle of a refactor, the development team would include tests together with the refactor on the web sites and the wizard installer had a set of unit tests for the installation modules. So I started to look for a way to test the deployed databases.

After searching for a while, I found a good tool in CodePlex called SQL Load Test. This tool has the ability of translating a SQL Profiler trace file into a Visual Studio 2005 C# file, generating unit tests for each entry in the trace file. This output file is intended to be added into a Test Project.
So I downloaded the tool and I read the usage notes: really simple. There are just a few considerations for setting up the SQL Profiler, which are:

Columns

The following columns must be present:

• EventClass
• TextData
• DatabaseName - if this is omitted then the trace is assumed to be all for the same database
• SPID

Events

The following events must be present:

• SQL:BatchStarting
• RPC:Starting
• Audit Login
• Audit Logout

Additionally, the tool will ignore the following entries:

• All entries related to the master, model and msdb databases.
• All entries with no database name at all.
• All calls to sp_reset_connection.

So, after setting the profiler with the above requisites, I've asked a QA co-worker to navigate through a set of different Reporting Services' reports. After some minutes (about 5 or 6 reports, no more than 10 minutes spent), I stopped the profiler and save the trace to a file.

Using the Unit Test

Now, the following step was using the tool and transforms that trace file into a C# unit test. Well, nothing could be as easier as that. If you read the documentation, you'll see that the tool is a command-line application, and that only three parameters are needed. The first one is a name for the scenario. This is a C# style name. Then, the path and the name of the trace file. Finally (and optional), is the path and name of the configuration file. This configuration file is provided by the tool, and the settings are really simple, like creating a test method for each operation or just one with all the operations, etc. If you don't set a custom configuration file, the application will load the default parameters, which are fine for the initial tests.

I run the tool and in a few seconds I had my C# file containing the tests. Then I added the file into my project, configure the SQL connection string, and Voila: I had added more than 500 unit tests in a few minutes. I repeated this with my co-worker for different reports and modules of the application, and after 2 hours I had more than 4000 unit tests, separated into modules (Reports, OLAP queries, transactional queries, etc).

For demonstrating the use of the tool, I have a Solution called SQLUnitTesting with two projects: One database project (SQLUnitTesting) and one test project (SQLUnitTesting_TestProject). The database project has 1 table and 1 stored procedure. The sp is used to insert one record in the table. Also, I've configured the profiler to monitor the database SQLUnitTesting, executed in Management Studio two queries (one insert command and the sp) and save the trace file as SQLUnitTesting.trc.

After this, I run the following command from the command line to convert the trace file into the C# unit test

As you can see, the first parameter is the name (I used the same as the test project namespace), the name of the trace file and the name of the config file. This file is the default file that comes with the tool.

Next, two files called SQLUnitTesting_TestProject.cs and SQLUnitTesting_TestProject.stubs are created on the application folder. The first one contains the unit tests and the second contains the connection details. You can discard the .stubs file and use only the .cs file. Then, I've renamed the .cs file to SQLUnitTesting_TestProject01.cs and add it to my test project:

Finally, you must set the connection string in order to connect to the SQL server. You can do this using the method you want. For this test, I've added the following lines on the TestMethod that call the unit tests:

_connection = new SqlConnection("Initial Catalog=SQLUnitTesting;Integrated Security=SSPI;Data Source=(local)");
_connection.Open();

After all this is done, you can run the test from visual studio and see the results:

As you can see, the report indicates I run only one test. This is because the tool created all queries in a single TestMethod:

[TestMethod()]
public void SQLUnitTesting_TestProject()
{
 Statement();
 Statement2();
}

If you want to see one test per statement, then you can separate them into different TestMethods. Attached, you'll see the .cs file for my project for better understanding of how the statements are executed

Conclusion

Although there are lots of tools down there for unit testing, and even though Visual Studio provides Unit Testing for SQL, I found in this tool a great value: The user variable. Because these tests are based on database usage, you can have real-life queries to databases. Suppose you can launch the profiler on the production server and leave it running for one or two days. Then, you'll have tests that reflect what users do. In addition, you can configure the Visual Studio Test Load Agent and monitor the performance of your SQL server during the execution of the test, simulating concurrent connections and so on.

I know that this tool won't replace the Unit Tests a developer should do on the database solution, and also that there are lots of tools that can achieve similar results, but this application is a great starting point, allowing the configuration of unit tests easily and without knowing the code behind your databases.

In my case, this tool has provided me a great way for managing integrity checks on the solution databases. Usually when a change is made on a complex database, a break can occur in the most unthinking procedure or query. At that time, the dev team was changed from one contractor to another one, so until the new devs really knew the application, this approach helped to avoid releasing a build that has errors on the databases.