Unit Testing is commonly leveraged in application development and this can applied to database development as well, In Visual Studio 2013 integrated database testing can run directly against the database.It can even be configured automatically in the team build scenario. This post outlines how to create database unit testing in Visual Studio 2013. Create a new SQL Server database project in Visual Studio 2013, to do the unit testing of a stored procedure go to SQL Server object explorer and under the projects node you will see the database that you are trying to write the unit test case, off course it is a logical view of your database
right click on the database stored procedure and say Create Unit Tests, The unit test case can be written in Visual Basic or C#. Give a name to your test project and click ok. The unit test that we are writing is test AddEmployee stored procedure.
It then prompt you to enter the database connection to execute the unit test case
you can also choose the secondary data connection for validating the test cases. One of the reason to use the secondary data connection is you might want to execute a procedure with one set of credentials and second set of credentials with more access level permissions to do the validation. You can also choose to deploy the database project before you run the unit test. This will deploy the database against your target connection before you running unit test. When you click ok it is going to generate a database unit test.
Notice your unit test contains a Execute statement call to your stored procedure.You can set the data that you want to pass in, before you add the record to database you might want to check if this record exist, this can be achieved using Pre-test option where you can create T-SQL script where it deletes the existing record.
To validate the test case , delete the default inconclusive test condition and add the row count condition for this unit test
set the row count for this condition to 1 for passing the test case.
After validating the above condition you might want to do some post test script with row condition set to 1like below to see the employee was actually inserted into the table.
Now build your project and your test case will appear in Visual Studio test explorer, right click on it and say run selected test case.
when you run it then connects to database and it then runs you pre-test, test and post test conditions and gives you the results. Database unit testing is a powerful tool in Visual Studio 2013 to validate the functionality at database level.