SQL Kit

I was playing around with a recently downloaded Beta 2 of VS 2010. As of me , I work a lot with Databases and Data warehouses but I did have an opportunity to work with C# based development using ADO.NET entity framework sometime back.

I am a big fan or writing test cases and making sure that I achieve a good code coverage. With VS 2010 I can write test cases for database objects like stored procedures. In this blog post I am going to write a very small bit about how you can write test cases for SQL Queries using VS 2010. (You don’t need any NUnit or csUnit as you might need in previous versions of VS and even in that case I don’t see an easy way as VS 2010 provides).

Start VS 2010 and go to ribbon on the top

Click New Test and select Database Unit Test

Configure the database connection string and Project name and other stuff

You will have a window which will look like this. Click on the CREATE NEW link

You can my TestMyProc.cs . This is a C# class. You can Paste you SQL code in the designer and the C# class will wrap this and execute it using ADO.NET . You need not worry writing the TESTFIXTURES and etc etc

The designer has this commented bit on top which is self explanatory.

/*
Add T-SQL statements here to exercise the database object
that you want to test.

To test a stored procedure, invoke it here by adding
an EXEC statement that has appropriate parameters.

In the lower pane, you can add test conditions that verify
whether the results of your T-SQL statements match what
you expect.
*/

Ok . Now lest write some test . Lets Begin with a negative on. Which Will fail.

I have configured my Database to connect to adventureworks.

The SQL Code I want to test is

SELECT * FROM HumanResources.Employee   WHERE BusinessEntityID  = 1 --will fail

Now go down to test condition panel. Here you can specify the conditions which will be based on what is the expected output. So Lets say I don’t expect any result set.

After you have configured the Condition you need to run this test.

The test result panel will have the below output stating that test has failed.

Now change the SQL Query to

SELECT * FROM HumanResources.Employee   WHERE BusinessEntityID  = -1 --will succeed

and run the test again. The test result panel will show PASSED

So we have written our first Unit Test to Test a SQL Query. The same thing can be extended to Stored Procedures. I don’t know as of now if it supports MDX unit testing out of the box but if go the code behind (F7) you will see a C# class. You can add ADOMD or AMO and test MDX , XMLA or anything else in theory. I have not tried it yet though.

Happy Test Driven Development for Database Guys!!. I will be blogging more on this.