1

I imported my SQL Server 2008 DB script and created a new VS 2010 project to create Unit Testing for the database. It has built in functionality for checking return row count and scalar variables

What I need to know is how I can clear the entire database prior to running the procedures because it will check the new inserted row but requires I tell it a row# to check.

Here is the code for a unit test calling a stored proc to insert an addres:

-- database unit test for dbo.spInsertAddress
DECLARE @RC AS INT, @Street AS VARCHAR (60), @City AS VARCHAR (50), @State AS CHAR (20), @Zip AS VARCHAR (10), @Intersection1 AS VARCHAR (60), @Intersection2 AS VARCHAR (60), @AddressID AS INT;

SELECT @RC = 0,
       @Street = 'StreetName',
       @City = NULL,
       @State = NULL,
       @Zip = NULL,
       @Intersection1 = NULL,
       @Intersection2 = NULL;

EXECUTE @RC = [dbo].[spInsertAddress] @Street, @City, @State, @Zip, @Intersection1, @Intersection2, @AddressID OUTPUT;

--SELECT @RC AS RC,
--       @AddressID AS AddressID;
SELECT * FROM Address;

Here is an image of the VS unit testing: Unit Test

To reiterate the question how do I clear the database or remove whatever was inserted AFTER the procedure runs?

Also for others interested in Unit Testing a DB via VS here is a link: Create Database Unit Tests for Functions, Triggers, and Stored Procedures

Kairan
  • 5,342
  • 27
  • 65
  • 104
  • +1 for this interesting question, I was looking for a lot for a method to do that until I found my own. – Leandro Bardelli May 14 '13 at 18:54
  • @Leandro Thanks it did take a while to figure out this solution and resources. I will add a link to my main post. – Kairan May 14 '13 at 19:59
  • I saw the link but, for e.g. in my case, any change on the database it remains on it on a UT DB. I can't see other ways that specified in my answer, if you find one better please answer yourself to help everyone :) – Leandro Bardelli May 15 '13 at 13:07

1 Answers1

0

Solution 1

I use a good practice for it, but it depends on your production policy, is that not commit if is called from a UT. So everytime you get a rollback.

Solution 2

If this is not possible because the procedure must affect tables or something, you can get the records and call a store procedure with your own rollback procedure and called only from your UT

Solution 3

Also, in any case, the recommended work for this is do an environment for UT, with your own database in order the TFS Agent runs a build and run the test without touching any critical data.

Please comment if you want to review some of these answers, I can help you more if you tell me what approach it's more applicable to your use.

Leandro Bardelli
  • 10,561
  • 15
  • 79
  • 116
  • How do I tell it to rollback each time I call the unit test given my particular sample? I set my unit test to deploy to a test database so I can do whatever I want to it as far as clearing tables and stuff. – Kairan May 14 '13 at 19:58
  • no, you have to modify your store procedures, you only can rollback transactions on directly commands, no con procedure execution. Try find information like this: http://stackoverflow.com/questions/3935900/how-to-commit-and-rollback-transaction-in-sql-server – Leandro Bardelli May 14 '13 at 22:05