Putting Unit Tests to Work
By: Grant Fritchey
There are multiple working assumptions in effect. You've got a working a knowledge of TSQL, C# and Visual Studio. Biggest assumption, you'll be kind to me since I'm not a .NET expert. Please, if you spot issues with my methods let me know in the comments section. I'm also going to assume a nodding acquaintance with the Team Edition for Database Developers.
Before we get started on the details of my implementation, let me acknowledge that withoutt a blog entry. I could never have done this. The core of what I'm going to present came from there. I just made the necessary changes to get the test condition in place that I wanted. My sincere appreciation to him for doing the work to make this possible. Any mistakes in the code below are mine and mine alone.
The rest of this is honestly as easy as it looks.
The condition that I set up relies on two result sets. One result set is obviously the stored procedure (trigger, function) being tested. The second result set can be either a straight select from another table or database, a second stored procedure, or by generating a temporary table, populating it with data and selecting from there. The code verifies that it actually got two result sets. It then checks that the result sets are equal in number of rows returned, columns, data types and the data in the columns.
The MSDN documentation covers all the basics on creating your own test condition. Please refer to this if you have any questions. First off, you do need to ensure that you add references to:
Now we'll go through and set up the beginning of the code. Start with the 'using' statements and the necessary code to create the class.
using TestTools = Microsoft.VisualStudio.TestTools.UnitTesting;
public class DataEqual : TestCondition
Notice the DisplayName & string. This is how the test condition will show up in DBPro. Now I'm going to declare some variables to hold the result set that I want to compare and the result set that I want to compare it to. I also hold which batch I'm referring to in order to ensure that I'm looking at a single set of statements and not more than one. Right now it's hard-wired to only deal with a single batch. I create the class & load a set of default values for you.
private int _resultSet;
private int _batch;
private int _CompareSet;
_resultSet = 1;
_CompareSet = 2;
_batch = 1;
Now we override the Assert function. This is where the meat of the code is created. It took took the time to verify that we have a batch processed and that the batch returned result sets. If any of these things are problems, I simply throw an error specified as an AssertFailedException and describe what the problem encountered.
... to read more articles, visit http://sqa.fyicenter.com/art/