SQL Unit Testing with C# and Specflow

I recently gave a talk on SQL unit testing using C# and Specflow and the following are the slides from the talk. Link to the recording from the talk will be made available soon.

Link to demo SQL unit testing framework project code on GitHub - SQL Server Unit Testing with C# and Specflow

If you’re someone who doesn’t see database just a storage for object and would like to know more about how to unit test programmable part of database object, this SQL unit testing with C# and Specflow post is for you.

Here is a little bit about myself.

Let’s begin by considering what do we mean by this thing called Unit Testing?

There are several definitions available out there but there is one definition that I kinda like and it comes from Microsoft, and it reads like this:

As we move into DevOps centric world, it is becoming ever more important to have similar unit testing exaptation from SQL programmers as we have from other programmers.

Unit testing has become ever more important in the software industry, however SQL programming is generally less likely to be subject to unit testing but SQL can often benefit from unit testing.

So let’s look at the unit testing benefits:

When it comes to SQL unit testing tools there are aren’t many growing numbers of unit testing frameworks when it comes to SQL and there are certainly fewer frameworks to choose from as compared to other programming languages.

So one SQL unit testing framework that is widely used is tSQLt, hence I naturally started with evaluating tSQLt but I wanted to explore other options as well and given my C# experience I also wanted to give C# a fighting chance during the evaluation process.

During the evaluation I mainly focused on two parameters:

• Ease of adoption
• Capabilities

After considering the above two parameters and several other criteria for each of the above parameters (see image below), the conclusion was that either tSQLt or C# and Specflow will work well and can be used.

However, tSQLt is all in t-SAL so it’s easier to write tests for database people whereas specflow is more integrated into Visual Studio and the team that I was working with at the time had more members with C# experience as compared to SQL, so there was more internal support and expertise available within the team for C# and Specflow hence I decided to go with C# and Specflow.

Now let’s take a look at some SQL unit testing patterns that you may want to consider or be aware of when embarking on the SQL unit testing journey.

Thanks for reading! 🎊 Hope you learned something useful. Don’t hesitate to share, or post your thoughts in the comments section.

You can also reach out to me on LinkedIn 🙏