tSQLt - Assertions

In this post my aim is to introduce the basic tSQLt assertions.  The examples should be enough to get you going with assertions. My assumption is that you have at least some experience writing unit tests with any unit testing framework.

tSQLt.Fail

CREATE PROCEDURE [UnitTest_FirstGo].[test Fail]
AS
BEGIN
  EXEC tSQLt.Fail 'This test should fail and display this message';
END

 tSQLt.AssertEquals

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEquals]
AS
BEGIN
  --Assemble
  DECLARE @expected int
  DECLARE @actual int
 SET @expected = 1;
 SET @actual = 0;
  
  --Act
  
  --Assert
  EXEC tSQLt.AssertEquals @expected, @actual, 'The is a TEST FAILURE MESSAGE!'
END

 tSQLt.AssertEquals with System Under Test (SUT)

First we need to create something to represent the SUT

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_SUM]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_SUM]
 
GO

CREATE FUNCTION fn_SUM
(
 @a int,
 @b int
)
RETURNS int
BEGIN
 RETURN @a+@b;
END

and now the test

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEquals with SUT]
AS
BEGIN
  --Assemble
  DECLARE @expected int
  DECLARE @actual int
 SET @expected = 3;
  --Act
 SELECT @actual = dbo.fn_SUM(1,2);
  
  --Assert
  EXEC tSQLt.AssertEquals @expected, @actual
END

tSQLt.AssertEqualsString

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEqualsString]
AS
BEGIN
  --Assemble
  DECLARE @expected varchar(20)
  DECLARE @actual varchar(20)
 SET @expected = 'hello world';
 SET @actual = 'goodbye world';
  
  --Act
  
  --Assert
  EXEC tSQLt.AssertEqualsString @expected, @actual, 'The is a TEST FAILURE MESSAGE!'

END

tSQLt.AssertEqualsTable

This one takes a bit of time to get your head around, but once you get going and when used in conjunction with Fake tables it's really awesome.

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertEqualsTable]
AS
BEGIN
  --Assemble
 if exists(select * 
 from information_schema.tables
 where table_schema='dbo' and table_name='tablea')
 begin
  drop table dbo.TableA;
  create table dbo.TableA (id int not null);
 end

 if exists(select * 
 from information_schema.tables
 where table_schema='dbo' and table_name='tableb')
 begin
  drop table dbo.TableB;
  create table dbo.TableB (id int not null);
 end

 insert tablea (id) values (1);
 insert tablea (id) values (2);
 insert tablea (id) values (3);


 insert tableb (id) values (1);
 --insert tableb (id) values (2);
 insert tableb (id) values (3);

  
  --Act
 -- nothing to do here
  
  --Assert
  exec tSQLt.AssertEqualsTable 'TableA', 'TableB'

END

Uncomment the commented out insert to make the test pass.

The failure message attempts to point you to what failed showing which rows matched and which failed to match.

tSQLt.AssertObjectEquals

For this test we once again need some setup (i.e. we need to create an object to test if it exists)

SELECT OBJECT_ID (N'dbo.fn_SUM', N'F')
IF OBJECT_ID (N'dbo.fn_SUM', N'F') IS NOT NULL
 DROP FUNCTION dbo.fn_sum
 
GO 
 
CREATE FUNCTION fn_SUM
(
 @a int,
 @b int
)
RETURNS int
BEGIN
 RETURN @a+@b;
END

And now the test

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertObjectExists]
AS
BEGIN

  --Assert
  EXEC tSQLt.AssertObjectExists 'fn_SUM'

END

tSQLt.AssertResultSetsHaveSameMetaData

CREATE PROCEDURE [UnitTest_FirstGo].[test AssertObjectExists]
AS
BEGIN

  --Assemble
 if exists(select * 
 from information_schema.tables
 where table_schema='dbo' and table_name='tablea')
 begin
  drop table dbo.TableA;
  create table dbo.TableA (id int not null);
 end

 if exists(select * 
 from information_schema.tables
 where table_schema='dbo' and table_name='tablec')
 begin
  drop table dbo.TableC;
  create table dbo.TableC (id int not null, name varchar(50) not null);
 end

 insert tablea (id) values (1);
 insert tablea (id) values (2);
 insert tablea (id) values (3);


 insert tablec (id, name) values (5, 'andrew')
 
  --Act
 -- nothing to do here
  
  --Assert
  exec tSQLt.AssertResultSetsHaveSameMetaData 
   'select * from tablea',
   'select * from tablec'

END

That's it for now. I hope to get some time to look at how to isolate dependencies in the near future.

MSDTC Troubleshooting

Yesterday afternoon I was caught out by this error for a few hours.

A severe error occurred on the current command. The results, if any, should be discarded. OLE DB provider "SQLNCLI10" for linked server "LinkedServerName" returned message "No transaction is active.".

I had release the appliaction I am currently working on to testing.  The applcation has been runnin g perfectly on my development machine, but no such luck on our testers machine.  A new feature for this release is the use of distributed transactions to complete some of the business functionality.  This obviously requires that MSDTC is setup and a linked server is created on the local SQL Server. 

The MSDTC and linked server were setup on the test machine as I had described in my test document.

It took me a fair bit of googling to find the solution.  Most of the obvious setup problems were covered in my test document.  The problem in the end turned out to be the firewall on the test machine, something I should have checked much sooner.

Links for future reference:
http://stackoverflow.com/questions/673806/msdtc-how-many-ports-are-needed
http://support.microsoft.com/kb/306843
http://www.lewisroberts.com/2009/08/16/msdtc-through-a-firewall-to-an-sql-cluster-with-rpc/

Comparing Byte Arrays with Linq

This morning I ran into a small issue. I needed to extract a series of images from a database table. The problem I had was there were duplicate images in the database table and I needed a unique set of images written to disk. To filter the duplicates I have used the Enumerable.SequenceEqual linq operator. So here is how I did it.

using (var sqlConnection = new SqlConnection(connectionString))
{
 sqlConnection.Open();

 foreach (var deliveryNumber in deliveryNumbers)
 {
  
  var images = new List<byte[]>();

  // get all ewtphotos for this sapdeliverynumber 
  using (var sqlCommand = new SqlCommand())
  {
   sqlCommand.Connection = sqlConnection;
   sqlCommand.CommandType = CommandType.Text;
   sqlCommand.CommandText = "select imagedata " +
          "from delivery d " +
          "inner join image i on d.imageid=i.imageid " +
          "where d.deliverynumber='" + sapDeliveryNumber + "'";
   using (var sqlDataReader = sqlCommand.ExecuteReader())
   {
    while (sqlDataReader.Read())
    {
     var imageData = (byte[])sqlDataReader["ImageData"];

     var imageExists = images.Any(image => image.SequenceEqual(imageData));
     if (!imageExists) images.Add(imageData);

    }
   }

  }
  WriteImagesToDisk(sapDeliveryNumber, images);
 }

}

private static void WriteImagesToDisk(string deliveryNumber, List<byte[]> images)
{
 var counter = 1;
 foreach (var image in images)
 {
  var fileFullName = @"images\DeliveryNumber_" + deliveryNumber + "_" + counter + ".jpg";
  File.WriteAllBytes(fileFullName, image);
  counter++;
 }
}