Automated database unit testing on VSTS

[meridian]

I’m trying to automate some unit testing of stored procedures on an SQL Server database using Visual Studio Team Suite.

The sprocs are simple SELECT statements with a couple of parameters that return a value from the table. A simple unit test for one of the sprocs from within VSTS is the following:

DECLARE @RC INT,
@fruit_id numeric(8,0),
@language_code varchar(2)

SELECT @RC = 0,
@fruit_id = 00004545,
@language_code = 'EN'

EXEC @RC = [dbo].[usp_fruit_select] @fruit_id, @language_code

SELECT RC=@RC

Set the scalarValueCondition so that the condition fails if the value in the Resultset is not ‘Apple’, run the test and the test is successful.

I have a .csv file of the full expected result sets, which runs to several hundred rows. Now I don’t want to have to create several hundred individual unit tests, I’d prefer to be able to call the parameters and the scalar result from a file that looks like this:

fruit_id; language_code; scalarValue;
00004545; EN; Apple
00004546; FR; Pomme
00004547; DE; Apfel
00004548; EN; Pear
00004549; ES; Pera

The file has been added to the test using the Data Connection String, but that’s where I come to a halt. Where/how do I recode the parameters and scalar Value to retrieve the data from the file?

Cheers,

[lightng]

Are the unit tests run from within the .NET framework or purely in SQL server? I prefer to use the framework plus my preferred language to do unit testing.

Databases are king at set operations and tulip for logic even in 2010. The operation of parsing a delimited file is really simple in most programming languages but too much effort in TSQL; you have to write your own function to do the equivalent of string split for example.

Are you using a unit testing framework? If so, which one.