.NET Musings

I had a great time (again) at Central Ohio Day of .NET, despite presenting with a broken pinkie finger on my left hand.  (Ok, since you asked, I smashed it between a really heavy object and an immovable object.)  Turns out I use my pinkie when I type.  Who knew? (I think there is an alt key and a ctl key on the right side of my keyboard, but I just can’t find it…)

My session went very well (despite the pain in my finger).  I spoke on Test Driven Development for T-SQL (you can download my slides from here).  My session was in the last time slot, which can be good or bad, but it was well attended, and followed by some really good questions, so the attendees were really listening!

I’ve been promoting and speaking TDD (in managed code) for a couple of years now, and I was challenged by a friend of mine to solve the problem for T-SQL.  I blogged about this in January (see post here) after working through the issue with a couple of co-workers, but it still wasn’t the best solution.

On the Gallio/MbUnit forums, someone suggested looking into TST (T-SQL Testing Framework) on Codeplex, so I gave it a whirl. Turns out it was a great fit for what I was trying to accomplish. I will blog more about how I’m using it later, but it’s definitely worth checking out.  As a sample of how you use it, here is one of my unit tests.  Note how the tool supports the ARRANGE/ACT/ASSERT pattern seamlessly!

ALTER PROCEDURE SQLTest_UpdatePricesByCategoryID
AS
BEGIN
	--Arrange
	SET IDENTITY_INSERT dbo.Categories ON;
	INSERT INTO dbo.Categories (CategoryID,CategoryName,ParentCategoryID) 
		VALUES ( 1,'Test1',null);
	INSERT INTO dbo.Categories (CategoryID,CategoryName,ParentCategoryID) 
		VALUES ( 2,'Test2',null);
	SET IDENTITY_INSERT dbo.Categories OFF;
	SET IDENTITY_INSERT dbo.Products ON;
	INSERT INTO dbo.Products (ProductID,CategoryID,ModelNumber,UnitCost,
		CurrentPrice,UnitsInStock) VALUES (1,1,'Model1',1,1.5,10);
	INSERT INTO dbo.Products (ProductID,CategoryID,ModelNumber,UnitCost,
	CurrentPrice,UnitsInStock) VALUES (2,2,'Model2',2,2.5,20);
	INSERT INTO dbo.Products (ProductID,CategoryID,ModelNumber,UnitCost,
	CurrentPrice,UnitsInStock) VALUES (3,2,'Model3',3,3.5,30);
	SET IDENTITY_INSERT dbo.Products OFF;
	--Act
	EXEC UpdatePricesByCategory 2,1.0;
	--Assert
	CREATE TABLE #ExpectedResult (
		ProductID INT PRIMARY KEY NOT NULL,
		UnitCost money,
		CurrentPrice MONEY,
		UnitsInStock int
	) 
	
	CREATE TABLE #ActualResult (
		ProductID INT PRIMARY KEY NOT NULL,
		UnitCost money,
		CurrentPrice MONEY,
		UnitsInStock int
	) 
	INSERT INTO #ExpectedResult (ProductID,UnitCost,CurrentPrice,UnitsInStock) 
	VALUES (1,1,1.5,10) ;
	INSERT INTO #ExpectedResult (ProductID,UnitCost,CurrentPrice,UnitsInStock) 
	VALUES (2,2,3.5,20) ;
	INSERT INTO #ExpectedResult (ProductID,UnitCost,CurrentPrice,UnitsInStock) 
	VALUES (3,3,4.5,30) ;
	INSERT INTO #ActualResult (ProductID,UnitCost,CurrentPrice,UnitsInStock) 
		SELECT ProductID,UnitCost,CurrentPrice,UnitsInStock FROM Products;
	EXEC TST.Assert.TableEquals 'Changes Happened';
END
GO

More on this tool and TDD for T-SQL will be coming to these pages, so stay tuned!

Happy Coding!