I was asked if I could provide some of the code samples that I showed during today’s webcast. So I thought I’d post them right here.
One example I showed was how to use the regular expression generator to generate phone numbers. The expression I used was the following.
You could make this expression fancier to only generate numbers in a specific area code, etc.
Next I created a unit test for a GetAllAuctions stored procedure that I had created. I used the script generation facilities to automatically generate the stub script. After doing that, I added a SQL assertion to verify that the correct number of rows were returned from the query. The SQL to check this is as follows:
IF (@@ROWCOUNT <> 50)
RAISERROR('GetAllAuctionsTest Error: Unexpected row count',1,1)
Of course you could accomplish the same verification using a Row Count test condition.
In addition, I wrote a unit test for a CreateAuction stored procedure. I modified the automatically generated script to look like this:
-- db unit test for dbo.CreateAuction
DECLARE @AuctionID Int,
@AuctionTitle NVarChar( 50 ),
@AuctionDescription NVarChar( 2000 ),
SELECT @AuctionID = 0,
@AuctionTitle = 'Chai',
@AuctionDescription = '2 25-pack boxes of India Chai',
@AuctionStartDate = '6/14/2006',
@AuctionLength = 10,
@ProductID = 1,
@ItemQuantity = 2,
@EmployeeID = 1,
@AuctionTypeID = 1,
@MininumBidAmount = 10.00
EXEC @AuctionID = [dbo].[CreateAuction] @AuctionTitle , @AuctionDescription , @AuctionStartDate , @AuctionLength , @ProductID , @ItemQuantity , @EmployeeID , @AuctionTypeID , @MininumBidAmount
--query AuctionTitle, AuctionStartDate of created auction
--to verify appropriate results
SELECT AuctionTitle, AuctionStartDate
FROM Auctions WHERE AuctionID = @AuctionID
--delete created auction to return db to previous state
DELETE FROM Auctions WHERE AuctionID = @AuctionID
This script assigns values to the parameters of the sproc and then executes the sproc. I them select the auction title and auction start date of the auction that was just created. I would then add 2 scalar value test conditions to verify that the actual values returned is what I expected. The last statement deletes the newly created auction to return the database to its previous state.
So those are a few quick samples from the webcast. I’ll be trying to get some more involved unit test examples up here in the near future.