Is it possible to test SQL data in a C# unit test?

Question

public ApplicationLayout Get(string objectName, string userName)
{
    using (var db = new SqlConnection(_connectionString))
    {
        var sql = "SELECT * FROM dbo.ApplicationLayout WHERE ObjectName = @ObjectName AND UserName=@UserName";

        return db.Query<ApplicationLayout>(sql, new {ObjectName = objectName, UserName = userName}).FirstOrDefault();
    } 
}

I have some code here, and I was wondering if it is possible to perform a unit test in C# using SQL data (any framework). From what I've seen, it is not possible. Can anyone please confirm this?

Answer 1

Possible, but not recommended for a unit test.

When you talk about a unit test, you're usually talking about a test that can be repeatably executed in isolation; that is, it has no dependencies outside the code assembly where it lives (and ideally each test should not go very far beyond a single class being tested), and has no lasting side effects regardless of how the test is started or stopped (doesn't require use of the file system, or a database, etc). This is because unit tests, being the smallest and most fine-grained tests, are intended to test the code that you yourself wrote. You didn't write SQL Server, so SQL Server shouldn't be in the scope of a unit test.

Now, once you widen the scope to integration tests and acceptance tests, now you're testing that code you wrote plays nicely with all the external dependencies you didn't write. Integration tests can touch external data, like databases, and can write to as well as read from those external stores, as long as they clean up after themselves properly, either by returning a persistent database to its original condition after the run, or just using a portable DB like SQLite or MSS Express, setting up the necessary schema and test data as part of your test setup, and then just blowing the DB away when it's done.

For many, this is splitting hairs, and unit testing frameworks like MSTest or NUnit don't prevent you using them to create a test that has external dependencies. These are merely good ideas for structuring your full test suite into a fine-grained, fast-running, repeatable component that you can run on any change you make, and the longer-running, wider-scoped proof of correctness that might run overnight.

Answer 2

I always did my testing on a dedicated database. You can do a setup and a tear down, and basically recreate the database (or restore it from a backup) each time you run those tests. It is a lot more involved than just plain C# unit testing. Like some comments stated, it's not really unit testing anymore but stuff needs to be tested one way or another.

Answer 3

The modern approach to orchestrate testing against a real SQL engine has to work:

• locally on dev machines (that run Windows, MacOS or Linux);
• build pipelines in the cloud (e.g. Azure DevOps pipelines, GitHub Actions, etc.).

TL;DR – check out DbSample on GitHub, a sample EF Core based project with fully automated tests against MS SQL Server and a GitHub Actions pipeline.

A detailed analysis of orchestrating tests is covered in "Pain & Gain of automated tests against SQL (MS SQL, PostgreSQL)" article. The key steps are:

1. Launch SQL engine in Docker (use official images for SQL Server or PostgreSQL)
2. Create a test database and populate the schema, logic, lookup dictionaries, etc (via command line for the containers)
3. Connect xUnit/NUnit tests to the database and for each test (rinse and repeat):
   1. Seed test data
   2. Perform the testable activity and checks
   3. Revert the database to the pristine state (via Respawn)
4. Tear down the SQL engine along with the database and other artefacts.

P.S. I know, the OP said "unit tests" but perhaps meant a more broad "automated tests"