Introduction
In this article, I'll describe a way to unit test data caching with ASP.NET when using SQL Server, by utilizing the TraceServer class.
Background
During the summer, I spent quite a lot of time building application layer APIs whose central parts where factories that created objects based on data retrieved from a SQL Server database. As the APIs had to be built to handle high performance loads, all database requests where cached.
I tried to build the APIs in a test driven fashion, and therefore wrote xUnit tests. When I was about to test the factory methods that implemented caching, I found myself in a dilemma. How should I test that objects were actually cached and not read from the database every time, and how should I test that cached objects were released from the cache when needed?
A couple of obvious solutions popped into my head. I could let the factories expose the cache keys they were using and query the cache system in my tests. This would allow me to check that objects were actually stored in the cache and that they were released from the cache when needed. The tests would not, however, be able to test that the factories actually checked the cache and returned cached objects instead of building new ones from the database. It also required that I had to expose the cache key building methods in the factories.
Another possible solution was to modify my factories to keep track of when objects were fetched from the database or from the cache. While I, in theory, could perform all the necessary testing with this approach, it would force me to build quite a lot of extra logic into my factories that would only be used while testing. I also suspected that this could be quite error prone.
The Solution
When testing caching without automated unit tests, I would run a trace against the database with SQL Server Profiler and count the number of times a certain Stored Procedure was run, and what I really needed to do was to find a way to automate that kind of tests. After some Googling, I found the Microsoft.SqlServer.ConnectionInfo assembly which contains the TraceServer class. The TraceServer class creates a new trace on a given SQL Server based on a trace definition file which must be supplied with it. It implements the IDataReader interface, and its rows consists of the columns that are specified in the trace definition file. In order to use the TraceServer class for unit testing my factory methods, I set out to build a class of my own which would have three public methods:
StartTrace() - Starts a trace against a SQL Server instance.StopTrace() - Stops the trace.CountOccurencesInTextData(string textToMatch) - Returns the number of rows in the trace which contained a specified string.
I'll continue with a simplified description of how it can be implemented. You may, however, download a Visual Studio 2008 project with a complete class, a trace definition file, and the configuration functionality, here.
Necessary Namespaces
The first thing we need to do is to add a reference to Microsoft.SqlServer.ConnectionInfo in our project and then import the namespaces below:
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Trace;
Starting a Trace
In order to start a trace, we will need a TraceServer, an instance of the SqlConnectionInfo class which will hold the necessary information to connect to the server, and a trace definition file. The SqlConnectionInfo object will require the name of the SQL Server (such as localhost or 123.456.123.456) and the username and password of a user who has the necessary permissions to start a trace on the server. As for the trace definition file, it can be created by exporting a trace template from SQL Server Profiler (by clicking on File -> Templates -> Export Template). In the downloadable sample project and in this example, I use a definition file that only contains the RPC:Completed and SQL:BatchCompleted events, with only three columns: Event name, text data, and the required column SPID.
SqlConnectionInfo connectionInfo = new SqlConnectionInfo
("serverName", "username", "password");
TraceServer traceServer = new TraceServer();
string traceDefinitionFilePath = "BatchOrSPCompleted.tdf";
traceServer.InitializeAsReader(connectionInfo, traceDefinitionFilePath);
Stopping the Trace and Analyzing the Data
Once we're done with the trace, we stop it and then count the number of rows in which the TextData column contained a specified string.
traceServer.Stop();
string textToMatch = "Some string to match, "
+ "such as the name of a stored procedure";
int count = 0;
while(traceServer.Read())
{
if (traceServer.IsDBNull(1))
continue;
string textData = _traceServer.GetString(1);
if (textData.Contains(textToMatch))
count++;
}
traceServer.Close();
Example Usage
The functionality described above, and especially the complete class in the downloadable project, can be used in a number of ways. A simple example scenario is that we want to check that a factory class named BlogFactory fetches a certain blog from the database the first time, and from the cache the second time.
[Test]
public void TestBlogCaching()
{
string blogSelectQuery = "SELECT ID, name FROM blog";
GetBlogWhileTracing();
int numberOfDatabaseHits =
TraceCounter.CountOccurencesInTextData(blogSelectQuery);
Assert.AreEqual(1, numberOfDatabaseHits);
GetBlogWhileTracing();
numberOfDatabaseHits =
TraceCounter.CountOccurencesInTextData(blogSelectQuery);
Assert.AreEqual(0, numberOfDatabaseHits);
}
private void GetBlogWhileTracing()
{
TraceCounter.StartTrace();
BlogFactory.GetBlog(1);
TraceCounter.StopTrace;
}
Updates and Other Articles
While I'll try to post any updates here, you will be sure to find them, plus other articles by me, at bloodsweatand.net where I have a blog. This specific article can be found here.
