Debugging SQL Server CLR Function In Visual Studio 2015

This is a step-by-step instruction of how to debug CLR code in Visual Studio. It is based on MSDN article Debugging CLR Database Objects. As stated in the comment there, the process can differ for different versions of Visual Studio. I’ve got Visual Studio 2015 Community Edition with Update 3.

1) Run Visual Studio with Administrator rights. It’s needed for VS to attach the SQL Server process.
If you skip this, you might see an error later:
Unable to debug .NET code. Could not attach to SQL Server process on 'VS2015'. Operation not supported. Unknown error: 0x80004005.
2) Open SQL Server Object Explorer in Visual Studio.
3) Right-click server and select Allow SQL/CLR Debugging.
4) Drill down to CLR function, right-click and select Debug Function…
5) In the Debug Function window enter value for input parameter and press OK.
6) Visual Studio will generate a test script and enter into the debug mode.
7) Now you can press F11 to step into the test script code and CLR function.

During the last step Windows Firewall could ask you to allow VS debugger to connect through network. Please allow it.

Also Visual Studio will always ask you to attach to SQL Server process in order to debug CLR code. Press Attach here.

SSDT Unit Test Connection For Validation. Really?

When you create database unit test project, you need to setup connections to your database. I always use only one connection to run the code and test conditions. But I was curious what is the second connection for? Where does that validation occur?

unit test connections

Now I’ve spent some time to check when this validation connection is really used. I’ve created a simple unit test and placed a simple command to check user name in every part of unit test:

Here are the results of my checks.

Part of the unit test Connection used
Test initialize Connection 2
Pre-test Connection 2
Test execution Connection 1
Post-test Connection 2
Test cleanup Connection 2

As you see, only test execution phase uses connection 1 (“execution”), whereas all the other phases use connection 2 (“validation”) if you use 2 connections. If you use only one connection, so all the steps will be run under this unique username.

It’s a bit strange that SSDT uses a term “validation” for a connection for pre- and post-test steps. But that’s out of our will 🙂

Running SSDT Database Unit Test in Transaction Mode

As I wrote in one of my previous posts (see here) SSDT leaves test data in tables after it executes INSERT command. One of the methods to delete these data is to use a special script in post-test action. The second method is to use transaction to run the unit test.

Obviously we can use explicit BEGIN TRANSACTION and ROLLBACK commands to execute test script. But it needs some effort to change the testing script. SSDT offers another graceful solution as described in MSDN. We can declare the transaction in C# code (that’s .cs file for unit test).

To execute only one test from the test class in a transaction we should do the following:

  • Right-click the unit test in Solution Explorer and select View Code.
  • Find the declaration of your particular test
transaction test - before
  • Place the code from inside the function body into the following block:

The code will look like this:
transaction test - one test
To execute all tests of the class in a transaction we should do:
  1. Declare TransactionScope variable in a test class.
  2. Create a transaction in the Init() method.
  3. Dispose of the transaction in the Cleanup() method.
The code will look like this:
transaction test - all tests

Making Fakes in SSDT Database Unit Tests

When we test SQL Server stored procedures or functions there may be situations where one object (for example, stored procedure) calls another object (for example, a scalar function). In this case we have dependency between objects.

One of the method to test the functionality of dependent objects is to use fake objects, i.e. we use a stub function instead of a real one. In this post I’ll show how to substitute a function with a fake.

I’ve created a very simple function that multiply an integer value by 2.

I’ve created a simple unit test for that function in SQL Server Data Tools with the only test condition to check that 1 * 2 = 2.
fake unit test
My test passes. Now I want to replace my function with a fake that will always return a value 2.
This can be made in pre-test section by renaming the real function and creating a stub.
fake pre-test
There’s two important things in this script:
  1. It does not check the existence of a new function name and it does not delete it. If this new name already exists, the unit test throw an error.
  2. You can’t create a stub function with a direct CREATE FUNCTION command due to an error in SQL batch. Therefore, I use a wrapper to execute SQL command dynamically.
In post-test section I safe-delete my stub function and revert the name back.
fake post-test
The test passes. That’s all right!
Using a fake object allows us to test each database object separately but it needs some efforts from a developer.

Comparing DateTime Values In SSDT Database Unit Test

Unit test in SQL Server Data Tools has Scalar Value test condition. This test condition receives a dataset (result set) from the query. You can choose the cell from that dataset with Row number and Column number properties.

ScalarValue properties

If you want to check a value of variable you should use the following command:

This will return a dataset that contain only one cell, which means it has one row and one column. If you want to check a value from another cell, adjust the properties mentioned above.

I was wondering how SSDT understand the value that we set in the Expected value property. When there’s a comparison between numbers or strings, I don’t even think about conversion problems.
But what will be if we compare dates? I have not found a calendar or other tools to set datetime value. The Properties pane has a mere textbox for the Expected value. It seems that SSDT gets a string.
So I decided to conduct an experiment to check how conversion really works. I created a very simple test case. I use ISO format in cast to avoid any conversion problem.

DateTime test

SQL Server has a number of string format that can be used as a representation of a datetime. Full list is available in MSDN.
When I use ISO or unseparated format, the test passes. When I set the value as ‘8/15/2014 8:20:15’, it works too. But when I set the date as ‘15.08.2014 8:20:15’, the test fails.

conversion error

That’s because SSDT (Visual Studio) or SQL Server has another locale or dateformat settings than the format

This experiment proves that SSDT really performs conversion from string to datetime type. To avoid conversion problems always set the value in the Expected value property in ISO 8601 format:

Hints For Creating Custom Test Condition for SSDT in Visual Studio 2013

I use the steps described in the MSDN article How to: Create Test Conditions for the SQL Server Unit Test Designer and found some variations while repeating it in Visual Studio 2013.

1) in step 5 we can use a newer version of dll if we have a newer version of SQL Server installed on local machine. Then number 110 in the path means SQL Server 2012, 120 – SQL Server 2014.

2) in step 8 we need to change the VS version number from 10.0 to 12.0. So you need to add these lines:

Hint: import of Microsoft.CSharp.targets is located at the end of the file.

3) in step 10 we need to include two namespaces to derive from class TestCondition. Add these directives to your class:

4) in step 13 we need to copy dll file from bin\Debug or bin\Release folder to C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\Extensions\Microsoft\SQLDB\TestConditions. You will see readme.txt file there.

Debugging In SSDT Database Unit Test

“SSDT now enables you to develop, debug and execute database unit tests interactively in Visual Studio…”

I was pleased when I read this in the post in SSDT team blog.
But I was confused a little when I could not set a breakpoint in unit test as I always do in Visual Studio. Yeah! You can’t click on the grey sidebar to the left from the unit test code (it’s on the red frame on the screenshot below).
place to set breakpoint
I’ve found a topic How to: Debug Database Objects on the MSDN that describe the steps needed to debug unit test.
1) enable SQL debugging on test project:

  • right-click test project in Solution Explorer, then click Properties
  • click Debug
  • check the option Enable SQL Server debugging
enable sql debugging
2) increase execution timeout:
  • double-click on app.config in Server Explorer
app.config in solution explorer
  • set CommandTimeout in the line ExecutionContext to a greater value (for example, 300 seconds)
  • rebuild the test project to apply changes in app.config
3) set breakpoint in stored procedure we are debugging. You can open it in your database project or via SQL Server Object Explorer (View -> SQL Server Object Explorer).
4) start the debugging:
  • open Test Explorer;
  • right-click the test
  • click Debug Selected Tests
Immediately after that you will see the debug panels in Visual Studio. But it’s too early to press any buttons to step into or over. You should wait some time while Visual Studio loads and runs all the actions before it reach the breakpoint. At this time the breakpoint is shown as a white circle with a red rim.
When the execution stops at breakpoint the line will be highlighted with yellow color. Now you can step forward over the commands of the stored procedure being debugged.

MatchFirstError Property in SSDT Database Unit Test And Multiple SQL Errors

When I explored the negative unit testing (see here) I encountered a new property MatchFirstError in ExpectedSqlException.

attributes of test method

According to MSDN, true value means that the expected severity, state, and error number must match to the first SqlError in the exception; false value – to any SqlError in the exception.

I was wondering in which situations a SQL error could not be the first. If it’s not the first, it can be the second error and so on. The next question – how can I raise two errors?
When SQL server executes T-SQL commands, the error interrupts that batch and raises an exception. So this error will be the the first and the only error.
But when we use try – catch blocks, there can be errors both in try and catch block. Therefore there can be multiple errors. It can be check in a very simple way.
We execute a code that tries to delete non-existing stored procedures in try and catch block.

nested errors

The result is two(!) errors from one stored procedure.

Let’s return back to MatchFirstError property. The default value (true) means that expected error will be the only exception or the cause of other consequent exceptions (in another words, it won’t be raised by another error).

SSDT Negative Database Unit Test

In one of my previous post I’ve faced with exception that occurred during unit test. Placing an appropriate command to disable constraints helps to avoid exception. But what should we do if we want to check that T-SQL code really fires an exception? The answer is very simple – we need to write a test that will handle the exception. This technique is called “negative testing”.

I will use the same example that I described in my previous test.

  • I change the Pre-test section to enforce constraints. Just place this command:

  • open the C# code of unit test: right-click the test in Solution Explorer, then click View Code
view csharp code of unit test
  • find the definition of our test (search for string dbo_CreateClientTest)
  • change the line in square brackets above the definition by adding a ExpectedSqlException attribute
attributes of test method

Message number and severity code was taken from the output of failed test that was run before. All possible properties that can be used in ExpectedSqlException declaration are explained here.
Note: The test will work even if you omit all the properties but using the exact values gives you the better level of granularity to check the right kind of exceptions.

 Now we can run our test. This time we see a green mark – our test passes.

Working With Dependencies In SSDT Unit Test: Disable Contstraints

When we run a piece of T-SQL code, it can be dependent on another T-SQL code such as function. For example, stored procedure queries data with only one SELECT command. In this command you can use scalar functions in SELECT clause, table-valued functions or views in FROM clause, scalar functions in WHERE clause (that’s a very bad habit, but it’s possible), etc.

Our T-SQL code can be affected by other database objects such as constraints and triggers. When we insert or update data, these objects can prevent to enter incorrect values, so our command would fail.

Let’s look at how they would impact on database unit testing.
I modify the test case I’ve created earlier in this post and add a simple constraint.

Table with constraint

My test passed because ClientName (‘John Smith’) consists of 10 chars. Now I change to something shorter, and the test failed.

failed test

There’s no error in stored procedure I’ve been testing, it’s a violation of constraint. So to pass this test (I mean to test the functionality of stored procedure) I should eliminate constraint from being applied during the test. It can be done in the following manner:

  • choose an item Pre-test in the dropdown list shown below
Create post-test
  • Click the link Click here to create in the center of Test Designer.
  • Write T-SQL code to disable the constraint.

Alternatively, we can disable all constraints in a table with one command:

Now our test really passed.

pre-test disable constraint
But don’t forget to enable constraint in Post-Test section of unit test.