Test Driven Development with Visual Studio for Database Professionals

For many years now, developers have had the upper hand when it comes to Test-Driven Development. No matter the language, there was a handy toolset to use - NUnit, JUnit, and xUnits for Perl, Python, Ruby, Delphi, and many others. But when it came time to implement the logic in the database, the options were few. Many people turned to developing their own unit testing solution - if they did unit tests at all.

With the release of Visual Studio 2005, that changed for SQL Server developers. As part of Visual Studio Team System, an edition for Database Professionals (aptly named "Visual Studio 2005 Team Edition for Database Professionals") was released which helps answer the following:

• Where the true representation of the database is stored.
• How to enable developers to modify the schema of the database and send those updates to DBAs for approval in a standardized fashion
• How to be able to make changes to the schema (like renaming a column) and understand the impact across the database
• How to unit test stored procedures, including how to generate data for those tests

While the first three points are great - and solve a lot of problems for teams - the fourth point is often overlooked. Because unit testing and data generation are first class citizens in DBPro, developers can integrate stored procedure development into their Test-Driven Development cycle. This provides a lot of power and confidence to the teams to know how well their system is working and a deeper understanding of the things affected by changes to the schema.

Before we dive into doing Test-Driven Development in DBPro, let's look at how developers take advantage of unit testing frameworks to create business logic in their code. Say we have a requirement to calculate the discount for a given order. The discount matrix looks like:

• $0-$99.99 -> No discount
• $100 - $299.99 -> 2% discount
• $300 - $999.99 -> 4% discount
• $1000 or above -> 7% discount

Using Visual Studio .NET, we can create a test project and write the business logic test-first. Our first test might look like:

[TestMethod]
public voidOrderOfZeroDollarsShouldHaveZeroDiscount()
{
 	double orderAmount = 0.00;
 	double discountExpected = 0.00;
 	double actualDiscount = 
 		OrderDiscount.CalculateDiscountFor(orderAmount);
 	Assert.AreEqual<double>(discountExpected, 		actualDiscount);
}

Which we could implement with a method like:

public static double CalculateDiscountFor(double orderAmount)
{
 	return 0.00;
}

And we could continue on with our tests and class until we had implemented our table. However, this would mean that any changes to our discounts would require a recompilation of code, or, at the very minimum, a change to a configuration file.

If this logic was instead stored in a table, we could call a stored procedure with the order amount and it could look up the values. However, if we just went right out and created a table and a stored procedure, we'd have some questions quickly come up. How should the table be structured? How should we represent the low and high end of the ranges? What about those edge cases?

This is exactly the kind of thing unit testing in DBPro helps answer. Let's take a look at how to implement this in SQL Server using stored procedures and tables in a test-driven fashion. In order to follow along, you will either need Visual Studio 2005 or 2008 Beta 2 with Team Edition for Database Professionals (DBPro) and Team Edition for Testers or Developers installed. You can download a 180-Day trial from http://msdn2.microsoft.com/en-us/teamsystem/default.aspx.

To begin, we need a database. Tests in DBPro are executed against a real database. Typically it is best to avoid touching file systems, databases and other external sources in unit tests (Feathers, Michael, Working Effectively with Legacy Code, Prentice Hall PTR, 2004) as it slows down your test suite. However, you would probably treat these as integration tests, and the power of the tests offsets the slowness of them. To help with the speed of the tests, we are going to be executing them against a local SQL Express database.

From Visual Studio go to View->Server Explorer. Right click on Data Connections and choose "Create New SQL Server Database":

Enter your database server (we're using (local)\SQLEXPRESS to connect in this example) and enter a database name of OnlineStore:

You should now see the connection to the database listed in Server Explorer. Next we need to set up a project to write our business logic against. From Visual Studio, choose File->New->Project. In the Project Types column, you should see an entry for "Database Projects". Expanding that, you'll see an entry for "Microsoft SQL Server". Click on that and select the SQL Server 2005 Wizard. Name the project OnlineStore:

When you click Ok, you may get a warning that your SQL Server doesn't support full-text indexing. This is because SQL Express doesn't. You can safely ignore this if you are following along.)

We now go through the 2005 Wizard. You can click next on all of the screens until you get to Configure Build/Deploy. DBPro is actually an offline representation of our database, and we can deploy the project very similar to any other .NET project. Click the Edit button next to Target Connection and point it to the database we set up earlier:

With that set, click Ok, and then click Finish on the Wizard. It will give a summary page of what is happening behind the scenes. Click Finish once it has finished creating the project.

At this point we have a test database on SQL Express and a project which represents both an offline view of our database and a place where we can determine the true schema of the database. We're finally ready to start writing this business logic. Right-Click on our solution and choose Add->New Project. Add a new Test Project, and name it OnlineStoreTests:

This will add a test project to your solution. You can close out of the files that are opened, and delete the AuthoringTests.txt, ManualTest1.mht and UnitTest1.cs files that were generated. Next, right click on the Test project and choose Add->New Test. From the Add New Test dialog, choose Database Unit Test and name it OrderDiscountTests.cs:

When you click Ok, a configuration wizard pops up. This allows us to specify what connection we want to run the tests under. It also allows us to specify a second connection to validate the tests. This is great for situations where the test should run in the context of a normal user, but perhaps the stored procedure modifies tables the user account doesn't have access to.

For now, we'll choose to execute the unit tests using the connection we set up earlier, so choose the OnlineStore connection from the drop down. Since we are going to be developing our stored procedures in our database project as we write our tests, we'll also set the configuration to automatically deploy any changes we've made to the database project before the tests are run. This will lead to a delay before the tests are executed, but may save you from wondering why a test pass and finding out it was because you didn't deploy the changes. Your screen should look like:

Note that we could also generate test data before the unit tests are run. This is a very powerful capability that we won't go into here, but one that you would be encouraged to explore. With our configuration complete, click Ok. You'll now see three main areas in Visual Studio that are important for writing the unit tests. The first is the current test area:

This has dropdowns to choose which test we are looking at, whether we are examining the Test, Pre-test or Post-test scripts, and some buttons to add, delete and rename tests.

The second area is our main test writing area. It has the message:

Tests will be written using T-SQL as we'll see in a bit. The last area is the Test Condition section:

This is where we specify what should happen after the test script has run, and is analogous to Asserts in xUnit testing frameworks.

We have one more housekeeping step before we get started. When we created our OrderDiscountTests class, it created a default test for us. Click on the Rename button and call it ZeroDollarOrderShouldHaveZeroDiscount. Now, click on the "Click here to create" button. Delete out the comment and enter the following:

exec sp_calculate_discount_for_order 0.00

We want our test to call our stored procedure with an order amount of $0.00. Based on our table from the beginning of the article, this should return an discount amount of 0.00. So we need to add a Test Condition to compare what was returned with what we are expecting. In our Test Conditions section, delete out the Inconclusive result (by clicking the red X), then choose Scalar Value from the drop down list and click add:

This allows us to compare the result in a specific row and column from the result set to an expected value. Right-Click on the row and choose Properties, and edit it so that we expect 0.00 from Row 1, Column 1:

Now, run our unit tests by going to the Test menu and choosing Run (in 2005 choose to run without the debugger). You'll notice that it takes a while for the test to run. This is because it is comparing the database to our database project to see if any deployments need to happen and setting up some other first time settings. You should see the test fail because it can't find the sp_calculate_discount_for_order. So let's fix that. Right-Click your database project and choose Add->Stored Procedure:

Name it sp_calculate_discount_for_order and click Ok. Notice that what comes up is a SQL definition for a stored procedure. Modify it to be:

CREATE PROCEDURE [dbo].[sp_calculate_discount_for_order]
 	@orderAmount money
AS
 	SELECT 0.00
RETURN 0;

Out of the box we get support for analyzing ResultSets. So our stored procedure will return the discount amount we are expecting. Once you've change the stored procedure to match the above, save the file. At this point we have the definition stored in our database project but not in our database. You can open the database we created to verify that it currently doesn't have any stored procedures. Let's run our test again:

It passed! You may have noticed a delay again before this test was run. That's because it was deploying out our changes to the test database.

Let's add another test to check the other side of the zero discount. From your test screen, click the green plus and name the test NinetyNineNinetyNineOrderShouldHaveZeroDiscount. Modify the script to call our stored procedure and change the test condition to verify that we received a zero discount from the stored procedure:

With that set up, run the tests again. You should notice they run much faster this time, and both pass:

Let's now write a test for the next discount level. Orders from $100.00 - $299.99 get a 2% discount. Add a new test called OneHundredDollarOrderShouldHaveTwoPercentDiscount. This time, the scalar value we are expecting is 0.02:

Now run our tests:

The first two pass, but the third fails that it expected 0.02, but got 0.00. We could put the logic to make this test pass in the stored procedure, but since we are in the database already, let's create a table to store the values. Right-Click on the database project and choose Add->Table, naming it OrderDiscounts with the following definition:

CREATE TABLE [dbo].[OrderDiscounts]
(
 	low_range float NOT NULL,
 	high_range float NOT NULL,
 	discount_amount float NOT NULL
);

Since we want to control what is in this table to test that our business logic works, we'll create a script that runs before all of our tests to insert the proper values into this table. From our OrderDiscountTests.cs file, choose the drop down with the test names and select (Common Scripts):

Notice that the drop down next to that gives us two options - Test Initialize and Test Cleanup. For our purposes here we'll only be using Test Initialize to populate the database. Make sure Test Initialize is selected and click the "Click here to create" link. Populate it with the following script:

INSERT INTO OrderDiscounts(low_range, high_range, discount_amount)
VALUES (0.00, 99.99, 0.00);

INSERT INTO OrderDiscounts(low_range, high_range, discount_amount)
VALUES (100.00, 299.99, 0.02);

INSERT INTO OrderDiscounts(low_range, high_range, discount_amount)
VALUES (300.00, 999.99, 0.04);

INSERT INTO OrderDiscounts(low_range, high_range, discount_amount)
VALUES (1000.00, 10000000.00, 0.07);

If we rerun our tests, we'll see the delay as our new table is pushed out, and we'll see the test we last added still failing. Let's get that green. Modify our stored procedure to select the discount from the table we just added:

CREATE PROCEDURE [dbo].[sp_calculate_discount_for_order]
 	@orderAmount money
AS
 	SELECT discount_amount from OrderDiscounts
 	where @orderAmount between low_range and high_range
RETURN 0;

Now rerun our tests:

Green! However, there's something fishy going on. Go back to one of our tests and add a condition to make sure our stored procedure is only returning one row:

Now rerun our tests. Do they pass?

No! Even stranger, look at the reason why:

9 Rows? Let's go look in the database to see what's going on:

Woah! It looks like our test data is getting inserted over and over. This is a very important lesson - when working with database, data can hang around and impact your tests without realizing it. Let's go back to our test scripts and fix that. Click over to OrderDiscountTests and choose our (Common scripts) then modify it to add a truncate statement at the beginning:

TRUNCATE TABLE OrderDiscounts;
INSERT INTO OrderDiscounts(low_range, high_range, discount_amount)
VALUES (0.00, 99.99, 0.00);
 
INSERT INTO OrderDiscounts(low_range, high_range, discount_amount)
VALUES (100.00, 299.99, 0.02);
 
INSERT INTO OrderDiscounts(low_range, high_range, discount_amount)
VALUES (300.00, 999.99, 0.04);
 
INSERT INTO OrderDiscounts(low_range, high_range, discount_amount)
VALUES (1000.00, 10000000.00, 0.07);

Now rerun our tests, and ...

All green! Before we finish out the rest of our test cases, there's one thing some of you may wonder. All is fine and dandy if the order amount is exactly the low_range or high_range, but what if it is in between the high_range and the low_range of the next level? In other words, what happens if our order amount is 99.997 through some accidental calculation? Instead of wondering, let's find out and add guidance on what should happen in that situation. Add a new test to our OrderDiscountTests file called NinetyNineNinetyNineNineShouldHaveZeroDiscount. Your business may want it the other way - anything over $99.99 goes to the next level. Execute our stored procedure and add a test condition to make sure 0.00 is returned:

exec sp_calculate_discount_for_order 99.999

And run our tests. Do they pass?

No. If you look at the error message, it was because no rows were returned. We could change the data we are inserting into the table, but what if someone else made the same mistake? Given the business logic we have, it looks like we only care about 2 decimal places, so the money type is overkill. Let's change the stored procedure:

CREATE PROCEDURE [dbo].[sp_calculate_discount_for_order]
 	@orderAmount float
AS
 	SELECT discount_amount from OrderDiscounts
 	where ROUND(@orderAmount, 2, 1) between low_range and high_range
RETURN 0;

And rerun our tests:

All green! Time to get to work implementing all of the other cases. However, that will be left as an exercise for the reader.

As you can see, with Team Edition for Database Professionals, developers familiar with Test-Driven Development can continue their familiar Red-Green-Refactor cycle even when working with Stored Procedures. Developers not doing Test-Driven Development can still take advantage of the offline representation and unit testing features to ensure business logic implemented in the database is functioning appropriately.

About the author

When not spending time running after with his two daughters, Cory enjoys programming test-first in a variety of languages including Ruby, C# and Java and speaking at Code Camps and User Groups. Cory currently works for Microsoft as a Field Engineer, and writes regularly on his blog at http://www.cornetdesign.com.

Other Articles: